Query Optimization Statistics - Driving Force Behind Performance

Explore the critical role of statistics in SQL Server query optimization in this comprehensive conference talk from PASS Data Community Summit. Delve into the fundamentals of SQL Server statistics objects, their importance in optimizer decision-making, and best practices for maintenance. Discover common issues arising from imperfect statistics, including ascending keys, skewed distribution, and inaccurate summary information. Learn techniques to identify and resolve these problems, including the use of a provided stored procedure for detecting ascending keys. Gain insights into improving query performance through practical examples and expert guidance on topics such as auto-update statistics, persistent sample percent, and handling problematic column types like identity and modified date columns.

Intro
Agenda
What is the Optimizer
Complexity of the Optimizer
What are Statistics
Spstats
Show Statistics
Read Statistics
Maintain Statistics
Getting rid of Statistics
Auto Update Statistics
Persistent Sample Percent
Bad Samples
Default Sample Size
Step Column
Identity Column
Modified Date Column