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