Find Last Update Statistics Date In SQL Server

I was working on a query to improve the performance and I happened to find the Update Statistics on the table outdated. Update Statistics play an important role in query performance. If you are a DBA or a database developer, the update statistics is one common term you keep listening to every day. Additionally, one fantastic feature we have in SQL Server is the jobs. We can always have the Update Statistics updated by scheduling jobs.

Today, we will be checking the last updated statistics date using the system view - sys.indexes. Before checking the last updated statistics date, let’s check the indexes we have on the desired table.

I have created a dummy table as “Hospital”. I will be checking the indexes on that table with the below command.

sp_helpindex Hospital

Once you have the list of indexes from the sp_helpindex command for your information, let’s check the last updates statistics date with the below query.

SELECT name AS Index_Name,
STATS_DATE(OBJECT_ID, index_id) AS Last_UpdateStats
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('your table name')

NOTE: If you have created a new table and notice NULL values in the date field, then the SQL Server never created a statistic blob on your table. The statistic blobs are not created for new tables, empty tables, or filtered statistics. Due to these reasons, the results can go NULL for you and the predicate does not return any rows as results. 

If any of your statistics do not match the indexes of your table, then they appear in the sys.stats instead sys.indexes.

SELECT Stats_ID, [name] AS Stats_Name,
STATS_DATE(object_id, stats_id) AS [Stats_Date] 
FROM sys.stats s 
WHERE s.object_id = OBJECT_ID('your table name');  

• Tweet
• Share
• Share
• Share
• Share