Here’s the execution plan … now what?

on YouTube ↗51m

" summary

Examines real plans to find the exact culprit: a dashboard paginated_sales_report uses a temp table and a full Sort that requests ~47 MB per execution, multiplying into RESOURCE_SEMAPHORE waits when ~999 of 1000 calls request page 1; rewriting page 1 to use TOP 20 (no temp table) forces a top-end sort and drops the grant to ~14 MB (fixing the semaphore problem). Uses execution plans, Query Store and plan properties (memory grant, estimated rows, hash vs sort) and shows optional-parameter fixes: split procedures, safe Dynamic SQL or OPTION(RECOMPILE).

AI-written summary · about

" chapters (13)

1. 0:00Introduction
2. 1:34Theory versus execution plans
3. 4:47Resource semaphor waits in SQL Server
4. 8:47Paginated sales report analysis
5. 12:20Memory-heavy operators in plans
6. 16:21Optimizing memory grants for dashboards
7. 27:29Bug in optimized query
8. 29:15Erratic query performance patterns
9. 32:06Query Store for performance issues
10. 38:29Bad parameter sniffing causes
11. 43:06Fixing bad parameter sniffing
12. 47:42Dynamic SQL to avoid sniffing
13. 50:13Balancing optimization and business

" related talks

• 51mHere’s the execution plan … now what?Hugo Kornelis · SQLBits 2022
• 51mHere’s the execution plan … now what?Hugo Kornelis · SQLBits 2019
• 44mImproving Performance with Intelligent Query ProcessingJohn Morehouse · SQL Server Virtual Conference 2021
• 19mStabilizing Performance with Query StoreErin Stellato · SQLBits 2019
• 53mBanishing Slow Queries with Intelligent Query ProcessingJohn Morehouse · SQLBits 2019
• 58m"Run Query Run" - A Fresh Look at SQL Wait StatsPinal Dave · NDC London 2026
• 53mWhen to stop tuning a queryMilos Radivojevic · SQLBits 2022
• 51mAdvanced Plan Explorer Usage for Tuning Execution PlansAndy Yun · SQLBits 2020