How to find Outdated Index Statistics in SQL

Question

Hi Team,

How to find Outdated Index Statistics in SQL server

Answer 1

Hi balanarasimhac-1495,

We can use sys.dm_db_stats_properties and sys.stats to find the outdated statistics. Please refer to this query which might help:

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  

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Just finding them or also update them?
Then use https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Answer 3

That's non-trivial. Because when is a statistics "outdated"? We probably mean that it is outdated when the optimizer produces a bad plan. But that depends a lot on the query. So there may be tables where statistics have not been updated for ages, but we will get good plans. And there are tables where you may need to update statistics more than once a day.

An experienced database professional recognises an outdated statistics when he or she encounters them when battling a query. But in the general case?

As Olaf says, best is to run index maintenance in advance to prevent it from happening.