7.2. CPU configuration
When an instance of SQL server starts, it's created as an operating system process. Unlike a simple application that performs a series of serial tasks on a single CPU, SQL Server is a complex application that must support hundreds or even thousands of simultaneous requests. In order to do this, the SQL Server process creates threads.
A multithreaded operating system such as Windows allows applications like SQL Server to create multiple threads in order to maximize CPU efficiency and application throughput. Threads are assigned and balanced across the available CPUs in a server. If a thread is waiting on the completion of a task such as a disk request, SQL Server can schedule the execution of other threads in the meantime. The combination of a multithreaded architecture and support for multi-CPU servers allows applications such as SQL Server to support a large number of simultaneous requests.
With this background in mind, let's take a look at some of the configuration options that can be set to control the manner in which SQL Server threads are executed.
7.2.1. Boost SQL Server Priority option
Threads created in Windows are assigned a priority from 1 to 31, with thread priority 0 reserved for operating system use. Waiting threads are assigned to CPUs in priority order—that is, higher-priority threads are assigned for execution ahead of lower-priority threads.
By default, SQL Server threads are created with a "normal" priority level of 7. This priority ensures SQL Server threads are assigned and executed in a timely manner without causing any stability issues for anything else running on the server.
The Boost SQL Server Priority option runs SQL Server threads at a priority level of 13, higher than most other applications. Although this sounds like a setting that should always be enabled, much like the "turbo" button on old PCs, it should only be used in rare circumstances.
Enabling this option has resulted in problems, such as not being able to shut down the server and various other stability issues. In almost all cases, on well-configured dedicated servers, the performance difference is likely to be negligible at best. Unless you have a very accurate load-testing environment in which you can prove this option results in a measurable performance boost and doesn't cause any other stability and performance issues, use the default setting.
7.2.2. Maximum Worker Threads option
Despite each thread being light in terms of resource consumption, they consume resources nonetheless. In systems with thousands of concurrent tasks, creating a dedicated thread per task would consume a significant amount of resources. To counter this, SQL Server implements thread pooling, whereby threads are assigned to tasks from a pool as required.
SQL Server will size the thread pool automatically based on the number of CPUs in the server and whether the system is 32- or 64-bit. As table 7.2 shows, this ranges from 256 to 960. Optionally, you can define the number of threads for greater control.
| Number of CPUs | 32-bit | 64-bit |
|---|---|---|
| 1-4 | 256 | 512 |
| 8 | 288 | 576 |
| 16 | 352 | 704 |
| 32 | 480 | 960 |
In situations where the number of running tasks is less than the defined number of threads, each task will have its own dedicated thread. If the number of concurrent tasks rises beyond the number of threads, threads are pooled, and pending tasks are granted threads from the pool when available.
When system load is very high and all available threads are assigned to running tasks, the system may become unresponsive until a thread becomes available. In such situations, the dedicated administrator connection (DAC) can be used to connect to the server and perform troubleshooting tasks, possibly involving terminating some processes.
Dedicated administrator connection (DAC)When a SQL Server instance has become unresponsive, troubleshooting can be difficult. To assist with these situations, SQL Server reserves resources for a dedicated administrator connection (DAC), enabling a DBA to connect to the instance and take appropriate actions, such as terminate a process. A connection to the DAC is made using either the SQLCMD command prompt utility or SQL Server Management Studio. Books Online describes how to use these tools to establish a DAC connection. |
One of the common recommendations for systems supporting a large number of connections is to increase the number of worker threads in an attempt to increase throughput. Although this may improve performance, it can also lead to a performance reduction.
When SQL Server starts up, it reserves a quantity of memory for each configured thread. The higher the number of threads, the more memory needs to be reserved. Further, this memory comes from the MemToLeave area, so the configured worker thread count is directly linked to the maximum memory value that can be safely configured.
The recommended maximum value for worker threads is 1024 in a 32-bit environment and 2048 in a 64-bit environment. As with all other configuration settings, the best value in almost all cases is the default (0). If you change the value, first verify it for stability in an accurate load-testing environment.
Finally, if you're upgrading from SQL Server 2000, the Maximum Worker Threads setting is kept at the SQL 2000 default value of 255. After the upgrade, change this value to 0, which allows SQL Server to determine the appropriate value based on the number and type of CPUs available to the instance.
7.2.3. Lightweight pooling
On servers with a large number of CPUs close to capacity, performance may benefit from enabling fiber mode, also known as lightweight pooling. With this setting in place, SQL Server creates fibers instead of threads. A fiber is a lightweight version of a thread that's able to switch context in user mode rather than kernel mode.
Systems experiencing a very high level of context switches per second (>20,000), a value measured using the Windows Performance tool, may experience some performance increase with this option enabled. In chapter 17, we'll cover the measurement and threshold value for this setting in more detail. Like all configuration options, the Use Windows Fibers (Lightweight Pooling) setting should only be changed from the default value after careful analysis in a testing environment capable of accurately simulating production load.
7.2.4. CPU affinity
SQL Server can be configured so that threads will only be assigned to particular CPUs. This setting is typically used in NUMA-enabled systems, or on systems used for environments where a certain level of CPU resource needs to be reserved for other purposes, for example, other SQL Server instances or applications, to prevent one SQL Server instance from dominating CPU usage.
On single instance servers dedicated to SQL Server, this setting is best left at the default, in which case threads will be balanced across all available CPUs.
The Processor Affinity option, along with the Fiber Mode, Maximum Worker Threads, and Boost SQL Server Priority settings, can be modified using sp_configure or via the Processors page of the Server Properties window in SQL Server Management Studio, as you can see in figure 7.5.
7.2.5. Maximum Degree of Parallelism
A commonly altered setting is Maximum Degree of Parallelism (MAXDOP), which controls the maximum number of CPUs that can be used in executing a single task. For example, a large query may be broken up into different parts, with each part executing threads on separate CPUs. Such a query is known as a parallel query.
Figure 7.5. The Processors page of the Server Properties window allows changes to CPU configuration settings: CPU Affinity, Maximum Worker Threads, Boost SQL Server Priority, and Use Windows Fibers (Lightweight Pooling).
In a typical OLTP system consisting of lots of short, simple transactions, multiple CPUs are valuable in their ability to service lots of simultaneous threads from multiple users. In contrast, a typical OLAP (reporting/data warehouse) application consists of a smaller number of much larger queries. It follows that splitting up a query into multiple chunks, with each chunk running on a separate CPU, is more suited to an OLAP system whereby large amounts of resources can be used to speed up the execution time of large queries.
During query compilation, SQL Server will decide whether to use a parallel query if the MAXDOP setting allows and if the estimated cost of executing the query in a serial manner on a single CPU exceeds a cost threshold. By default, MAXDOP is 0, meaning that SQL Server is left to decide the appropriate number of CPUs to use. You can set this value to 1, effectively disabling parallel queries, or to a specific number that limits the number of CPUs that can be used.
Max MAXDOP?In OLTP systems, use a maximum MAXDOP setting of 8, including systems with access to more than 8 CPU cores. The effort to split and rejoin a query across more than 8 CPUs often outweighs the benefits of parallelism. |
In some cases, parallel queries in an OLTP environment are chosen by SQL Server to circumvent poor design and maintenance practices, most often as a result of missing indexes or out-of-date statistics. For example, SQL Server may decide to perform an index scan rather than a lookup, in which case it may parallelize the query. A high incidence of parallel queries is typically accompanied by a large number of CXPACKET waits. In chapter 17, we'll spend more time analyzing wait types, including CXPACKET.
In typical OLTP environments, MAXDOP is often set to 1 to limit the CPU and memory impact from parallel queries. In such cases, the question needs to be asked as to why SQL Server is choosing parallel queries—that is, are the indexes being designed and maintained appropriately? We'll cover index design and maintenance in chapter 13.
One of the downsides from setting MAXDOP to 1 is that certain operations, such as index rebuilds, benefit greatly from parallelism but are unable to do so with a MAXDOP 1 setting. In such cases, you can specify the MAXDOP setting at a statement level. For example, the CREATE INDEX command, an example of which is shown here, accepts a MAXDOP parameter:
-- Use a MAXDOP hint to override the default server MAXDOP setting
CREATE NONCLUSTERED INDEX [IX_Address_StateProvinceID]
ON [Person].[Address] ([StateProvinceID] ASC)
WITH (MAXDOP=0)
GOIn this example, we specify MAXDOP = 0 to override the instance default MAXDOP setting, and thereby permit the index creation to be parallelized if SQL Server decides that's the best approach.
Figure 7.6. The Advanced tab of the Server Properties window allows changes to Server configuration settings, such as Max Degree of Parallelism and Cost Threshold for Parallelism.
Like all configuration options, Max Degree of Parallelism can be set using sp_configure, or via the Advanced page of a server's properties window in SQL Server Management Studio, as shown in figure 7.6.
7.2.6. Cost Threshold for Parallelism
If MAXDOP is left at the default value, or set to a number higher than 1, the threshold at which SQL Server will consider a parallel plan can be set through the Cost Threshold for Parallelism option. This value, specified in seconds, represents the estimated time the query would take if executed serially on a single CPU. Queries estimated to take longer than this will be considered for parallelism. The default value for this setting is 5.
In some cases, increasing this value is a better alternative to setting MAXDOP to 1 when dealing with a large number of (unwanted) parallel queries.
With these CPU configuration options covered, let's turn our attention now to some other general server settings. Like most of the CPU settings, these settings are also best left at their default values.