Introduction
This guide is targeted towards users who are already familiar with Microsoft* SQL Server* and provides pointers and system settings for hardware and software that will provide the best performance for most situations. However, please note that we rely on the users to carefully consider these settings for their specific scenarios, since Microsoft SQL Server can be deployed in multiple ways and this is a reference to one such use-case.
Microsoft SQL Server is a relational database management system developed by Microsoft. SQL Server is offered in many different editions, but this guide will focus on the SQL Server Enterprise edition. SQL Server’s database architecture is based on a Client-Server architecture model. This tuning guide will focus on Online Transaction Processing (OLTP) workloads (TPCE like) while using SQL Server for Microsoft Windows*.
3rd Gen Intel® Xeon® Scalable processors deliver industry-leading, workload-optimized platforms with built-in AI acceleration, providing a seamless performance foundation to help speed data’s transformative impact, from the multi-cloud to the intelligent edge and back. Improvements of particular interest to this workload applications are:
- Enhanced Performance
- More Intel® Ultra Path Interconnect
- Increased DDR4 Memory Speed & Capacity
Tested hardware and software environment for this tuning guide:
Note The configuration described in this article is based on 3rd Generation Intel Xeon processor hardware. Server platform, memory, hard drives, network interface cards can be determined according to customer usage requirements.
Hardware Tuning
BIOS Setting
Please note, that all BIOS settings outlined below are based on Intel’s Software Development Platforms designed for the 3rd Gen Intel Xeon Scalable processors. Begin by resetting your BIOS to default setting, then follow the suggestion below for changes:
Memory Configuration/Settings
This workload runs best with 2 DIMMS per channel populated with 3200 MTS DDR4 registered memory. For specifics consult your platform’s manual.
Storage/Disk Configuration/Settings
The table below details the type and number of drives used. Each type of drive sets (DB, Temp DB, etc.…) are organized into RAID volumes, with the DB drives being evenly split into RAID0 volumes per IO controller. The only exception to this is the LOG volume which is configured as RAID10. The following are the recommended settings for the RAID volumes and can be set via the Intel® RAID Web Console 3 for Windows.
Network Configuration/Setting
This workload runs best with the following settings for all adapters connecting the Server to the Client:
The following PowerShell script is used as an example with ‘Ethernet 2’:
Set-NetAdapterAdvancedProperty -Name "Ethernet 2" -RegistryKeyword '*JumboPacket' –
RegistryValue '9014'
Set-NetAdapterRss
-Name "Ethernet 2" -Enable 1 -BaseProcessorGroup 0 -BaseProcessorNumber 0 -MaxProcessors 16 -NumaNode 65535 -MaxProcessorGroup 0 -MaxProcessorNumber 16 -NumberOfReceiveQueues 8 -Profile Closest
In addition, the following must be set in SQL Server using the SQL Server sp_configure command.
sp_configure network_packet_size,8192
go
RECONFIGURE WITH OVERRIDE
go
Software Tuning
Software configuration tuning is essential. From the Operating System to SQL Server configuration settings, they are all designed for general purpose applications and default settings are almost never tuned for best performance.
Microsoft Windows Server* 2019 settings
OLTP Architecture
The TPC-E like workload consists of transactions that simulate the interchanges commonly associated with brokers, customers, and a real-time stock exchange. These transactions are intended to represent a balanced mixture of disk input/output and CPU usage. Performance is measured in transactions per second and reported as tpsE.
Example of an OLTP benchmarking hardware configuration:
Tuning SQL Server for OLTP Workload
The following sp_configure commands should be used to configure SQL Server:
sp_configure show_advanced_options,1
go
RECONFIGURE WITH OVERRIDE
go
sp_configure backup_compression,1
go
RECONFIGURE WITH OVERRIDE
go
sp_configure "default trace enabled",0
go
RECONFIGURE WITH OVERRIDE
go
sp_configure lightweight_pooling,1
go
RECONFIGURE WITH OVERRIDE
go
sp_configure max_degree_of_parallelism,1
go
RECONFIGURE WITH OVERRIDE
go
sp_configure max_serv,<This number should be equal to 90% of system memory>
go
RECONFIGURE WITH OVERRIDE
go
sp_configure max_worker_threads,3000
go
RECONFIGURE WITH OVERRIDE
go
sp_configure priority_boost,1
go
RECONFIGURE WITH OVERRIDE
go
sp_configure recovery_interval,32767
go
RECONFIGURE WITH OVERRIDE
go
sp_configure remote_query_timeout,0
go
RECONFIGURE WITH OVERRIDE
go
sp_configure set_working_set_size,1
go
RECONFIGURE WITH OVERRIDE
go
sp_configure network_packet_size,8192
go
RECONFIGURE WITH OVERRIDE
go
The following flags should be used on the command line when starting up SQL Server prior to running the OLTP workload.
Sqlservr.exe -c -x -T3502 -T834 -T661 -T8744 -T652 -s SQLOLTP
Related Tools
Microsoft Windows Performance Monitor tool (Perfmon) can be used to monitor overall system and disk performance metrics.
Best Practices for Testing and Verification
This workload is best run just after a fresh restoration of the database from backup and restart of the system. This workload should drive CPU Utilization to 99% across all logical processors. CPU kernel utilization should be around 12-13%. This is an I/O intensive workload that is characterized by 600K IOPs, Random Access, and a 90/10 Read/Write ratio. The clients for this workload initiate transactions with 500+ simultaneous connections to the system under test with 500+ transactions in flight. This workload is more sensitive to memory speed than capacity. Typically, this workload reaches peak performance with memory size equal to about 3% of the database size. For verification of the database integrity, refer to the TPCE kit mentioned in resources for details. The TPCE kit provides verification tools suitable for an audit per the TPCE benchmark spec.
Conclusion
We have shared our best-known methods to optimally benchmark our 3rd Generation Intel Xeon Scalable Processors using an OLTP benchmark. We have covered both software and hardware configuration considerations to get the best performance.
Additional Resources
[1] Microsoft SQL Server: https://www.microsoft.com/en-us/sql-server/sql-server-downloads
[2] Transaction Processing Council: http://www.tpc.org/
[3] TPCE benchmark kit version 1.14.0: http://tpc.org/TPC_Documents_Current_Versions/download_programs/tools-download-request5.asp?bm_type=TPC-E&bm_vers=1.14.0&mode=CURRENT-ONLY
Feedback
We value your feedback. If you have comments (positive or negative) on this guide or are seeking something that is not part of this guide, please reach out and let us know what you think.
Notices & Disclaimers
Intel technologies may require enabled hardware, software or service activation.
No product or component can be absolutely secure.
Your costs and results may vary.
Code names are used by Intel to identify products, technologies, or services that are in development and not publicly available. These are not "commercial" names and not intended to function as trademarks
The products described may contain design defects or errors known as errata which may cause the product to deviate from published specifications. Current characterized errata are available on request.
© Intel Corporation. Intel, the Intel logo, and other Intel marks are trademarks of Intel Corporation or its subsidiaries. Other names and brands may be claimed as the property of others.