The Most Important Performance Factor in SQL Server and Azure SQL
summary
Central claim: query performance hinges on accurate statistics, not indexes alone. Demo shows missing stats cause SQL Server to use a square‑root default distinct count and a tiny sample (0.7%), producing wrong row estimates and plans that turn a 31k logical‑read query into ~800k or force 5 million key lookups. Key facts: histograms cap at 200 histogram steps, auto‑update threshold changes from 20% (pre‑2016) to sqrt(rows1000) (~200k for 41M rows), unknown values trigger a 30% inequality guess*, indexes create full‑scan stats, and SQL Server 2014+ rewrites the cardinality estimator.
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
53mSQL Server Internals: The Practical Angle
57mTake the SQL Server Index Quiz!
50mSQL Server to Azure SQL: Performance and Availability
07mIndeksimi dhe përmirësimi i performancës në SQL
49mSQL Server Query Execution: Expectation vs Reality