Statistics, or “stats,” are fundamental components of SQL Server performance, but vastly under-appreciated and misunderstood. They are at the core of query optimization and can have a tremendous effect on query plan selection. The query optimizer uses statistics to estimate I/O costs and memory grants. Poor statistics, whether they are skewed or incorrect, can cause massive performance problems when they lead to the selection of a problematic plan. Regardless, having good statistics is still no guarantee that the plan is optimal for the query. Statistics are mostly self-maintaining, though they can require a little care and feeding when they cause poor plan selection. However, caution is warranted when deciding to do regular maintenance on statistics. Sometimes doing maintenance on stats when it is not justified can cause more harm than good. The key is to make sure to address the problem and not just the symptom.
This whitepaper is an introductory guide for database administrators about SQL Server statistics. It covers how to use them, how to maintain them, and how they affect performance. The whitepaper describes what statistics are, how SQL Server uses them, how to view the information they provide, how they are used, and how to maintain them. The whitepaper also shares guidelines on when to perform maintenance on statistics, and equally important, when to avoid the temptation to update statistics and leave them alone.
Robert L. Davis
Robert L. Davis was a senior database administrator and technical lead at Microsoft. He was a speaker and a trainer as well as a writer for SQL Server Magazine. Blog: www.sqlsoldier.com. Twitter @sqlsoldier
Try SQL Diagnostic Manager for SQL Server FREE for 14 days
24X7 SQL performance monitoring, alerting and diagnostics