Sunday, March 2, 2014

Question on sql statistics

There is no need to delete statistics to recreate them with fullscan, just run UPDATE STATISTICS WITH FULLSCAN.


Having statistics updated with fullscan will not incur any impact on INSERT, UPDATE or DELETE statements, once the the statistics have been created as statistics are not maintained with each and every statement.


However, running UPDATE STATISTICS WITH FULLSCAN on a big table will incur load on the system, as SQL Server scans the table once for each non-indexes column there is statistics.


And that is the reason, SQL Server defaults to sampled statistics. to reduce the impact of statistics creation. It is not likely that FULLSCAN is always needed, but there are certainly situations where the sampled statistics are insufficient. A compromise is to run WITH FULLSCAN, INDEX. This restricts the operation to indexed column. These statistics are likely to be more important, and they are also faster to update, because here SQL Server only have to scan the index which is smaller and already sorted.


From this follows that you should run UPDATE STATISTICS outside business hours, and if you don't have all the hours to run fullscan on your 3000 tables you should not do it. But if you identify an occasional table where this gives effect, do it with that one.


Important to know is that when you create or rebuild in an index, this updates the statistics for that index with fullscan. (Since SQL Server is reading all data anyway.)





Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

No comments:

Post a Comment