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.
Create statistics Command
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.
A unique name identifying the statistic group
The table for which you want statistics.
The columns in the table on which to create statistics.
Tells the create statistics command to scan the whole table when creating the statistics.
Allows you to specify how much of the table to scan.
Stops SQL Server from automatically updating statistics on this statistics group
Create Statistics - Syntax
To create statistics on the ZipCode column in the Customer table, you could use the following command:
create statistics CustStat1 on Customer (ZipCode)
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.
Drop statistics
The drop statistics command is used to remove a statistics group. The syntax is:
DROP STATISTICS table.statistics_name [,...n]
Update statistics
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:
The table for which you are updating statistics.
A specific index on the table for which you are updating statistics.
One or more statistic groups.
Tells the create statistics command to scan the whole table when creating the statistics.
Allows you to specify how much of the table to scan.
Tells SQL Server to update statistics on both index and statistic groups.
Tells SQL Server to only update statistics on statistic groups.
Tells SQL Server to only update statistics on indexes.
Stops SQL Server from automatically updating statistics on this statistics group.
Update Statistics Syntax
When you do not specify an index or a statistics group, all statistics are updated.
sp_updatestats
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:
sp_updatestats
sp_autostats
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.
sp_createstats
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: