Lesson Learned #95: Updating the auto-created and user defined statistics only

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Today, I worked on a service request that our customer has implemented a process to rebuild the indexes and after it they update the statistics. As this process is taking too much time, they want to reduce the time, and, I found an important thing about how the statistics are updated.

Once thing that I saw, was that they are using a script that rebuild the indexes and after it they update all the statistics, but, without filtering if this statistic has been updated previously due to the rebuild process (we need to know that, every time that you rebuild an index, the statistic that is associated to this index will be update too).

So, I suggested to use this script, leaving to our customer decides what is the better value for modification_counter, etc.. where we update the statistics that have been created automatically by SQL Server engine or by the user. Remember that when you update the statistics SQL Server needs to sort the data by the field(s) that is/are in the definition of the statistics using time and resources.

SELECT  sp.rows, sp.modification_counter,
  o.name as objname, s.name,
  CASE WHEN (s.auto_created=1) THEN 'AUTOSTATS'
       WHEN (s.user_created=1) THEN 'STATS'
  END as type,
  sp.last_updated,
  s.no_recompute, 
  o.object_id as objid, 
  persisted_sample_percent,
  CASE WHEN (sp.persisted_sample_percent=0 OR sp.persisted_sample_percent IS NULL)
          THEN 'UPDATE STATISTICS [' + OBJECT_SCHEMA_NAME(O.object_id) + '].[' + o.name + '] ('+ s.name + ') WITH FULLSCAN' 
       WHEN (sp.persisted_sample_percent>0)
         THEN 'UPDATE STATISTICS [' + OBJECT_SCHEMA_NAME(O.object_id) + '].[' + o.name + '] ('+ s.name + ') WITH SAMPLE ' + CONVERT(varchar(200),sp.persisted_sample_percent) + ' PERCENT'
  END as TSQLCmd
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp  
join  sys.objects o  WITH (nolock) on s.object_id=o.object_id 
WHERE o.type = 'U' and (s.auto_created=1 or s.user_created=1)
AND sp.modification_counter >= 1 or s.no_recompute=1
ORDER BY objid

I used these two URLs about dm_db_stats_properties and update statistics for reference.

Enjoy!