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
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
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
