Servers & Systems: The Right Compute
Gary_Craze

3 ways to supercharge your SQL Server 2019 performance

Learn how HPE ProLiant Servers and Intel® Optane™ Persistent Memory supercharge SQL Server 2019 performance.

HPE ProLiant-Intel-supercharge SQL performance-blog.png

You live this every day: Today’s enterprise IT environments are facing a range of challenges presented by the continuing growth of data and the demands for increased workload optimization.

This means you must manage your data carefully to ensure that your business’s demanding and tech-savvy end users can securely access valuable data resources whenever and wherever. You also need to focus on IT efficiency, managing workloads to maintain optimal cost and performance balance. Some days you seem to be in an endless state of doing more with less.

What’s more, these challenges are often spread across a hybrid cloud environment. Sure, this has provided your organization agility and cost benefits. But it also challenges your IT team to ensure consistency in resourcing and performance—no matter where a workload is running.

As a leading relational database management system (RDBMS), Microsoft SQL Server is essential to meeting these goals for many organizations. To do so, SQL Server environments must address three key priorities.

1. Accelerating workloads

As the pace of business continues to accelerate, your IT team is pressured to run core SQL Server workloads faster to generate more results, more quickly than your competition. This is true for all types of workloads, including online transaction processing (OLTP), business operations workloads like airline reservation systems, ordering, or financial trading, and online analytics processing (OLAP), enterprise data warehouse workloads like tracking, predicting customer behavior, or budgeting.

HPE ProLiant servers with Intel Xeon Scalable processors and Intel Optane persistent memory process data faster, speeding up data warehousing (OLAP) and online transaction processing (OLTP) workloads on SQL Server.  Benchmark testing of a 22-query stream data warehouse workload running SQL Server 2019 standard edition on HPE ProLiant DL380 Gen10 servers with Intel Optane PMem in App Direct mode ran queries up to 6.9X faster than the same system without persistent memory.1 For analytic workloads, a performance improvement of up to 3.6X has been demonstrated with Intel Optane persistent memory in App Direct mode over current generation NVMe technology with SQL Server 2019 Standard Edition.2

2. Optimizing workload performance and cost

Enterprise workloads can be complex and costly to deploy and run, so you’ve got to ensure each workload can be implemented rapidly and efficiently. Once deployed, you then must keep it running on the most appropriate hardware to maintain an optimal balance of performance versus costs. This requires constant oversight and adjustment, which customarily is a manual effort, relying on your staff’s experience and expertise.

Relying on experience or trial-and-error to optimize critical SQL Server workloads is simply no longer feasible. Whether in the cloud or on-premises, your server foundation needs elements that enable real-time performance tuning and data distribution. By automating workload monitoring and optimization as much as possible, your team can focus on more strategic tasks.

With Intel Optane persistent memory, you can choose between two modes to help optimize workload performance. When used in Memory mode, you can replace more-expensive DRAM to increase VM density and support in-memory SQL Server 2019 operations without compromising on performance. This will reduce IO bottlenecks and maintains DRAM performance where you need it while minimizing the need to overprovision your memory resources. 

When used in App Direct mode, you can do more with standard edition SQL Server 2019. In App Direct mode, Intel Optane PMem helps reduce the amount of data that needs to be copied to DRAM in your buffer pool. Read-only pages can stay in-memory without needing to go into the buffer pool, helping to minimize the amount of DRAM needed without impacting workload performance, thus enhancing your capacity without the need to invest in enterprise edition.

3. Minimizing hardware constraints

If you’re running SQL Server workloads across a diverse hybrid cloud environment, you must ensure consistent resource availability and performance for your end users. At the same time, cost and sustainability pressures are likely driving your organization to reduce the data center footprint as much as possible. As a result, your hardware investments must be made with a focus on maximizing performance, flexibility, and capacity while keeping costs and space to a minimum.

HPE ProLiant Gen10 servers with Intel Xeon Scalable processors and Intel Optane persistent memory help you reduce your SQL Server data center footprint by transitioning the live database to persistent memory and away from disk memory.

HPE and Intel offer an ideal foundation for SQL server workloads

HPE and Intel have worked together for decades, combining a depth of performance engineering expertise to help our customers build optimal foundations. For SQL Server workloads, this means combining HPE ProLiant DL380 Gen10 servers with Intel® Xeon® Scalable processors featuring Intel Optane persistent memory.

As the industry’s most trusted compute platform, the HPE ProLiant DL380 Gen10 server delivers the latest in workload-optimized performance, security, and management automation, backed by a comprehensive warranty. Meanwhile, 2nd Gen Intel Xeon Scalable processors offer new all-core frequencies of up to 3.9 GHz (up to 4.5 GHz with Intel Turbo Boost) and 44% more cache-optimizing performance for the most demanding workloads. They deliver up to 36% increased performance and 42% performance per dollar improvement over the prior generations.3

With support for SQL Server 2016 due to be discontinued in July 2021, now is an opportune time to consider upgrading to the latest version (2019). As part of this migration, it’s also worth ensuring the hardware you use to support it offers the performance, manageability, agility, and cost-effectiveness to support your ongoing business requirements. Together, HPE and Intel offer a server platform that meets these needs using proven, industry-leading technology.

For more information, please check out:

 

Gary Craze
Hewlett Packard Enterprise

twitter.com/HPE_Servers
linkedin.com/showcase/hpe-servers-and-systems/
hpe.com/servers


1 Benchmark setup: Data warehouse workload: 22 queries per stream and 7 parallel query streams on 1 TB scale factor. HPE ProLiant DL560 Gen10, 2 x 12-core @ 3.30 GHz: 192 GB DRAM, 8 x 1.92 TB NVMe SSD database storage, SQL Server 2019 Standard Edition. HPE ProLiant DL380 Gen10, 2 x 12-core @ 3.30 GHz: 192 GB DRAM, 8 x HPE 128 GB Persistent Memory database storage, SQL Server 2019 Standard Edition

2 https://h20195.www2.hpe.com/v2/getdocument.aspx?docname=a50000414enw

32nd Gen Intel® Xeon® Gold R processors: 1-node, 2 x 2nd Gen Intel Xeon Gold processor (62xxR/$$) on Intel reference platform with 384 GB (12 slots/32 GB/62xx@2933,52xx@2666) total memory, ucode 0x500002c, HT on for all except off for STREAM (GB/s), LINPACK (GFLOPS/s), Turbo on, with Ubuntu 19.10, 5.3.0-24-generic, 6258R/$3950: SPECrate®2017 _int_base(est)=323, SPECrate®2017_fp_base(est)=262, STREAM = 224, LINPACK= 3305; 6248R/$2700: SPECrate®2017_int_base(est)=299, SPECrate®2017_fp_base(est)=248, STREAM = 224, LINPACK = 3010; 6246R/$3286: SPECrate®2017_int_base(est)=238, SPECrate®2017_fp_base(est)=217, STREAM = 225, LINPACK = 2394; 6242R/$2529: SPECrate®2017_ int_base(est)=265, SPECrate®2017_fp_base(est)=231, STREAM = 227, LINPACK = 2698; 6240R/$2200: SPECrate®2017_int_base(est)=268, SPECrate®2017_fp_base(est)=228, STREAM = 223, LINPACK= 2438; 6238R/$2612: SPECrate®2017_int_base(est)=287, SPECrate®2017_ fp_base(est)=240, STREAM = 222, LINPACK = 2545; 6230R/$1894: SPECrate®2017_int_base(est)=266, SPECrate®2017_fp_base(est)=227, STREAM = 222, LINPACK = 2219; 6226R/$1300: SPECrate®2017_int_base(est)=208, SPECrate®2017_fp_base(est)=192, STREAM = 200, LINPACK= 2073; 5220R/$1555: SPECrate®2017_int_base(est)=257, SPECrate®2017_fp_base(est)=220, STREAM = 210, LINPACK = 1610; 5218R/$1273: SPECrate®2017_int_base(est)=210, SPECrate®2017_fp_base(est)=188, STREAM = 199, LINPACK = 1290, test by Intel on 12/25/2019. First Gen Intel Xeon Gold processors: 1-node, 2 x Intel Xeon Gold processor (61xx/$$) on Intel reference platform with 384 GB (12 slots / 32 GB / 61xx@2666,51xx@2400) total memory, ucode 0x500002c, HT on for all except off for STREAM (GB/s), LINPACK (GFLOPS/s), Turbo on, with Ubuntu 19.10, 5.3.0-24-generic, 6152/$3655: SPECrate®2017_int_base(est)=224, SPECrate®2017_fp_base(est)=198, STREAM = 200, LINPACK= 1988; 6148/$3072: SPECrate®2017_int_base(est)=225, SPECrate®2017_fp_base(est)=198, STREAM = 197, LINPACK = 2162; 6146/$3286: SPECrate®2017_int_base(est)=161, SPECrate®2017_fp_base(est)=175, STREAM = 185, LINPACK = 1896; 6142/$2946: SPECrate®2017_ int_base(est)=193, SPECrate®2017_fp_base(est)=176, STREAM = 185, LINPACK=1895; 6140/$2445: SPECrate®2017_int_base(est)=202, SPECrate®2017_fp_base(est)=183, STREAM = 188, LINPACK = 1877; 6138/$2612: SPECrate®2017_int_base(est)=189, SPECrate®2017_fp_ base(est)=195, STREAM = 189, LINPACK = 1976; 6130/$1894: SPECrate®2017_int_base(est)=172, SPECrate®2017_fp_base(est)=165, STREAM = 185, LINPACK = 1645; 6126(proj)/$1776: SPECrate®2017_int_base(est)=141, SPECrate®2017_fp_base(est)=157, STREAM = 170, LINPACK = 1605; 5120(proj)/$1555: SPECrate®2017_int_base(est)=148, SPECrate®2017_fp_base(est)=148, STREAM = 159, LINPACK = 924, 5118/$1273: SPECrate®2017_int_base(est)=134, SPECrate®2017_fp_base(est)=132, STREAM=149, LINPACK = 818, test by Intel on 2/18/2020

0 Kudos
About the Author

Gary_Craze

Meet blogger Gary Craze, a 30-year veteran of the technology industry. Gary has held marketing and product management roles with enterprise technology companies helping them to understand the needs of customers and creating compelling value propositions that meet their business needs. Currently, Gary is the Worldwide Product Marketing Manager for the HPE ProLiant Server team at HPE where he is responsible for evangelizing the world’s most secure industry-standard servers.