Monday, 8 July 2013

SQL Server: Part 5 : All About SQL Server Statistics : How to Detect outdated Statistics ?

In the last post, we have discussed about the auto update statistics threshold and concluded that auto updated statistics threshold are good enough to get optimal performance in some workload. In many cases,a manual update of statistics will help to obtain better performance. In this post let us discuss, how to detect the outdated statistics.

In SQL server 2005 onwards, SQL server uses the ColModCtr to keep track of the changes in the leading column of the statistics. Unfortunatly that is not exposed through any DMV or system view in SQL server 2005 or SQL server 2008. In SQL server 2008 R2 (SP2) onwards, sys.dm_db_stats_properties will give the details of statistics along with the changes in the leading column of the statistics.

For older versions of SQL server users, we need to depends on rowmodctr available in the sys.sysindexes. As per BOL : In SQL Server 2005 and later, rowmodctr is not fully compatible with earlier versions.In earlier versions of SQL Server, the Database Engine maintained row-level modification counters. Such counters are now maintained at the column level. Therefore, the rowmodctr column is calculated and produces results that are similar to the results in earlier versions, but are not exact.

The below query will give an estimate of changes in the statistics.

    ,statisticsUpdateDate = STATS_DATE(i.OBJECT_ID, i.index_id)
FROM sys.indexes i 
JOIN sys.objects o ON    i.OBJECT_ID=o.OBJECT_ID
JOIN sys.sysindexes si ON
    AND i.index_id=si.indid 
    ,statisticsUpdateDate= STATS_DATE(o.OBJECT_ID, s.stats_id)
FROM sys.stats s INNER JOIN sys.objects o ON s.OBJECT_ID=o.OBJECT_ID
JOIN sys.sysindexes si ON AND s.stats_id= si.indid
INNER JOIN (SELECT id,rowcnt FROM sys.sysindexes WHERE indid IN (0,1)) IR
ON  WHERE  o.TYPE <> 'S'  
AND (s.auto_created=1 OR s.user_created=1)AND STATS_DATE(o.OBJECT_ID, s.stats_id) IS NOT NULL

By keeping this as a base, based on the workload/types of query running, we can schedule the statistics update at appropriate interval instead of blindly updating all statistics.

If you liked this post, do like my page on FaceBook


  1. Thanks for posting this useful content, Good to know about new things here, Let me share this, . LINUX training in pune


  2. I loved the way you discuss the topic great work thanks for the share, Let me share this, vmware training in pune

  3. interesting read all the parts one go thanks for writing such an awesome topics oracle fusion procurement training