THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: How old are my statistics?

This post is basically to answer a question asked in class this week: How can we get the last statistics update date for ALL user tables in a database?

After working on the query for a while, I realized that the new metadata function I posted about here can give you that info easily:

SELECT object_name(sp.object_id) as object_name,name as stats_name, sp.stats_id, 
    last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE sp.object_id > 100;

But for those of you not yet running SQL Server 2008R2 Service Pack 2 or SQL Server 2012 Service Pack 1 (as long as it’s 2005 or later), I’ll show you the other query I had worked out:

SELECT schema_name(schema_id) AS SchemaName,  object_name(o.object_id) AS ObjectName, AS IndexName, index_id, o.type,
    STATS_DATE(o.object_id, index_id) AS statistics_update_date
FROM sys.indexes i join sys.objects o
       on i.object_id = o.object_id
WHERE o.object_id > 100 AND index_id > 0
  AND is_ms_shipped = 0;

There are a few slight differences in the output between the two queries, in addition to the fact that the query using sys.dm_db_stats_properties() adds the extra columns for number of rows sampled, the column modification counter, etc. The function does does  not return the schema_id or the type of object (e.g. is it a table or an indexed view). Both those pieces of information can be determined by joining with the sys.objects view, but I have left that as an exercise for the reader/student.


I hope you find this useful!


Published Saturday, August 31, 2013 3:56 PM by Kalen Delaney
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Davide Mauri said:

Since it happens quite often that one has to query system metadata, I've created a "SYS2" views that can be used to ease to need to join several tables to get the desired result. A similar query to extract statistics informaion is also available:

September 1, 2013 9:25 AM

Carol Boersema said:

Exactly what I am looking for (and more) - thanks much Kalen.

September 4, 2013 11:36 AM

Gurpreet Sohal said:

Nice Info Kalen ..


September 6, 2013 4:40 AM

Liam Gavin said:

Nice, will use that. This is also good as its quick and shows one row per table:

SELECT name AS index_name,

STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated

FROM sys.indexes

order by StatsUpdated

September 9, 2013 5:39 AM

Pete said:

Copy/pasted the first statement exactly, I get this error:

Msg 208, Level 16, State 1, Line 5

Invalid object name 'sys.dm_db_stats_properties'.

I'm running:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

September 9, 2013 11:23 AM

David Clary said:

I get same error as Pete, using

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2796.0 (X64)  

Dec  9 2011 11:27:20  

Copyright (c) Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.1 <X64>

(Build 7601: Service Pack 1)

September 9, 2013 12:21 PM

Sandra said:

Peter and David:  You need to be running SQL Server 2008R2 Service Pack 2 or SQL Server 2012 Service Pack 1 to run the first query.

September 9, 2013 12:40 PM

Kalen Delaney said:

Thanks, Sandra. Yes, if you follow the link in the above post to where I first talked about this new function, I did say you needed SQL Server 2008R2 Service Pack 2 or SQL Server 2012 Service Pack 1.

So why aren't you guys running with the latest service pack?


September 9, 2013 12:48 PM

Pete said:

I thought this: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

meant Service Pack 2, but now I guess it means Windows SP2, eh? I was running Express for several years, and we just recently bought and installed the full version. I'll have to get onto those service packs.

September 9, 2013 1:23 PM

Kalen Delaney said:

Yes, the Service Pack 2 is for Windows... "Microsoft SQL Server 2008 R2 (RTM)" means RTM for SQL Server 2008R2  ... no service packs at all!

September 9, 2013 1:29 PM

Pete said:

Yep, I just found a website listing all that in a nice, tidy table:

Took me right to the proper MS site, and I'm downloading SP2 right now.

September 9, 2013 1:34 PM

Steven said:


If you are manually updating stats, never using auto update.  Is there a limit to how old stats can be and still be used by the optimizer? Do stats have an expiration date?

i.e. If we had a table that's seldom or never updated, and the stats aren't updated for say, a year, are they still valid (provided the conditions of no updates is true)?

October 11, 2013 5:38 PM

Kalen Delaney said:

Hi Steven

No, the optimizer itself doesn't check the age of the statistics. If they're there, it uses them.


October 11, 2013 6:12 PM

Steven said:

Thanks Kalen!  I really appreciate your quick response. It will help us  in our decisions of what to do with this old, yet critical application database.   I've been a huge fan of your books and webinars you've written/presented throughout the years.  You're the best! :)


October 12, 2013 12:53 AM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement