Microsoft® SQL Server® 2012 Bible

931

CHAPTER

39

Wait States

IN THIS CHAPTER

Exploring the SQL Server OS

Examining Wait Statistics

Identifying Common Red-Flag Wait Types

Finding Other Ways to Gather Wait Data

I

magine there are three people in front of you in line at your favorite coffee shop. You eventually

get to the front of the line and give the barista your order and patiently step aside. Two of the

customers in front of you receive their orders quickly. The barista then hands you your coffee

before the customer who was waiting directly in front of you because her request likely required

more work to complete than yours.

This process is not unlike how SQL Server handles requests. SQL Server assigns requests (tasks) to

worker threads, which wait in a queue to get their turn to run on the CPU (scheduler).

The SQL Server OS

To execute tasks, SQL Server uses its own custom operating system, referred to as the SQLOS, which

serves as a thin layer between SQL Server and the Windows Operating System. SQLOS provides

memory management, task scheduling, and IO completion, along with a number of functions for

SQL Server.

The SQLOS is a nonpreemptive, or cooperative, operating system. This means that worker threads

self-regulate the amount of time they use the scheduler. A worker can remove itself from the sched-

uler if its time slice is up. SQL Server operates this way because it has a better understanding as to

how to most effi ciently handle and schedule its work than the general Windows OS does.

To contrast, Windows is a preemptive operating system. This means that processes are taken on and

off of the CPU at the decision of the OS (typically in a round-robin fashion).

c39.indd 931c39.indd 931 7/31/2012 10:04:43 AM7/31/2012 10:04:43 AM

http://www.it-ebooks.info