Statistical information

SQL Server uses statistical information about the distribution of values in a column to determine the optimal strategy for evaluating a query. Distribution statistics help the system estimate how efficient an index would be in retrieving data associated with a key value or range specified in the query.

Note: This topic applies only to on-premises environments.

As the data in a column changes, index and column statistics can become out-of-date, affecting query performance. The statistics should be refreshed anytime significant numbers of changes to keys occur in the index.

We recommend that you update statistics nightly or weekly for best performance.

See Updating statistics.

You can use the dbcc show_statistics statement to generate a report on the distribution statistics for an index. The statements in this section use this syntax:

dbcc show_statistics (table_name, index_name)

In SQL Server Management Studio, with the application database selected as the current database, these statements show the current statistics and the last time statistics were updated for primary keys in major tables:


dbcc show_statistics (item, pk_item)
dbcc show_statistics (customer, pk_customer)
dbcc show_statistics (ledger, pk_ledger)
dbcc show_statistics (matltran, pk_matltran)
dbcc show_statistics (matltran_amt, pk_matltran_amt)
dbcc show_statistics (journal, pk_journal)
dbcc show_statistics (ledger_all, pk_ledger_all)

The results indicate the selectivity of an index (the lower the density returned, the higher the selectivity) and provide the basis for determining whether an index is useful in optimizing queries.

See SQL Server help for dbcc show_statistics and other DBCC (Database Console Commands) statements.