THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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,
    i.name 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.

 Smile 

I hope you find this useful!

~Kalen

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

Comments

 

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:

http://sys2dmvs.codeplex.com/

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

Thanks

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?

~Kalen

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:

http://sqlserverbuilds.blogspot.cz/

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

September 9, 2013 1:34 PM
 

Steven said:

Kalen,

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.

~Kalen

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! :)

-Steven

October 12, 2013 12:53 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

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