Maintain Database Index Statistics

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:
  1. Histogram: A range of values within an index.
  2. Density: The number of rows in which a column has a given value.

  

• Auto create statistics:
  Data-distribution statistics are a key factor in how the SQL Server Query Optimizer creates query execution plans. This option directs SQL Server to automatically create statistics for any columns for which statistics could be useful. The default for this option is set to ON. To set auto create statistics ON for database_sample
  

  ALTER DATABASE database_sample 
  SET AUTO_CREATE_STATISTICS ON;
  

  
  
  1. Statisitics_name – A unique name identifying the statistic group.
  2. Table – The table for which you want statistics.
  3. Column [, …n] – The columns in the table on which to create statistics.
  4. FULLSCAN – Tells the create statistics command to scan the whole table when creating the statistics.
  5. Sample number PERCENT – Allows you to specify how much of the table to scan.
  6. Norecompute – Stops SQL Server from automatically updating statistics on this statistics group.

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]
  

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 more. This will make sure that SQL Server is still making the correct choices for indexes and join orders. The syntax is as follows:

1. Table – The table for which you are updating statistics.
2. Index – A specific index on the table for which you are updating statistics.
3. Statstics_name [,…n] – One or more statistic groups.
4. FULLSCAN – Tells the create statistics command to scan the whole table when creating the statistics.
5. Sample number PERCENT – Allows you to specify how much of the table to scan.
6. ALL – Tells SQL Server to update statistics on both index and statistic groups.
7. COLUMNS – Tells SQL Server to only update statistics on statistic groups.
8. INDEX – Tells SQL Server to only update statistics on indexes.
9. Norecompute – Stops SQL Server from automatically updating statistics on this statistics group.

When you do not specify an index or a statistics group, all statistics are updated.

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:
  

  sp_autostats [@tblname =] 'table_name' 
  [, [@flagc =] 'ON|OFF'] 
  [, [@indname =] 'index or statistics group']
  

  
  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:

sp_createstats [[@indexonly =] 'indexonly']
[,[@fullscan =] 'fullscan']
[,[@norecompute =] 'norecompute']

1. Indexonly – Specifies to only create statistics for columns that are in a index
2. Fullscan – Specifies to scan the whole table
3. Norecompute –Specifies that statistics should not be automatically generated

The next lesson will show you how to view statistics information.