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.

SELECT 
     TableName=OBJECT_NAME(i.OBJECT_ID)
    ,ObjectType=o.type_desc
    ,StatisticsName=i.[name]
    ,statisticsUpdateDate = STATS_DATE(i.OBJECT_ID, i.index_id)
    ,RecordModified=si.rowmodctr
    ,NumberofRecords=si.rowcnt
FROM sys.indexes i 
JOIN sys.objects o ON    i.OBJECT_ID=o.OBJECT_ID
JOIN sys.sysindexes si ON    i.OBJECT_ID=si.id
    AND i.index_id=si.indid 
WHERE  o.TYPE <> 'S' AND STATS_DATE(i.OBJECT_ID, i.index_id) IS NOT NULL
UNION ALL
SELECT 
     TableName=OBJECT_NAME(o.OBJECT_ID)
    ,ObjectType=o.type_desc
    ,StatisticsName=s.name
    ,statisticsUpdateDate= STATS_DATE(o.OBJECT_ID, s.stats_id)
    ,RecordModified=si.rowmodctr
    ,NumberofRecords=ir.rowcnt
FROM sys.stats s INNER JOIN sys.objects o ON s.OBJECT_ID=o.OBJECT_ID
JOIN sys.sysindexes si ON s.OBJECT_ID=si.id AND s.stats_id= si.indid
INNER JOIN (SELECT id,rowcnt FROM sys.sysindexes WHERE indid IN (0,1)) IR
ON IR.id=o.OBJECT_ID  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