Rob Risetto

SQL Server Wait Statistics
Capture, Report, Analyse
Rob Risetto
Principal Consultant with StrataDB
([email protected])
About Rob
SQL Server consultant and cofounder of StrataDB
StrataDB - Brisbane based SQL Server Consultancy
covering SQL Engine and Business Intelligence
Worked with SQL Server for 14 years
My focus area
Performance Tuning
High Availability
SQL Engine Architecture and Design
SQL Automation via Powershell
• Quick theory refresher on Wait Statistics
• How/Why
• Common Wait Types
• Wait Statistic Analysis Guidelines
• Capture, Report & Analyse Demos using
• DMVs
• SQL Server 2012 Performance Dashboard
• Powershell Charting
Wait Statistics – How/Why
Rather than reinventing the wheel…
Let’s use Joe Sack (Microsoft Corporation) slides
Wait Types – Common Ones
Represents a SQLOS worker (thread) that has voluntarily yielded the CPU to another
May indicate CPU pressure if very high percentage of all waits (> 80%).
A query is parallelized and the parallel threads are not given equal amounts of work to
do, or one thread blocks.
High number for OLTP system is not good.
This is where SQL Server is waiting for a data page to be read from disk into memory.
May indicate Disk IO or Memory pressure.
Wait Statistics – Common Ones
Occurs when a task is waiting on a latch for a buffer that is not in an I/O request.
PFS, SGAM, and GAM contention that can occur in tempdb
Index hot-spot with concurrent inserts into an index with an identity value key
This is the log management system waiting for a log flush to disk.
May indicate Disk IO
This is simply the thread waiting for a lock to be granted and indicates blocking
Wait Statistics – Common Ones
This is SQL Server waiting for IOs to complete.
May indicate Disk IO bottleneck
SQL Server is waiting for a client to finish consuming data.
Bad programming or network issue.
Queries waiting for execution memory (the memory used to process the query
operators - like a sort)
May indicate memory pressure or a very high concurrent workload.
Paul Randal’s Wait Stats Survey
Guidelines for Analysis
Look at the Accumulated Waits
- Use to focus investigation effort on performance issue
- Is a wait type a high percentage of all waits
- Is Signal time > 15% - 20% (may indicate CPU pressure)
- Is there a high average wait time for a wait type
- eg PageIOLatch average > 20 ms
Guidelines for Analysis
Correlate Waits with related DMVs and Perfmon
- If IO wait compare IO File Stats, Avg Disk Secs/Read, Avg Disk Secs/Write
- If CPU waits, check Processor Time %, sys.dm_os_schedulers (runnable_task_count)
- If Memory waits, check Page Life Expectancy, Pending Memory Grants
(query workspace)
Guidelines for Analysis
Look at Delta Waits to determine current wait statistics
Capture Waits history for troubleshooting
- Review history to identify high wait periods
- Combine with other DMV and Perfmon history capture to correlate and diagnose
Use sys.dm_os_waiting_tasks to drilldown current waiting tasks
- see interesting patterns
- see the query associated with the wait
Real World Workloads & Demos
Useful links
Paul Randal (SQL Skills) Waits Stats blog
Glenn Berry 911 Emergency DMVs
Hammerora tool article
Response Time Analysis using Extended Events
Useful links
SQL Server 2012 Performance Dashboard Download
SQL Server 2005 Waits and Queues
Powershell Charting with MS Charting Controls
Powershell LibraryChart.ps1 download
Rob’s Contact Details
Email: [email protected]
Mobile: 0417 322 000

similar documents