SQL Server maintains statistics on every index and column in a table. The statistics are used by SQL Server to optimize the performance of your SQL statements. The database can be configured to automatically maintain statistics, or you can manually maintain the statistics.
For instance, if you are retrieving data from a customer table where the ZipCode = 80013, having an index or group statistics can help SQL Server find out how many rows will be returned from the query.
This helps SQL Server decide on indexes to use and in which order to join tables. In this lesson, we will cover six commands that will allow you to make use of database statistics.
The create statistics command will allow SQL Server to analyze columns in a table by gathering histogram and density information. This information is used to estimate how many rows will have a given value for the column. The syntax of create statistics is as follows:
Histogram: A range of values within an index.
Density: The number of rows in which a column has a given value.
It is a good idea to consider generating statistics on columns that are not in an index AND are used in a WHERE clause or in a JOIN clause. This is because this can help SQL Server make better choices when optimizing queries.
The drop statistics command is used to remove a statistics group. The syntax is:
DROP STATISTICS table.statistics_name [,...n]
The update statistics command can be used to update named statistics groups and index statistics. You should update the statistics whenever the data distribution changes by 10% or mor
e. This will make sure that SQL Server is still making the correct choices for indexes and join orders. The syntax is as follows:
To update the statistics on all tables in a database you run the sp_updatestats command. This will run update statistics for every table in a database. The syntax is:
The procedure sp_autostats can be used to allow SQL Server to automatically update statistics or stop SQL Server from automatically updating statistics. It can also be used to find out if the statistics group and indexes are automatically updating statistics.
The syntax is:
If you do not pass in the stats_flag, sp_autostats will return the statistics flag for every index and statistics group.
While automatically updating statistics can improve performance, it can slow down performance for inserts and updates.
The procedure sp_createstats will automatically create statistics for all user tables in the database. It will create statistics for every column except text, ntext, or image column. The syntax is: