(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

In today’s blog posting I want to talk about a very interesting topic: in which execution state is a query waiting on “something”? By definition we can all agree that a query always waits in the SUSPENDED state. But this behavior is not really true. So let’s talk about it…

Query Life Cycle

Every time when SQL Server executes a query, the query is moved through 3 different execution states:

• RUNNING
• SUSPENDED
• RUNNABLE

A query is only in the RUNNING state as long as useful work can be done. As soon as the query waits on some resources (like Pages, Locks) that are currently not available, the Scheduler moves that query into the SUSPENDED state. The query has to wait now in the SUSPENDED state until the requested resource becomes available.

And afterwards the Scheduler moves the query into the RUNNABLE state. The query waits in the RUNNABLE state until a CPU becomes available, and finally the query is put again into the RUNNING state. All these State Transitions are tracked by the Wait Statistics in SQL Server, and they are very important for Performance Troubleshooting.

Preemptive Waits

Within the code base of SQL Server, queries are executed with a Cooperative Scheduling. This means that a query itself decides how long it will run actively on a CPU. In SQL Server this so-called Quantum is about 4 milliseconds. After these 4 milliseconds the query voluntarily yields the CPU and moves back into the RUNNABLE state. In that case SQL Server reports for this query a SOS_SCHEDULER Wait Type. And in the mean time another query can move from the RUNNABLE state into the RUNNING state.

But things are changing now when we talk about so-called Preemptive Function Calls in SQL Server. A Preemptive Function Call occurs in SQL Server, when a query leaves the code base of SQL Server and calls for example Win32 API functions. When you call a Win32 API function, a query has no direct control anymore how long that function call takes. Maybe the called Win32 API function calls a buggy device driver that ends up in an infinite loop. Who knows?

And therefore SQL Server reports you during a Preemptive Function Call a Preemptive Wait. But that Preemptive Wait is reported in the RUNNING state! SQL Server actively executes a Win32 API function and “waits” in the RUNNING state until the function call is finished. As soon as the function call is finished, the Cooperative Scheduling continues within SQL Server.

A traditional example of a Preemptive Wait is the Auto Growth operation on the Transaction Log. SQL Server uses here the Win32 API function WriteFileGather to perform the Auto Growth operation. And because of that, SQL Server reports you the Preemptive Wait Type PREEMPTIVE_OS_WRITEFILEGATHER during the Auto Growth operation – but in the RUNNING State:

Summary

Never, ever take anything for granted in SQL Server! There are always some side-effects in which SQL Server reacts differently as expected. And Preemptive Waits are such a scenario. With Preemptive Waits a SQL Server query will “wait” in the RUNNING state as long as a Preemptive Function Call into the Win32 API takes.

Thanks for your time,

-Klaus