Testing Intel Optane PMem Solution For SQL2019 In The ATC
World Wide Technology’s Advanced Technology Center (ATC) is a collaborative ecosystem where WWT experts, customers and partners can explore, evaluate and educate themselves on integrated architectural solutions that accelerate digital transformation and help solve business challenges. Recently the ATC has experienced a significant increase in customer requests with SQL 2019 due to Microsoft ending support on their SQL 2005, SQL 2008, and SQL 2008 R2 products this past year. Customers are particularly interested in comparing 2-socket servers, 4-socket servers and servers integrated with Intel Optane solutions. These requests lead the ATC team to build an environment that combines these options into one solution. The environment includes an SQL 2019 workload, HammerDB for load generation, 2-socket blade (MX740c) and 4-socket blade (MX840c) solutions from Dell’s MX7000 kinetic blade infrastructure, Dell’s PowerMax Array and Intel Optane PMem.
The SQL2019 Workload testing completed within this ATC Insight was done using “Industry Best Standards” leveraging the ATC's Dell Compute Lab. This environment was comprised of:
Dedicated High-Speed Network Switch Engines in the "A" Fabric of each chassis
Dedicated G610s FC modules providing 32Gb FC connectivity for each blade
A PowerMax 2000 Array to meet the high-speed storage requirements for testing
Below are the details of the MX7000 Environment:
The remaining environment variables are here:
The load generation component was a HammerDB v3.3 instance installed in each VM and was built with 400 warehouses and nine virtual users per instance. We used VMware ESX 6.7 U3 as the Hypervisor and each VM had its own dedicated LUN provided via the PowerMax storage array.
The Engineering team in the Advanced Technology Center completed more than a dozen tests to date. The tests tasked each server with running one, five, ten and the maximum number of VMs each server can properly support up to the 80% threshold, as called out in the test document.
The lowest per-VM cost: The 4-socket server with Intel Optane persistent memory was nearly 38 percent less than the 2-socket server, and more than 18 percent less than the 4-socket server with standard memory (see team for information around the detailed bill of materials).
Supported 36 percent more VMs compared to the 4-socket server with DRAM only.
Completed 20 percent more transactions per minute compared to the 4-socket server with DRAM only.
Completed 20 percent more new orders per minute compared to the 4-socket server with DRAM only.
The main goal of testing was to provide our customers with valuable insights around how an SQL2019 database would perform within a 2-socket server configuration versus a 4-socket server configuration. We also wanted to show how incorporating Intel Optane PMem can add additional performance and economical returns to their investment.
The test plan in this environment was pretty simple and straight forward. We first reserved the necessary hardware components called out in the environment details of the ask, which included a dedicated MX7000 environment with three separate blades of like-CPU and fabric connectivity along with a Dell PowerMax 2000 Array. The blade configurations consisted of:
MX740c 2-socket blade with 2 x Intel Xeon 8280 processors and 24 x 32Gb DIMMs
MX840c 4-socket blade with 4 x Intel Xeon 8280 processors and 48 x 32Gb DIMMs
MX840c 4-socket blade with 4 x Intel Xeon 8280 Processors and 24 x 128Gb Intel Optane PMem modules and 24 x 32Gb DIMMs
The team took the following steps to build out both the physical and virtual components:
Installed ESX 6.7 U3 on each blade in a like-configuration and connected the hosts to a dedicated PowerMax 2000 Array
Created a base template VM with Windows Server 2019 Enterprise Edition as the operating system, SQL2019 Enterprise and HammerDB v3.3
Configured the SQL2019 Virtual Machine to have 8 vCPU, 128Gb of Memory and a 2.5TB Virtual Disk
Structured the SQL2019 database itself to have multiple data mount-points
Positioned the HammerDB component to be an OLTP use case with 400 warehouses and 9 users
Once completed, the idea was to deploy and run as many SQL2019 virtual machines as possible on each host to drive around 80 percent total host utilization of CPU / Memory.
The virtual machines were configured to run concurrently over a 15 minute period on each server while we gathered key performance indicators (KPIs) such as:
The synthetic load generation tool used for this environment was HammerDB v3.3.
HammerDB is the leading benchmarking and load testing software for the world's most popular databases supporting Oracle Database, SQL Server, IBM Db2, MySQL, MariaDB, PostgreSQL, and Redis.
HammerDB is used to create a test schema, load it with data, and simulate the workload of multiple virtual users against the database for both transactional and analytic scenarios. This workload can then be used to derive meaningful information about your environment such as hardware performance comparisons and software configurations.
WWT and Intel, commissioned by Dell Technologies, have applied their collective capabilities to bring these SQL Server 2019 solutions to our Advanced Technology Center. If you’re curious to see how they might work in your environment, we can arrange an on-site or virtual visit to the ATC so you can experience them first-hand or socially distanced. It’s one more example of our continued focus on strategic data center modernization guided by results-driven testing and the value of the ATC.
Max VM’s scaled per node based on average % CPU utilization per node. Threshold for VM scaling to approach but not exceed 80%, therefore leaving acceptable headroom for critical operations.
Estimated per VM pricing based on list pricing of hardware and software for specifications tested as of Aug 2020.
MX740c - Estimated acquisition cost of $17,600 per VM with a max of 5 VM per node achieved, node scaled to 5 x VM’s at 72% CPU utilization threshold
MX840c - Estimated acquisition cost of $13,200 per VM with a max of 11 VM per node achieved, node scaled to 11 x VM’s at 75% CPU utilization threshold
MX840c w/ PMem – Estimated acquisition cost of $11,700 per VM with a max of 15 x VM per node achieved , Node scaled to 15 x VM’s at 68% CPU utilization threshold