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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s