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:
http://support.microsoft.com/en-us/kb/2806535

Paul Randall Blog on Waitstats:

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

RedGate Documentation on Common Wait Types and short Explanation:

http://documentation.red-gate.com/display/SM4/List+of+common+wait+types


WaitStats

Query Plans which uses Parallel Execution Plan

Latch Wait Types:
http://www.sqlskills.com/blogs/paul/most-common-latch-classes-and-what-they-mean/

SPID Specific Waits:

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

http://sqlperformance.com/2014/06/io-subsystem/knee-jerk-waits-pageiolatch-sh

Leave a Reply

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

*