http://www.sqlservercentral.com/blogs/briankmcdonald/2010/11/05/how-stale-are-my-statistics_3F00_/

Printed 2014/07/25 11:23PM

How Stale Are My Statistics?

By Brian K. McDonald, 2010/11/05

Unlike fine wine, you typically wouldn’t want your statistics to be aged. At least for tables that are being updated frequently! A while ago, I showed you how to find out what indexes were on a table using the GUI. Then, I showed you how to generate a listing of indexes for a table or for the entire database using a query. Today, I am going extend those queries to include when the statistics were last updated. As in my prior example, the scripts shown below will use the AdventureWorksDW2008R2 and is available on Codeplex.

 

These queries are nearly identical to the ones previously written about, but now I am using the STATS_DATE system function. I am not going to rehash what MSDN states about the STATS_DATE function, but just know that it “returns the date of the most recent update for statistics on a table or index view”. The query optimizer will attempt to use the best query that it can to return the quickest results. If the indexes are stale (have not been refreshed lately), then it may not be using the best index and/or be less efficient. As such, it is good to know how stale the statistics are when performance tuning.

 

Script 1 below generates a list of tables, their index names, type and the statistics date for every table within the specified database (AdventureWorksDW2008R2 in my case).

 

Script 1: List All Index Statistics in Current Database

USE AdventureWorksDW2008R2

GO

 

SELECT

            o.name AS TableName

            , i.name AS IndexName

            , i.type_desc AS IndexType

            , STATS_DATE(i.[object_id], i.index_id) AS StatisticsDate

FROM

            sys.indexes i

            JOIN sys.objects o ON i.[object_id] = o.[object_id]

WHERE

            o.type = 'U'     --Only get indexes for User Created Tables

            AND i.name IS NOT NULL

ORDER BY

            o.name, i.type 

 

Figure 1: Sample Results – ALL Tables in Database

Index Statistics for All Tables  

 

 

 

If you only want to look at how stale the statistics are for a particular table, then you can just apply a filter to the name in the sys.objects table like shown in listing 2.

 

Script 2: List Index Statistics for Specific Table

USE AdventureWorksDW2008R2

GO

 

SELECT

            o.name AS TableName

            , i.name AS IndexName

            , i.type_desc AS IndexType

            , STATS_DATE(i.[object_id], i.index_id) AS StatisticsDate

FROM

            sys.indexes i

            JOIN sys.objects o ON i.[object_id] = o.[object_id]

WHERE

            o.type = 'U'     --Only get indexes for User Created Tables

            AND i.name IS NOT NULL

            AND o.name = 'DimProduct'

ORDER BY

            o.name, i.[type] 

 

Figure 2: Sample Results – Filtered Table Name

Index Statistics For Specified Table 

 

So, basically, if your query isn’t performing as well as you’d like, change the above query to use the IN clause and supply it all of the tables you are including in your query. J This may help you determine what is causing the lag time. I hope that you have enjoyed this quick blog. If you did, please rate it! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs.

 

 

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works

Email: bmcdonald@pragmaticworks.com | Blogs: SSRSGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald | LinkedIn: http://tinyurl.com/BrianKMcDonald

 

 

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.