Whitepaper : SQL Server Statistics Primer

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.

Topics :

Products :

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

SQL Diagnostic Manager Repository dashboard

24X7 SQL performance monitoring, alerting and diagnostics

  • Monitor performance for physical, virtual, and cloud environments.
  • Monitor queries and query plans to see the causes of blocks and deadlocks.
  • Monitor application transactions with SQL Workload Analysis add-on.
  • View expert recommendations from SQL Doctor to optimize performance.
  • Alert predictively with settings to avoid false alerts.
  • View summary of top issues and alerts with the web console add-on.

Start for Free