When to stop tuning a query
summary
A single T‑SQL query against 100M/335M rows is tuned stepwise: forcing a nested loop join cuts elapsed time from ~11s to ~2s but exposes a 10 GB memory grant that cripples concurrency when simulated with 200 sessions using SQLQueryStress. Trickery with a table variable (and later table variable defer compilation or option hints) changes cardinality estimates to avoid bad joins. Increasing statistics sample rates to ~20–50% fixes the root cause and restores stable plans.
AI-written summary · about
chapters (29)
- 0:00Query tuning session intro
- 1:09Database and query setup
- 3:14Starting query tuning
- 4:20Why tune SQL Server queries
- 6:07Join operators and workload
- 7:33Initial query metrics
- 8:22Nested loop join hint results
- 9:28When to stop tuning
- 10:31Memory grant and concurrency
- 11:33SQLQueryStress parallel tests
- 13:40Memory pressure decision
- 15:27Limit memory grant
- 16:37Eliminating unnecessary parallelism
- 17:41Final optimized query
- 18:45Override optimizer decisions
- 19:50Trick optimizer with table vars
- 25:16Table var deferred compilation
- 26:19Compatibility level impact
- 27:45Statistics estimation problems
- 34:15Stats for orders table
- 36:26Large table sample rate issues
- 39:15Fix stats with higher sample
- 41:00Eliminate root cause benefits
- 42:26Auto stats updates drawbacks
- 43:27PERSIST_SAMPLE_PERCENT
- 44:35Large tables special care
- 45:59Maintain statistics yourself
- 47:46Query tuning strategies
- 49:12Q&A
related talks
53mWhen to stop tuning a query- 53mWhen to stop tuning a query
26mThe five-step guide to SQL tuning
55mLet's Talk About MySQL Query Tuning
44mImproving Performance with Intelligent Query Processing
01h 00m7 Tips to Performance Tuning, Optimization, and Everything
53mBanishing Slow Queries with Intelligent Query Processing
51mDealing with Very Large Tables in SQL Server