Index Statistics Norecompute

     I’ve been meaning to write about this one for a while but life has gotten in the way.  There seems to be some confusion over the index setting statistics norecompute so I thought I would write about it in hopes of shedding some light on the subject.

     When you specify an alter index statement, one of the arguments you may specify is STATISTICS_NORECOMPUTE ON or OFF.  This setting tells the database engine whether or not it should automatically recompute distribution statistics for the index.  

     When you create an index statistics are automatically created for the associated index.  By default SQL Server will automatically update statistics when 20% + 500 columns of data have been modified.  So on a table with 100,000 rows once 20,500 of the column data has changed statistics will be recomputed or in the case of SQL Server 2005 and 2008 the statistic will be marked for updating.   In 2005 and 2008 SQL Server will flag statistics as out-of-date and will update statistics the next time they are accessed. 

     This is where the STATISTICS_NORECOMPUTE setting for Alter Index comes into play.  Setting STATISTICS_NORECOMPUTE to ON for an index will disable the auto updating of statistics for the index once the threshold for changed data has been reached.  This is the same effect as creating statistics with NORECOMPUTE except you don’t have to drop and recreate the statistic to change the setting.  In order to enable auto updating of index statistics once it has been disabled simply rebuild the index with the NORECOMPUTE OFF parameter.

     The confusion for this setting comes from the fact that people think NORECOMPUTE refers to the updating of statistics during an index rebuild.  The thought is that you can speed up an index rebuild by telling SQL Server not to recomputed statistics during the rebuild.  This is not the case.  SQL Server will update index statistics during a rebuild regardless of the NORECOMPUTE setting for the index.

To validate this I did a test.  I used the sys.stats system view along with the STATS_DATE function to return the statistics information for my table along with the date and time the statistics were created.  The STATS_DATE function accepts the Object_Id and the Stats_Id from sys.stats and returns the date the statistics were last update.  I ran the following statement:

1:  ALTER INDEX PK_INF_GROUP  
2:  ON INF_GROUP  
3:  REBUILD  
4:  WITH( STATISTICS_NORECOMPUTE = ON )  

Following that I ran this query to view the statistics information:

1:  SELECT *  
2:      , StatDate = STATS_DATE(s.object_id, s.stats_id)  
3:  FROM sys.STATS s  
4:  WHERE object_id = object_id('INF_GROUP')  

From the results below I can see that although no_recompute is set for my index the statistics for the index were recomputed.

    

 

     You can also see from these results that the other statistics for my table were not updated during the index rebuild.   By viewing the details of the statistics in SQL Server Management Studio I can further see that the statistics were updated using a full scan and not a sampling of the data.

     There may be some situations where you would want to disable the automatic updating of statistics.  One that comes to mind is if your data is skewed and sampling does not provide an accurate enough picture of the distribution of data to allow SQL Server to choose an optimal execution plan.  However, you cannot disable the updating of index statistics by setting the STATISTICS_NORECOMPUTE parameter to On during an index rebuild.

     Kimberly Tripp has blogged extensively about index statistics and I suggest you check out her blog at www.SQLSkills.com/blogs/Kimberly for more in-depth information on statistics.