The Most Important Performance Factor in SQL Server and Azure SQL
summary
Claim: table column statistics drive nearly every optimizer decision and bad stats cause wrong plans. Live demos on a 41 million‑row library show SQL Server guessing sqrt(N) when stats are missing (estimates ~6,450 vs actual 7,734), returning the same wrong estimate for a 5M romance query until auto create statistics runs, and cutting logical reads from ~800,000 (forced lookups) to ~31,000 after accurate stats. Uses SET STATISTICS IO, DBCC SHOW_STATISTICS and DMVs, notes histogram limit 200, sample ~0.7%, auto‑update threshold ≈200k rows, and CE changes in 2014 and 2022.
AI-written summary · about
chapters (13)
- 0:00Introduction and setup
- 2:07Importance of statistics
- 5:41Troubleshooting execution plans
- 10:47Auto-create statistics
- 13:39Metadata and histograms
- 18:31Index-based statistics
- 23:45Performance testing tips
- 27:18Local variable issues
- 33:45Column correlation and CE
- 37:38Advanced statistics types
- 41:21Filtered statistics
- 44:50Managing statistics updates
- 50:02Summary and best practices
related talks
53mThe Most Important Performance Factor in SQL Server and Azure SQL
47mUnderstanding Statistics in SQL Server
49mPerformance Features in SQL Server and Azure SQL
26mThe five-step guide to SQL tuning
42mMariaDB and MySQL — What Statistics the Optimizer Uses
07mIndeksimi dhe përmirësimi i performancës në SQL
51mPerformance Tuning Azure SQL Database
53mOptimizing your Azure SQL Database workloads