Best Practices for Running SQL Server on Computers That Have More Than 64 CPUs
This topic provides best practice information when you are running an instance of SQL Server on computers that have more than 64 CPUs.
Assigning Hardware Threads with CPUs
Do not use the affinity mask and affinity64 mask server configuration options to bind processors to specific threads. These options are limited to 64 CPUs. Use ALTER SERVER CONFIGURATION SET PROCESS AFFINITY (Transact-SQL) instead.
Managing the Transaction Log File Size
Do not rely on autogrow to increase the size of the transaction log file. Increasing the transaction log must be a serial process. Extending the log can prevent transaction write operations from proceeding until the log extension is finished. Instead, preallocate space for the log files by setting the file size to a value large enough to support the typical workload in the environment.
Setting Max Degree of Parallelism for Index Operations
The performance of index operations such as creating or rebuilding indexes can be improved on computers that have many CPUs by temporarily setting the recovery model of the database to either the bulk-logged or simple recovery model. These index operations can generate significant log activity and log contention can affect the best degree of parallelism (DOP) choice made by SQL Server.
In addition, consider adjusting the max degree of parallelism (MAXDOP) setting for these operations. The following guidelines are based on internal tests and are general recommendations. You should try several different MAXDOP settings to determine the optimal setting for your environment.
For the full recovery model, limit the value of the max degree of parallelism option to eight or less.
For the bulk-logged model or the simple recovery model, setting the value of the max degree of parallelism option to a value higher than eight should be considered.
For servers that have NUMA configured, the maximum degree of parallelism should not exceed the number of CPUs that are assigned to each NUMA node. This is because the query is more likely to use local memory from 1 NUMA node, which can improve memory access time.
For servers that have hyper-threading enabled and were manufactured in 2009 or earlier, the MAXDOP value should not exceed the number of physical processors.
For more information about the max degree of parallelism option, see Degree of Parallelism.
Setting the Maximum Number of Worker Threads
Always set the maximum number of worker threads to be more than the setting for the maximum degree of parallelism. The number of worker threads must always be set to a value of at least seven times the number of CPUs that are present on the server. For more information, see max worker threads Option.
Using SQL Trace and SQL Server Profiler
We recommend that you do not use SQL Trace and SQL Server Profiler in a production environment. The overhead for running these tools also increases as the number of CPUs increases. If you must use SQL Trace in a production environment, limit the number of trace events to a minimum. Carefully profile and test each trace event under load, and avoid using combinations of events that significantly affect performance.
Setting the Number of tempdb Data Files
Typically, the number of tempdb data files should match the number of CPUs. However, by carefully considering the concurrency needs of tempdb, you can reduce database management. For example, if a system has 64 CPUs and usually only 32 queries use tempdb, increasing the number of tempdb files to 64 will not improve performance. For more information, see Optimizing tempdb Performance.
SQL Server Components That Can Use More Than 64 CPUs
The following table lists SQL Server components and indicates whether they can use more that 64 CPUs.
Process name |
Executable program |
Use more than 64 CPUs |
|---|---|---|
SQL Server Database Engine |
Sqlserver.exe |
Yes |
Reporting Services |
Rs.exe |
No |
Analysis Services |
As.exe |
No |
Integration Services |
Is.exe |
No |
Service Broker |
Sb.exe |
No |
Full-Text Search |
Fts.exe |
No |
SQL Server Agent |
Sqlagent.exe |
No |
SQL Server Management Studio |
Ssms.exe |
No |
SQL Server Setup |
Setup.exe |
No |
See Also