Export (0) Print
Expand All

Thread and Fiber Execution

SQL Server 2000

Microsoft® Windows® uses a numeric priority ranging from 1 through 31 (0 is reserved for operating system use) to schedule threads for execution. When several threads are waiting to execute, Windows dispatches the thread with the highest priority.

Each instance of Microsoft SQL Server™ 2000 defaults to a priority of 7, which is called the normal priority. This gives SQL Server threads a high enough priority to get adequate CPU resources without adversely affecting other applications. The priority boost configuration option can be used to increase the priority of the threads from an instance of SQL Server to 13, which is called high priority. 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 running only instances of 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. If you are running multiple instances of SQL Server on a computer, and turn on priority boost for only some of the instances, the performance of any instances running at normal priority can be adversely affected. 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.

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.

See Also

query wait Option

© 2014 Microsoft