When to stop tuning a query
summary
Single T‑SQL query against Orders (100M) and OrderDetails (335M) returns 248 rows but starts as a hash join with a cluster scan, cold cache costing 5 million logical reads and ~11s; forcing a nested loop and using SET STATISTICS IO/TIME drops CPU and reads but reveals a 10 GB memory grant that blocks concurrency when simulated with SQL Query Stress (200 sessions). Workarounds: limit memory percent, use a table variable trick vs. table variable defer compilation, or fix root cause by updating statistics with a higher persisted sample percent (costly on very large tables).
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
01h 00m7 Tips to Performance Tuning, Optimization, and Everything
51mDealing with Very Large Tables in SQL Server
19mStabilizing Performance with Query Store- 19mStabilizing Performance with Query Store
43mPerformance Tuning, Getting the Biggest Bang for Your Buck