skip to content »

Updating statistics sql 2016 r2

Question: Recently I noticed an option with CREATE/ALTER INDEX called STATISTICS_NORECOMPUTE?I'm not sure I understand this option or why you'd ever want to use it? Answer: In general, I don't recommend this option.

updating statistics sql 2016 r2-69updating statistics sql 2016 r2-6updating statistics sql 2016 r2-50updating statistics sql 2016 r2-76

I've heard a few folks say that it stops SQL Server from updating the statistics at the time of a rebuild; that is NOT what it does.The more difficult question is if/when this would make sense to turn off – for either an index or a column-level statistic.And, yes, there are some cases where this can be appropriate.Related: Making the Most of Automatic Statistics Updating The case where it makes sense to be turned off is for tables that are large in size and have very skewed data distribution.For example, the ratio of might be very evenly distributed between 1 and 3 items with an average of 2.1.Statistics are ALWAYS updated at the time of a REBUILD; this cannot be turned off (nor would you want to).

Instead, STATISTICS_NORECOMPUTE stops the database-wide auto-updating (auto update statistics) from updating the specific statistics for an index (or column-level statistic) that was created (or rebuilt) with this option turned on.

NOTE: STATISTICS_RECOMPUTE only works when creating or rebuilding an index.

If you want to stop the auto-updating of a column-level statistic you use NORECOMPUTE when creating that particular statistic.

Evenly distributed data tends to be easier to represent in a histogram (one of the pieces of information maintained in a statistic in SQL Server).

However, might be much more uneven (and therefore skewed).

And, when data distribution is skewed then then statistics are potentially less accurate in a histogram.