You can optimize tables by periodically updating the statistics of indexes on the tables. To update statistics, use the UPDATE STATISTICS statement.
The UPDATE STATISTICS statement creates or updates information about the distribution of the key values in the indexes. This helps SQL Server determine which indexes to use when processing a query. You should use this statement when a large amount of data in an indexed column has been added, changed, or deleted.
Distribution pages are a sampling of data values that SQL Server uses to decide if the index is useful. Distribution pages provide a ratio of the number of rows for which the selection clause will qualify to the number of rows in a table. They are used to determine whether it is more efficient to use the index or to scan the table. A distribution page occupies one full data page.
Distribution pages are created when you run UPDATE STATISTICS on data containing one or more indexes, or when you create an index on already existing data. The UPDATE STATISTICS statement adds one distribution page per index on which statistics have been created. If you use UPDATE STATISTICS for an entire table, add one page for each index on the table. If you UPDATE STATISTICS for only one index, add one page.
Note You can see all of the statistical information in the distribution page for an index by using the DBCC SHOW_STATISTICS statement. The results returned indicate the selectivity of an index (the lower the density returned, the higher the selectivity) and provide the basis for determining whether or not an index would be useful to the optimizer. For details, see the DBCC statement in the Microsoft SQL Server Transact-SQL Reference.
You can display the date that the distribution page was last updated with current statistics by using the system function STATS_DATE. For details, see the System Functions topic in the Microsoft SQL Server Transact-SQL Reference.
Before the selectivity of a clause can be determined, the distribution page must contain valid data. It is necessary to establish statistics for the tables used by the optimizer. This requires that you issue an UPDATE STATISTICS statement periodically ¾ especially after extensive changes to a table. This is because a distribution page contains static information and the UPDATE STATISTICS statement is required to update the information in the distribution page based on the current data in a table. As an example, to update the distribution pages for the index called aunmind on the authors table of the pubs database, type:
UPDATE STATISTICS authors aunmind
Permission to execute the UPDATE STATISTICS statement defaults to the table owner and cannot be transferred. For details, see the UPDATE STATISTICS statement in the Microsoft SQL Server Transact-SQL Reference.