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.
The 12-page whitepaper “SQL Server Statistics Primer” 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.
The author, 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.
Click here to read the whitepaper.
Monitor the performance of SQL Server for physical, virtual, and cloud environments with SQL Diagnostic Manager with proactive alerting, prescriptive analysis, and comprehensive reporting.
View the infographic “Why Use SQL Diagnostic Manager”, read a case study, browse the datasheet, download a fully functioning 14-day trial, request a one-on-one demonstration, and request a price quotation.