SatishKartan

Blog Post

ARE YOU REALLY MAINTAINING YOUR STATISTICS?

  • By Satish Kartan
  • 12 Aug, 2017
  1. If you are rebuilding all the indexes regardless of the fragmentation levels (using SQL maintenance plan for example), there is no need to update index statistics, but column statistics should be updated. Executing sp_updatestats after the index re-build process will help accomplish this (sp_updatestats will only update if necessary)
  2. If you are using a smart re-index script to defrag/rebuild the indexes – you could encounter two different scenarios – some of the indexes are defragged and some of them are rebuilt only (based on rebuild threshold percentage). Executing sp_updatestats after the index defrag/rebuilt process will update all necessary column and index statistics (Note that index defrag process doesn’t update statistics and index rebuild process does not update column statistics)
  3. Some of the smart re-index scripts provide an option to update statistics, but they often explicitly update all the statistics (regardless of the column changes) with a default sampling ratio (both index and column statistics) – this is not something we want because an index rebuild updates statistics with fullscan. If you subsequently update the statistics with a default sampling rate, it would be negating any benefits of full scan statistic update.

To read more, please visit Satish Kartan's blog where he has shared important information!


Share by: