Microsoft® Windows® schedules threads for execution based on a numeric priority ranging from 1 through 31 (0 is reserved for operating system use). When several threads are waiting to execute, Windows dispatches the thread with the highest priority.
Microsoft SQL Server™ defaults to a priority of 7, which is called the normal priority. This gives SQL Server threads a high enough priority to get enough CPU resources without adversely affecting other applications. The priority boost configuration option can be used to increase the priority of SQL Server threads to 13, or high. This setting gives SQL Server threads a higher priority than most other applications. Thus, SQL Server threads will tend to be dispatched whenever they are ready to run and will not be preempted by threads from other applications. This can improve performance when a server is only running SQL Server and no other applications. If a memory-intensive operation occurs in SQL Server, however, other applications are not likely to have a high-enough priority to preempt the SQL Server thread. The performance of other applications and components on the server can be degraded if priority boost is turned on, so it should only be used under tightly controlled conditions.
SQL Server does not preempt running threads. A thread that does not voluntarily yield just keeps processing. The time slice configuration option is used prevent a looping thread from processing forever. time slice defines how many milliseconds a process can execute without yielding. If a thread reaches the time slice value, SQL Server assumes it is stuck in a loop and terminates the task. This is uncommon and the default time slice setting rarely needs to be changed. Setting the value too low hurts performance by increasing time checks, and it also may start prematurely terminating tasks that simply are doing a lot of work.
Threads occasionally need to wait for another thread or threads to free a resource. The threads should not be allowed to go into an indefinite wait. There are three server configuration options that control waits:
For some resources, SQL Server uses a spinlock to synchronize access to a resource. The spin counter option limits how many times a thread can spin on a lock before it temporarily sleeps. This allows other tasks to run. When the thread wakes up, it will retry the spin lock.
This limits how long a thread waits for the completion of an asynchronous I/O to complete on Microsoft Windows NT®.
Some Transact-SQL statements require large amounts of memory for operations, such as sorts. If there is not enough memory available, the thread waits for memory to be freed. The query wait option limits how long a thread can wait for memory.
| spin counter Option | query wait Option |
| resource timeout Option |