See Also: Main_Page - Database Administration - Database Design - Indexing

Statistics track the distribution of values within an index or within a particular column. If a column is not indexed but can benefit from an index, SQL Server will automatically create statistics for that column. The statistics object will show up if you query sysindexes system table with SQL Server 2000 or sys.indexes view with SQL Server 2005. Statistics created automatically by SQL Server are prefixed with '_WA' string. Note that you can turn off automatic creation of statistics at individual database level by executing sp_dboption system procedure. You can also create a statistics object on a table or view column by executing CREATE STATISTICS command.

The query optimizer uses the statistics information to compare the cost of scanning or seeking through a particular index as opposed to exploiting another index or scanning the entire table. Statistics information is stored in statblob column of sysindexes system table. You can retrieve index statistics in a more readable format by using DBCC SHOW_STATISTICS command. Note that the output of this command is slightly different with SQL Server 2005 than it was in previous versions of the software. The output now includes the name of the index on which statistics were calculated as well as an indication whether the current index contains string summary (discussed next).

With SQL Server 2005, a statistics object can contain string summary information which helps optimizer decide whether particular index would be beneficial for queries that search data based on a string pattern. String summary information can be derived for columns with string data types: CHAR, VARCHAR, NCHAR, NVARCHAR, VARCHAR(MAX), NVARCHAR(MAX), TEXT and NTEXT.

By default SQL Server updates statistics automatically as data modification language statements are executed. In most cases you should leave automatic update of statistics on. By default this option is on for every index in all databases. However, if you anticipate large data loads or bulk updates you might wish to turn off automatic update of statistics for the duration of such data changes. Updating statistics is necessary to ensure that optimizer makes accurate decisions as to whether using particular index is cost-effective as opposed to scanning a table or using another index. If statistics get out of date the optimizer could make poor choices and the query performance will suffer. You can configure automatic updating of statistics per index with the following statement:

sp_autostats 'table_name', 'off', 'index_name'

If you disable automatic updating of statistics you must remember to update statistics manually using UPDATE STATISTICS command or sp_updatestats system procedure. The former affects a single table or view, whereas the latter updates statistics for all objects within the current database.

More SQLServerPedia Articles on Indexes

How SQL Server Indexes Work

• Index Overview - the basics of how indexes work.
• Data Pages & Extents - about how indexes and data are stored on disk.
• Table and Record Structure - more about the mechanics of data storage.
• Effects of Data Modifications - the more indexes you have, the longer inserts/updates/delete take.
• Index Statistics - the basics of how statistics affect query execution.

Types of Indexes in SQL Server

• Included Columns - starting with SQL Server 2005, you can add fields to indexes with a smaller performance hit.
• Indexed Views - how to build indexes on top of views for more speed.
• XML Indexes Overview - how to index XML documents and fragments.
• XML Index Rules - some additional considerations and requirements for XML indexes.

Best Practices on How to Design Database Indexes

• Indexing Strategies - choosing between clustered/nonclustered, long/short, selectivity, and more.
• Best Practices for Creating Indexes - tips to start with when designing a new index strategy.
• Choosing Index Keys - how to pick which fields to use for a primary key.
• Index Selectivity and Column Order - how to pick which columns to put first, and how it affects different kinds of queries.
• Wide Index Performance - does adding more fields to an index cause it to run slower?
• Tuning Indexes - including articles on the Index Tuning Wizard and Database Tuning Advisor.

Maintaining Indexes for Top Performance

Indexes need regular maintenance in order to perform well.

• Index Maintenance Tutorial - video and script explaining how to defragment and rebuild your indexes automatically.
• Index Maintenance in SQL Server 2005 and Beyond - SQL 2005 introduced new syntax to ALTER INDEX.
• Index Maintenance Overview - analyzing fragmentation and statistics to get the best performance possible out of your indexes.