Microsoft SQL Server is a Win32 application that runs on the Windows NT orWindows NT Advanced Server operating systems. In some uncommon situationswhen SQL Server is running a compute-bound operation, the interactiveconsole responsiveness of Windows NT may be diminished. This articlediscusses characteristics of the Windows NT process scheduler (often calledthread scheduler or thread dispatcher) that relate to this behavior.
Windows NT and Windows NT Advanced Server (NTAS) use the same system ofscheduling Win32 processes and threads. In other operating systems, this isusually called process scheduling. With Windows NT, the unit of schedulingis the thread; hence, the term thread scheduling or dispatching. However,the term process will be used in this discussion, where appropriate. Theunit of scheduling time is called the time slice or quantum.
Windows NT uses 32 numerical thread priorities, ranging from one (thelowest) to 31 (the highest) with zero being reserved for system use.
Threads 1 through 15 are variable priority in that the scheduler adjuststhe priority based on thread behavior. The base priority of a thread is thebase level from which these upward adjustments are made. The currentpriority of a thread is called its dynamic priority. Interactive threadsthat yield before their time slice is up will tend to be adjusted upward inpriority from their base priority. Compute-bound threads that do not yield,consuming their entire time slice, will tend to have their prioritydecreased, but not below the base level. This arrangement is often calledheuristic scheduling. It provides better interactive performance and tendsto lessen the system impact of "CPU hog" threads.
Windows NT also uses additional priority adjustments based on other events,such as momentarily boosting thread priority when it returns from an I/Ocall, when returning from a user-mode subsystem call, and when receivingkeyboard input.
Priority threads 16 through 31 are termed real-time, and do not vary inpriority based on behavior. This provides more deterministic scheduling,which is useful for near-real-time systems.
Although there is a numerical priority for each thread, these are usuallyreferred to within a Win32 program by their process priority flag or threadpriority flag. There is a mapping of these flags to numerical threadpriorities, which is detailed in the Win32 SDK, Windows NT Resource KitVolume 3, and Knowledge Base article 106253
, "Program Priority andMultithreaded Applications."
In this article we primarily refer to these by their numerical equivalentto reduce confusion.
The key to understanding Windows NT thread scheduling and resultantapplication behavior is knowing the central algorithm used. This algorithmis very simple, and is the same one a number of other operating systemsuse. It is "highest priority ready thread gets run." A list of readythreads or processes exist which is often called the "dispatch queue" or"eligible queue." The queue entries are in order based on their individualpriority. A hardware-driven real-time clock or interval timer willperiodically interrupt, passing control to a device driver that calls theprocess or thread scheduler. The thread scheduler will take the highestpriority entry from the queue and dispatch it to run.
Thus, a hardware event drives the regular examination of thread states, andthe evaluation of which thread will run next. The frequency of thisoperation is quite rapid, often from 10 to 32 milliseconds on most systems.
Neither Windows NT nor many other operating systems use CPU quotas. Thescheduling algorithm merely selects the highest priority ready-to-runthread and allows it to run uninterrupted until the next clock tick, atwhich time the scheduler gets control and reevaluates which thread is thehighest priority and ready to run. However, if the previously runningthread is still highest priority, and still ready to run (has not blockedon I/O or otherwise changed states), it will be run again.
Also there is essentially no gradual or proportional control over theamount of CPU time a compute-bound thread receives. Often the misconceptionexists that by lowering the priority of a compute bound thread a certainamount, this will result in a proportional decrease in the CPU time itreceives. In actuality, it will receive just as much time as before, aslong as it is higher in priority than other threads. For example, a computebound thread could be diminished from priority 31 to 16, and as long as allother threads are at 15 or below, it will receive just as much time at 16as at 31, and its system impact will be just as great.
The scheduler attempts to minimize this situation for threads in thevariable class, which ranges from 1 to 15. However, highly compute-boundthreads can still degrade overall system responsiveness in somecircumstances. You can usually investigate these situations by usingPerformance Monitor to identify which threads are consuming CPU time. Theninspect the dynamic thread priority of the compute-bound thread withrespect to that of other slowly-responding threads. You will usually seethe compute-bound thread is equal or higher in priority than the others.
Schedulers of this type work remarkably well over a wide variety ofsituations, are well understood, and have low overhead. However, especiallywith compute-bound processes, they have limitations that are difficult toovercome. In these situations, the compute-bound process tends to eitherget all the available CPU time, or little to none. It is very hard tothrottle a compute-bound process so that it runs at a decent rate yet doesnot dominate the system.
An item that controls thread priority is the "tasking" option of theControl Panel System application. This allows you to control the degree offoreground priority boost that normal priority class applications receive.A foreground application is one that is made active by selecting it on theWindows NT desktop, thus, bringing it to the foreground. All otherapplications running are then termed background applications with respectto the foreground.
The Windows NT default is "Best Foreground Application Response Time,"which results in a foreground application's priority being increased twolevels.
Neither foreground boost nor the heuristic priority adjustment will causean application to change classes from normal to realtime, or vice versa.The priority adjustment from these sources is bounded by the process class.
The intermediate Control Panel tasking option is "foreground applicationmore responsive than background," which equates to a foreground boost ofone level.
The last Control Panel tasking option is "foreground and backgroundapplications equally responsive," which deactivates any foreground boost.The SQL Server for Windows NT setup program selects this setting duringinstallation. This is mainly to prevent the database server from being CPU-starved by foreground applications and is the best setting for a dedicatedSQL Server.
The SQL Server setup program offers two options that affect the priority atwhich SQL runs, hence, system behavior. These two options are listedunder the "Set server options" option of setup and are called "DedicatedMultiprocessor Performance" and "Boost SQL Server Priority."
On SQL Server 6.0 the "Dedicated Multiprocessor Performance" setupoption has been replaced by the sp_configure advanced optioncalled "SMP concurrency". See the version 6.0 documentation for moreinformation on this option.
Selecting Dedicated Multiprocessor Performance on a symmetricmultiprocessor (SMP) computer increases the scalability improvement thatmultiple CPUs have on SQL Server's performance. Selecting this option isnot necessary for SQL Server to benefit from multiple CPUs, but it doesincrease the amount of improvement. A side effect of selecting the optionis that it causes SQL Server to run at a priority of 15. This helpsminimize scheduling overhead and obtain maximum benefit from SMP.
Selecting "Boost SQL Server Priority" is possible on either a uniprocessoror SMP computer. When selected on a uniprocessor, SQL Server runs atpriority 15, which is high but within the variable class of priorities thatrange from 1 through 15. When selected on a SMP computer, SQL Server runsat priority 24, which is midway into the realtime class of priorities thatrange from 16 through 31.
This chart displays the priority at which SQL Server for Windows NT runsdepending on configuration:
Priority Multiprocessor (MP) Support Boost SMP Computer------------------------------------------------------------------- 7 n/a OFF No 15 n/a ON No 7 OFF OFF Yes 24 OFF ON Yes 15 ON OFF Yes 24 ON ON Yes
With the previous in mind, a number of behaviors concerning SQL Server andthe Windows NT thread scheduler are more understandable. One of theseconcerns when SQL Server for Windows NT is performing a CPU-intensiveoperation, such as some types of joins. Depending on the exact operationand the amount of memory available, the operation could be done entirely incache--effectively becoming CPU-bound. During this period SQL Server, evenwhen running at the default priority of 7, could degrade interactiveperformance. The conditions necessary for this behavior to occur arerelatively narrow and do not happen frequently.
Another situation where this might occur is if a large single transactionor a large number of smaller transactions needed processing during start uprecovery, as would be the case if the server was abruptly shut down whilethese transactions were in progress. Depending on the amount of SQL cachebuffer memory and the exact disk subsystem used, a lengthy recovery cantake place mostly or entirely in cache. Because of this it would be CPUbound and could degrade interactive performance during this interval.
On a computer dedicated mainly as a database server, interactive consoleperformance is usually not a priority. However, if necessary, as aworkaround SQL Server can be started from the command line in the IDLEpriority class with a command like:
start /low sqlservr -c -dc:\sql\data\master.dat
Another example is when SQL Server does a large disk init. In this case,the operation is extremely I/O bound, and the lack of any scheduler I/Oquotas causes similar behavior to a CPU bound operation.
Using the Dedicated MultiProcessor Support option when on an SMP computerdedicated to SQL Server can improve performance. This will also result inSQL Server running at priority 15. In some CPU-intensive operations, thiscan cause logins to be slower (or the Windows NT console responsiveness tobe sluggish) as SQL Server may allow less CPU time for other systemprocesses.
Using the Boost SQL Server Priority on a SMP computer dedicated to SQLServer can also improve performance, although often not as much as the MPsupport option. Boost Priority results in SQL Server running at priority24, which is in the realtime class, and consequently can have similar ormore pronounced effects on Windows NT responsiveness as DedicatedMultiprocessor Support.
If the SQL Server computer is used for purposes other than a pure OLTPenvironment (for example ad-hoc or decision support queries, or use as afile server, non-SQL application server, or interactive use from theconsole), for optimal overall responsiveness it is generally best toreserve the version 4.2 "Dedicated Multiprocessor Support" option forcomputers with three and more processors, and to not use "Boost Priority,"nor the version 6.0 "SMP concurrency" setting of -1. Version 6.0 willautomatically use "Dedicated Multiprocessor Support" on computers withthree or more processors. Using either Boost Priority, 4.2 "DedicatedMultiprocessor Support" on greater than three processor computers, or 6.0"SMP Concurrency" of anything other than 0 (auto-configure) cansignificantly impact Windows NT console responsiveness and overall systembehavior in a non-OLTP environment. Hence, Microsoft does not recommendthese options be used outside of a pure OLTP environment characterized bystrictly controlled short transactions, no decision support, or ad-hocqueries, and no interactive use of the Windows NT console. The defaultsettings on version 6.0 provide the best overall behavior across the widestvariety of circumstances and should be used in the vast majority of cases.
Additional Suggested Reading
"Inside Windows NT" by Helen Custer, ISBN 1-55615-481-X
"Advanced Windows NT" by Jeffrey Richter, ISBN 1-55615-567-0
"Optimizing Windows NT" by Russ Blake, ISBN 1-55615-619-7
(Vol. 3 of the Windows NT Resource Kit)
"Windows NT SDK," Microsoft Developer Network CD-ROM
"Operating Systems, Design and Implementation" by Andrew S. Tannenbaum,
"Modern Operating Systems" by Andrew S. Tannenbaum, ISBN 0135881870
Microsoft Knowledge Base article Q96418