"Run Query Run" - A Fresh Look at SQL Wait Stats
summary
Demonstrations show wait stats point to real bottlenecks: a heavy insert spends write log = 14s of 30s (≈44%) when data and log share a slow drive; moving log to a fast drive drops log waits to near zero but exposes PAGEIOLATCH/PAGELATCH = ~11–17s; parallelism tests show CXPACKET waste goes from 291s at 20 threads to 168s at 10 and 64s at 4, with fastest runtime at MPP=4; scripts (SQLAuthority URL) find overindexed tables (LCK_M_IS) and missing/unused indexes; NOLOCK shows dirty reads during long inserts.
AI-written summary · about
chapters (8)
- 0:00Introduction to wait stats
- 2:50Query execution life cycle
- 5:36Interactive SQL quiz
- 8:15Using diagnostic scripts
- 13:30Disk performance demonstration
- 26:25Parallelism and CPU tuning
- 43:31Advanced SQL troubleshooting
- 51:31Index analysis and conclusion
related talks
58m"Run Query Run" - A Fresh Look at SQL Wait Stats
50mSupercharge your Code to Get Optimal Database Performance
58mTroubleshooting CPU Performance Issue for SQL Developers
51mHere’s the execution plan … now what?
19mStabilizing Performance with Query Store
01h 50mSQL Execution Internals – From Database to OS and Hardware Levels
47mLoad Testing Storage Performance for SQL Server
53mWhen to stop tuning a query