SQL Server Waits and Queues

SQL Server Waits and Queues :

I watched Paul Randall Videos on Wait Types and which inspired me to write a simple post on wait types on logical way.

We all have written queries, TSQL statements to perform CRUD ( Create, Read, Update , Delete ) Operations.
When you think about simple Read Operation, we issue SELECT SQL Statement.Sometimes, we get the results immediately and sometimes it takes a bit. We all have experienced that. When some queries takes few seconds to minutes, we all wonder whats causing the hold up.Thats where the wait types comes handy. Based on this, we can diagnose where the time being spent what sub-system might be the bottle-neck and where we need to pay attention.

Lets say, we issue simple SELECT Statement from Management Studio. Management Studio will be our Client App. SQL Server do , PARSE -> BIND -> OPTIMIZE -> EXECUTE operations for that query. It can fetch the data from Memory if the data is found in Memory . If not, it will fetch the data from Hard Disk which would result in Physical IO Operations.  Some data operations, can use Parallelism or use single CPU.

There could be waits while sending the data from Memory to Client App ( Network ) , Hard Disk to Memory ( IO Wait Types )  or CPU . Following Diagram explains different wait types.

Microsoft Recommendation on MAXDOP Settings:

Paul Randall Blog on Waitstats:


RedGate Documentation on Common Wait Types and short Explanation:



Query Plans which uses Parallel Execution Plan

sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
max(//p:RelOp/@Parallel)', 'float') > 0

Latch Wait Types:

Most common latch classes and what they mean

SPID Specific Waits:

SELECT  wait_type ,
        last_wait_type ,
        command ,
FROM    sys.dm_exec_requests
WHERE   session_id = 60

--wait_type			last_wait_type	command		wait_resource

PAGEIOLATCH_SH  – (SHare) waiting for a data file page to be brought from disk into the buffer pool so its contents can be read
PAGEIOLATCH_EX or PAGEIOLATCH_UP – (EXclusive or UPdate) waiting for a data file page to be brought from disk into the buffer pool so its contents can be modified


You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *