Wednesday, September 10, 2014

Performance tips - How to determine last statistics update in table ? TIP #46

Hi,

For maintenance purpose we need to determine when the stats last update and analyze the data and if data is out dated then we need to update the stats.

To determine this we need to run the following scripts

SELECT OBJECT_NAME(s.object_id) AS [ObjectName]
      ,s.name AS [StatisticName]
      ,STATS_DATE(s.object_id, [stats_id]) AS [StatisticUpdateDate]
FROM sys.stats s
INNER JOIN sys.objects obj ON obj.object_id = s.object_id
AND obj.type IN ('U','V')
ORDER BY STATS_DATE(s.object_id, [stats_id]) Asc,obj.name

 

For example I run the following code in adventurework2012

Last_Updated_Stats

 

So according to update status date we do update statistics of those tables in database.

I hope this tip will help you.

Enjoy !!!

RJ