simple wait states

Klaus Aschenbrenner has a simple overview of wait states.

Unless the query gets the cpu and finishes in 4 ms, all queries in MS SQL are at times waiting.

A query that is being processed has to yield its place by cooperative Scheduling, which is enforced for SQL processes by the SQLOS.  Every 4 milliseconds, the query is moved to the ‘suspended’ state to let other queries play.  Then ideally, it moves to the runnable state and waits until it it has another chance to play.

But at times, or even the first time around, it might not get on the CPU.  If the storage manager can not locate the data needed by the query, the query gets moved immediately to suspended until the I/O system can find the data on disk, read it into the data cache, and tell the storage manager that a pointer to the data can be given to the query.

A query might in the middle of its run need more data.  This could happen if some of the data was loaded but there is not enough room for all the data, and now the additional pages need to be loaded.  Or a subquery.  Or the Query analyzer’s plan needs to write something to tempdb (for example a sort).  Anything that is going to require reading from the very slow physical media is going to make the query wait.

Now, suppose the data is in memory, but another process is using it.  OK, wait 4ms and it will be come free.  But it will take time to request and get granted a lock on the data for our query – it has to wait for the resource to become free, and then get the lock.

There are other, non-normal, causes for waits that indicate something is not ideal, but the point is that wait states are normal.  And all wait state information is stored by SQL Server and can be examined by the Dynamic Management View sys.dm_os_wait_stats.  If the server is running OK, the wait states you see with this DMV are normal.  If the server slows down, the reason for the slowdown will be seen as one or more wait states.  The wait state is the symptom, not the cause (slow storage, bad indexes, poor plans, locking and blocking, CPU pressure) but will start you looking, hopefully in the right places.

Advertisements