The following SQL Server tunings improve performance and scalability in environments such as TPC-E:
You can use the -T834 start flag to enable SQL Server to use large pages.
If you disable SQL Server performance counters to avoid potential overhead, start SQL Server as a process instead of a service and use the -x flag:
-
From the Services MMC snap-in (Services.msc), stop and disable SQL Services.
-
Execute the following command from the SQL Server Binn directory:
sqlservr.exe –c –x
Enable the TCP/IP protocol to allow communication with client systems:
-
Navigate to . Then navigate to SQL Server Network Configuration > Protocols for MSSQL Server, right-click TCP/IP, and click Enable.
Configure SQL Server according to the guidance in the following list. You can configure SQL Server by using the sp_configure stored procedure. Set the “show advanced options” value to 1 to display more available configuration options. Detailed information about the sp_configure stored procedure is available in “Resources” later in this guide:
You can set CPU affinity for the SQL process to isolate system resources for the SQL Server instance from other SQL Server instances or other applications running on the same system. You can also set CPU affinity for the SQL process to not use a set of logical processors that handle I/O interrupt traffic (network and disk).
You can set CPU affinity for the SQL process in different ways, depending on processor count: Set affinity mask to partition the SQL process on specific cores up to 32 logical processors. To set affinity on more than 32 logical processors but fewer than 64 processors, use affinity64 mask. Starting with SQL Server 2008 R2, you can apply equivalent settings for configuring CPU affinity on as many as 256 logical processors using the ALTER SERVER CONFIGURATION SET PROCESS AFFINITY Data Definition Language (DDL) TSQL statement as the sp_configure affinity mask options are announced for deprecation. Use the ‘alter server configuration set process affinity cpu =’ command to set affinity to the desired range or ranges of processors, separated by commas. For more information on best practices for installations with more than 64 logical processors, and for more information on DDL, see “Resources” later in this guide.
You can set a fixed amount of memory for the SQL Server process to use. About 3% of the total available memory is used for the system, and another 1% is used for memory management structures. SQL Server can use the rest of available memory, but not more.
The following equation is available to calculate total memory to be used by SQL Server:
TotalMemory – (1%memory * (numa_nodes)) – 3%memory – 1GB memory
Leave the lightweight pooling value set to the default of 0. This enables SQL Server to run in threads mode. Threads mode performance is comparable to fibers mode.
If it appears that the default settings do not allow sufficient concurrent transactions based on a throughput value lower than expected for the system and benchmark configuration, set the maximum worker threads value to approximately the number of connected users. Monitor the sys.dm_os_schedulers DMV to determine whether you need to increase the number of worker threads.
Set the default trace enabled value to 0.
Set the priority boost value to 1.
| 