Here’s the execution plan … now what?
summary
Diagnoses a dashboard causing resource semaphore waits by reading actual execution plans and estimated operator input, finds a 120,000-row sort feeding a temp table that grants 47 MB to 14 MB of memory after replacing the insert+sort with a top-end sort for page 1 (999 of 1000 calls are page one), accepts a small correctness risk to remove spills. For erratic runs uses Query Store to prove parameter sniffing, then splits logic and uses safe parameterized dynamic SQL or OPTION(RECOMPILE) to get stable plans.
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?
19mStabilizing Performance with Query Store- 19mStabilizing Performance with Query Store
58m"Run Query Run" - A Fresh Look at SQL Wait Stats
44mImproving Performance with Intelligent Query Processing
53mBanishing Slow Queries with Intelligent Query Processing
51mAdvanced Plan Explorer Usage for Tuning Execution Plans