When to stop tuning a query
summary
A single T‑SQL query against two tables — 100 million rows in orders and 335 million rows in order_details — starts with a hash join, ~11s and 5 million logical reads. Forcing a nested loop via hint cuts reads to a few thousand and time to sub‑second but shows a 10 GB memory grant that blocks under 200 concurrent sessions run with SQL Query Stress. Limiting the query memory to ~0.05% drops it to ~9 MB; using a table variable to skew cardinality and finally updating statistics with a higher, persisted sample percent fixes the root cause.
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
51mDealing with Very Large Tables in SQL Server
55mLet's Talk About MySQL Query Tuning
19mStabilizing Performance with Query Store- 51mDealing with Very Large Tables in SQL Server
01h 00m7 Tips to Performance Tuning, Optimization, and Everything