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
Query Plans which uses Parallel Execution Plan
select
p.*,
q.*,
cp.plan_handle
from
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
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0
Latch Wait Types:
SPID Specific Waits:
SELECT wait_type ,
last_wait_type ,
command ,
wait_resource
FROM sys.dm_exec_requests
WHERE session_id = 60
--wait_type last_wait_type command wait_resource
--PAGEIOLATCH_SH PAGEIOLATCH_SH INSERT 13:1:4721379
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