Identify when Statistics were last Updated in SQL Server Database

StatsUpdate

If you want to identify when last time statistics were updated on objects in database then you can use either DBCC SHOW_STATISTICS header information or sys.stats catalog view.

Below example displays all statistics information for the AK_Currency_Name index of the Sales.Currency table in AdventureWorks2012 database.

USE AdventureWorks2012;
GO
DBCC SHOW_STATISTICS ('Sales.Currency', AK_Currency_Name) WITH STAT_HEADER
GO

As you can see above, Updated column provides you last updated date and time for statistics on Sales.Currency table.

Below query is very simple and important which provides you statistics last update details on all objects in a database. So this is our main query and important for DBAs.

USE AdventureWorks2012;
GO

SELECT OBJECT_NAME(object_id) AS [Objects], 
MAX(STATS_DATE([object_id], [stats_id])) AS [StatisticsUpdatedOn] 
FROM sys.stats GROUP BY OBJECT_NAME(object_id) 
HAVING OBJECT_NAME(object_id) NOT LIKE 'sys%' 
ORDER BY [StatisticsUpdatedOn] DESC

statisticsUpdatedOn

So, once you identify required information then you can decide to update required objects or all objects in a database.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

You may also like...

Leave a Reply

Your email address will not be published.