Threads are an important part of a multitasking operating system, and they enable SQL Server to do many things at once. Threads are not processes, however. They are concurrent execution paths that allow applications to use the CPU more effectively.
SQL Server tries to match threads to user connections. When the number of threads that are available is greater than the number of user connections, at least a one-to-one ratio of threads to user connections exists, which allows each user connection to be handled uniquely. When the number of threads available is less than the number of user connections, SQL Server must pool threads; as a result, the same thread may serve multiple user connections, which can reduce performance and response time if additional resources are available and are not being used.
Normally, the operating system handles threads in kernel mode, but it handles applications and user-related tasks in user mode. Switching between modes, such as when the kernel needs to handle a new thread, requires CPU cycles and resources. To allow the application to handle threading directly, you can use fibers. Switching fibers does not require changing modes and therefore can sometimes improve performance.
Another way to improve performance is by increasing the priority of SQL Server threads. Normally, threads have a priority of 1 to 31, and higher priority threads get more CPU time than lower priority threads. Higher priority threads can also preempt lower priority threads, forcing threads to wait until higher priority threads finish executing. By increasing thread priority, you can give the threads a higher preference for CPU time and ensure that other threads do not preempt them.
Note
The complete range for thread priority is 0 to 31. Thread priority 0 is reserved for operating system use.
You configure worker threads, fibers, and thread priority using the Server Properties dialog box. Go to the Processors page and use these options:
Maximum Worker Threads. Sets the maximum number of threads. By default, the value is set to 0, which allows SQL Server to configure the number of worker threads. However, you can use any value from 10 to 32,767. On a busy server with many user connections, you may want to increase this value. On a slow server with few connections, you may want to decrease this value. Computers with multiple processors can concurrently execute one thread per CPU. Microsoft recommends a maximum setting on 32-bit systems of 1024.
Boost SQL Server Priority. Increases the priority of SQL Server threads. Without boosting, SQL Server threads have a priority of 7 (normal priority). With boosting, SQL Server threads have a priority of 13 (high priority). On a dedicated system running only SQL Server, this option can improve performance. However, if the server runs other applications, the performance of those applications may be degraded.
Use Windows Fibers (Lightweight Pooling). Configures SQL Server to use fibers, which it can handle directly. SQL Server still needs threads to carry out tasks. SQL Server allocates one thread per CPU, and then allocates one fiber per concurrent user connection up to the Maximum worker threads value. You must restart the server to apply this option.
Tip
Fibers work best when the server has multiple CPUs and a relatively low user-to-CPU ratio. For example, on an Enterprise installation with 32 CPUs and 250 users, you may see a noticeable performance boost with fibers. But if you have a system with eight CPUs and 5000 users, you may see performance decrease with fibers.
You can use sp_configure to set fibers, maximum worker threads, and priority boost. The commands are:
exec sp_configure "lightweight pooling", <0 or 1> exec sp_configure "max worker threads", <integer value> exec sp_configure "priority boost", <0 or 1>
When setting lightweight pooling (fibers) and priority boost, you use 0 to disable and 1 to enable.