Wednesday, April 15, 2015

Display Stale Index Statistics

This query displays all the index statistics in the current database that are more than 'n' days old.

-- When were Statistics last updated on all indexes more than 7 days ago?  
  select o.name                                as 'Table Name'
       , i.name                                as 'Index Name'
       , stats_date(i.object_id, i.index_id)   as 'Statistics Date'
       , s.auto_created
       , s.no_recompute
       , s.user_created
    from sys.objects   o
    join sys.indexes   i
      on o.object_id = i.object_id
    join sys.stats     s
      on i.object_id = s.object_id
     and i.index_id    = s.stats_id
   where o.type = 'U'
     and datediff(Day, stats_date(i.object_id, i.index_id), getdate()) > 7
order by [Statistics Date] desc

0 comments:

Post a Comment