Here’s the execution plan … now what?
summary
Hands-on diagnostics for real plans: shows a dashboard proc causing resource semaphore waits where a 47 MB memory grant 47MB → 14MB drops by splitting the page‑1 path and forcing a top-end sort instead of a full SORT, identifies heavy memory-users (SORT, HASH MATCH) via execution-plan estimates and the Execution Plan Reference, uses Query Store to find plans with 6 ms → 300 ms variance from bad parameter sniffing, and demonstrates fixes: OPTION(RECOMPILE), OPTIMIZE FOR, safe dynamic SQL and small specialized procs.
AI-written summary · about
chapters (13)
- 0:00Introduction
- 1:34Theory versus execution plans
- 4:47Resource semaphor waits in SQL Server
- 8:47Paginated sales report analysis
- 12:20Memory-heavy operators in plans
- 16:21Optimizing memory grants for dashboards
- 27:29Bug in optimized query
- 29:15Erratic query performance patterns
- 32:06Query Store for performance issues
- 38:29Bad parameter sniffing causes
- 43:06Fixing bad parameter sniffing
- 47:42Dynamic SQL to avoid sniffing
- 50:13Balancing optimization and business
related talks
51mHere’s the execution plan … now what?- 51mHere’s the execution plan … now what?
44mImproving Performance with Intelligent Query Processing
51mAdvanced Plan Explorer Usage for Tuning Execution Plans
52mUnderstanding PostgreSQL Query Plans (aka EXPLAIN)
58m"Run Query Run" - A Fresh Look at SQL Wait Stats
19mStabilizing Performance with Query Store
53mBanishing Slow Queries with Intelligent Query Processing