"Run Query Run" - A Fresh Look at SQL Wait Stats
summary
Live demos extract wait stats from SQL Server, show a parallel query eating CPU but producing only 75s of useful work while CXPACKET waste = 389s wasted, and demonstrate that forcing down parallelism (MAXDOP) makes the same job take 29s → 31s yet frees 15 CPUs so capacity rises (max degree 1: 29s→31s but frees 15 CPUs). Scripts, execution plans and a Python batch prove CPU context switching and memory grant/resource_semaphore issues (7 of 10 batches delayed ~89s), and clearing wait stats isolates problem sources fast.
AI-written summary · about
chapters (8)
- 0:00Introduction to wait stats
- 5:42Understanding query states
- 10:55CPU and parallelism issues
- 18:05Demo: Parallelism and MAXDOP
- 24:17Optimizing parallel queries
- 34:42Resource issues and bottlenecks
- 42:38Deep dive into resource semaphores
- 50:05Conclusion and Q&A
related talks
59m"Run Query Run" - A Fresh Look at SQL Wait Stats
51mHere’s the execution plan … now what?- 51mHere’s the execution plan … now what?
- 51mHere’s the execution plan … now what?
53mBanishing Slow Queries with Intelligent Query Processing
47mLoad Testing Storage Performance for SQL Server
58mTroubleshooting CPU Performance Issue for SQL Developers
49mHidden Gems in SQL Server 2022 Database Engine