SQL Server Portal

“How to find outdated Statistics ?” is an old topic being discussed on different forums but what encourages me to write this article is the way we usually find outdated statistics using sys.sysindexes will be obsolete soon.
As per MSDN sys.sysindexes will be removed in the future version of Microsoft SQL Server. Avoid using this feature in new development work.

Given below are both approaches.

Old Approach using sys.sysindexes :
Given below is an old approach using sys.sysindexes to find the outdated statistics of tables.
In the given below script, I filtered all the statistics those are updated the previous day or the day before and their actual data is being modified.

--This script is compatible with SQL Server 2005 and above.
SELECT
id			        AS [Table ID]
, OBJECT_NAME(id)		AS [Table Name]
, name			        AS [Index Name]
, STATS_DATE(id, indid)	AS [LastUpdated]
, rowmodctr				AS [Rows Modified]
FROM sys.sysindexes
WHERE STATS_DATE(id, indid)<=DATEADD(DAY,-1,GETDATE())
AND rowmodctr>0 AND (OBJECTPROPERTY(id,'IsUserTable'))=1
GO
--OUTPUT

New Approach Using sys.dm_db_stats_properties :
Given below is the new approach using sys.dm_db_stats_properties and sys.stats to find the outdated statistics of tables.
In the given below script, I filtered all the statistics those are updated the previous day or the day before  and their actual data is being modified.

--This script is compatible with SQL Server 2008 R2 and above.
USE AdventureWorks2012
GO
SELECT
st.object_id				            AS [Table ID]
, OBJECT_NAME(st.object_id)	            AS [Table Name]
, st.name		                        AS [Index Name]
, STATS_DATE(st.object_id, st.stats_id) AS [LastUpdated]
, modification_counter			        AS [Rows Modified]
FROM
sys.stats st
CROSS APPLY
sys.dm_db_stats_properties(st.object_id, st.stats_id) AS sp
WHERE
STATS_DATE(st.object_id, st.stats_id)<=DATEADD(DAY,-1,GETDATE())
AND modification_counter > 0
AND OBJECTPROPERTY(st.object_id,'IsUserTable')=1
GO
--OUTPUT

Conclusion :
As you can see that both approaches give the same result but Old approach will be removed from the future version of SQL Server. So, it is recommended to use the new approach.

Reference: MSDN

Read Full Post »