Databases Reference
In-Depth Information
SQL Server Waits
Whenever a task in SQL Server has to wait for something before it can continue, information on the
reason for the wait is tracked by SQL Server and can be viewed through Dynamic Management Views
(DMVs). Aggregating this data across all connections will give you a performance profile for SQL
Server and tracking it for a particular connection will allow you to see what the bottleneck for a specific
workload is.
All waits in SQL Server are categorized into wait types and can be grouped into three areas:
❑
Resource waits:
Occur when the requested resource is unavailable.
Queue waits:
Occur when a worker is idle, waiting for work.
❑
External waits:
Occur when waiting for an external event.
❑
Resource waits, which include I/O, locking, and memory, tend to be the most common and provide the
most actionable information and so receive the most attention in this chapter.
Architecture
The SQL Server Operating System (SQLOS) uses schedulers to manage the execution of user requests. The
number of schedulers defaults to the number of logical CPUs in the server because only one scheduler at
a time can be executing on a CPU. If you have a 4 CPU server, you'll have four schedulers, 8 if they are
dual-core, and 16 if they are dual-core and hyper-threaded. See Chapter 6 for more details on CPU types
and configuration.
Within a scheduler, a session (actually a task associated with a session) can be in one of three states:
running, runnable, or suspended. (There are also three other states: pending, spinloop, and done;
however, they're not important to the concept being discussed here.) Only one session can be
running
on a scheduler at any one time. All sessions waiting to run are in a
runnable
queue. Sessions that stopped
executing to
wait
for something have a status of
suspended
. This is illustrated in Figure 4-1.
Windows uses a scheduling model called pre-emptive scheduling, which basically means that it decides
when a thread running a task needs to make way for another task and switches it out. This works great
for a server running lots of different services of equal importance, but for a server that is dedicated to
running SQL Server it would be bad because a SQL Server task might get switched out unexpectedly
to give a minor Windows task some CPU time. SQL Server instead has its own scheduler that uses
a non-preemptive or cooperative model whereby it relies on all of its threads to yield processor time
whenever that thread has to wait for something. It's a more efficient model for SQL Server because it
knows that its threads will yield appropriately. For Windows, however, the threads could be running
anything, so the pre-emptive model is better to guarantee performance.
In the diagram you can see that session_id 55 is currently executing, there are three sessions waiting
on resources (which you'll see descriptions of later in this chapter), and there are four sessions in the
runnable queue. The runnable queue contains sessions that are waiting to be scheduled some CPU
time, so this represents a pure wait on the CPU, which we can translate into CPU pressure. Time in
the runnable queue is measured as
signal wait
time.
Search WWH ::
Custom Search