INFO: SQL Server and Windows NT Thread SchedulingID: Q111405
|
Microsoft SQL Server is a Win32 application that runs on the Windows NT or Windows NT Advanced Server operating systems. In some uncommon situations when SQL Server is running a compute-bound operation, the interactive console responsiveness of Windows NT may be diminished. This article discusses characteristics of the Windows NT process scheduler (often called thread scheduler or thread dispatcher) that relate to this behavior.
Windows NT and Windows NT Advanced Server (NTAS) use the same system of
scheduling Win32 processes and threads. In other operating systems, this is
usually called process scheduling. With Windows NT, the unit of scheduling
is the thread; hence, the term thread scheduling or dispatching. However,
the term process will be used in this discussion, where appropriate. The
unit of scheduling time is called the time slice or quantum.
Windows NT uses 32 numerical thread priorities, ranging from one (the
lowest) to 31 (the highest) with zero being reserved for system use.
Threads 1 through 15 are variable priority in that the scheduler adjusts
the priority based on thread behavior. The base priority of a thread is the
base level from which these upward adjustments are made. The current
priority of a thread is called its dynamic priority. Interactive threads
that yield before their time slice is up will tend to be adjusted upward in
priority from their base priority. Compute-bound threads that do not yield,
consuming their entire time slice, will tend to have their priority
decreased, but not below the base level. This arrangement is often called
heuristic scheduling. It provides better interactive performance and tends
to 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/O
call, when returning from a user-mode subsystem call, and when receiving
keyboard input.
Priority threads 16 through 31 are termed real-time, and do not vary in
priority 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 usually
referred to within a Win32 program by their process priority flag or thread
priority flag. There is a mapping of these flags to numerical thread
priorities, which is detailed in the Win32 SDK, Windows NT Resource Kit
Volume 3, and Knowledge Base article Q106253, "Program Priority and
Multithreaded Applications."
In this article we primarily refer to these by their numerical equivalent
to reduce confusion.
The key to understanding Windows NT thread scheduling and resultant
application behavior is knowing the central algorithm used. This algorithm
is very simple, and is the same one a number of other operating systems
use. It is "highest priority ready thread gets run." A list of ready
threads or processes exist which is often called the "dispatch queue" or
"eligible queue." The queue entries are in order based on their individual
priority. A hardware-driven real-time clock or interval timer will
periodically interrupt, passing control to a device driver that calls the
process or thread scheduler. The thread scheduler will take the highest
priority entry from the queue and dispatch it to run.
Thus, a hardware event drives the regular examination of thread states, and
the evaluation of which thread will run next. The frequency of this
operation is quite rapid, often from 10 to 32 milliseconds on most systems.
Neither Windows NT nor many other operating systems use CPU quotas. The
scheduling algorithm merely selects the highest priority ready-to-run
thread and allows it to run uninterrupted until the next clock tick, at
which time the scheduler gets control and reevaluates which thread is the
highest priority and ready to run. However, if the previously running
thread is still highest priority, and still ready to run (has not blocked
on I/O or otherwise changed states), it will be run again.
Also there is essentially no gradual or proportional control over the
amount of CPU time a compute-bound thread receives. Often the misconception
exists that by lowering the priority of a compute bound thread a certain
amount, this will result in a proportional decrease in the CPU time it
receives. In actuality, it will receive just as much time as before, as
long as it is higher in priority than other threads. For example, a compute
bound thread could be diminished from priority 31 to 16, and as long as all
other threads are at 15 or below, it will receive just as much time at 16
as at 31, and its system impact will be just as great.
The scheduler attempts to minimize this situation for threads in the
variable class, which ranges from 1 to 15. However, highly compute-bound
threads can still degrade overall system responsiveness in some
circumstances. You can usually investigate these situations by using
Performance Monitor to identify which threads are consuming CPU time. Then
inspect the dynamic thread priority of the compute-bound thread with
respect to that of other slowly-responding threads. You will usually see
the compute-bound thread is equal or higher in priority than the others.
Schedulers of this type work remarkably well over a wide variety of
situations, are well understood, and have low overhead. However, especially
with compute-bound processes, they have limitations that are difficult to
overcome. In these situations, the compute-bound process tends to either
get all the available CPU time, or little to none. It is very hard to
throttle a compute-bound process so that it runs at a decent rate yet does
not dominate the system.
An item that controls thread priority is the "tasking" option of the
Control Panel System application. This allows you to control the degree of
foreground priority boost that normal priority class applications receive.
A foreground application is one that is made active by selecting it on the
Windows NT desktop, thus, bringing it to the foreground. All other
applications running are then termed background applications with respect
to the foreground.
The Windows NT default is "Best Foreground Application Response Time,"
which results in a foreground application's priority being increased two
levels.
Neither foreground boost nor the heuristic priority adjustment will cause
an 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 application
more responsive than background," which equates to a foreground boost of
one level.
The last Control Panel tasking option is "foreground and background
applications equally responsive," which deactivates any foreground boost.
The SQL Server for Windows NT setup program selects this setting during
installation. This is mainly to prevent the database server from being CPU-
starved by foreground applications and is the best setting for a dedicated
SQL Server.
The SQL Server setup program offers two options that affect the priority at
which SQL runs, hence, system behavior. These two options are listed
under the "Set server options" option of setup and are called "Dedicated
Multiprocessor Performance" and "Boost SQL Server Priority."
On SQL Server 6.0 the "Dedicated Multiprocessor Performance" setup
option has been replaced by the sp_configure advanced option
called "SMP concurrency". See the version 6.0 documentation for more
information on this option.
Selecting Dedicated Multiprocessor Performance on a symmetric
multiprocessor (SMP) computer increases the scalability improvement that
multiple CPUs have on SQL Server's performance. Selecting this option is
not necessary for SQL Server to benefit from multiple CPUs, but it does
increase the amount of improvement. A side effect of selecting the option
is that it causes SQL Server to run at a priority of 15. This helps
minimize scheduling overhead and obtain maximum benefit from SMP.
Selecting "Boost SQL Server Priority" is possible on either a uniprocessor
or SMP computer. When selected on a uniprocessor, SQL Server runs at
priority 15, which is high but within the variable class of priorities that
range from 1 through 15. When selected on a SMP computer, SQL Server runs
at priority 24, which is midway into the realtime class of priorities that
range from 16 through 31.
This chart displays the priority at which SQL Server for Windows NT runs
depending 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
start /low sqlservr -c -dc:\sql\data\master.dat
(Vol. 3 of the Windows NT Resource Kit)
ISBN 0136374069 Additional query words: sql6 Windows NT
Keywords : kbenv SSrvWinNT
Version : winnt:6.0,6.5
Platform : winnt
Issue type : kbinfo Last Reviewed: August 2, 1999