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

king said:


January 16, 2018 11:34 PM

king said:



January 16, 2018 11:34 PM

zzyytt said:">">">">

May 1, 2018 7:01 PM

linying123 said:


May 10, 2018 8:11 PM

shenyuhang said:


June 1, 2018 7:09 PM

kanker paru paru bisa sembuh tanpa operasi said:

June 28, 2018 6:40 PM

dongdong8 said:



June 29, 2018 3:25 AM

kesehatan said:

July 4, 2018 10:59 PM

kesehatan said:

July 5, 2018 8:37 PM

kesehatan said:

July 6, 2018 9:04 PM

kesehatan said:

July 6, 2018 9:04 PM

kesehatan said:

July 9, 2018 12:44 AM

obat kanker payudara terbaik said:

July 10, 2018 8:53 PM

obat meningkatkan kecerdasan otak said:

July 13, 2018 9:45 PM

obat keputihan berlebihan dan berbau said:

July 15, 2018 8:52 PM

linying123 said:



July 16, 2018 8:56 PM

dongdong8 said:



July 23, 2018 11:39 PM

obat diabetes said:

August 1, 2018 11:17 PM

obat sinusitis said:

August 10, 2018 7:22 PM

yaoxuemei said:


August 15, 2018 2:13 AM

masker wajah alami untuk menghilangkan jerawat said: Masker wajah alami untuk menghilangkan jerawat Obat liver alami aman dan terbaik Obat herbal sinusitis kronis terbaik Obat bronkitis anak secara alami Obat Kanker Payudara terbaru 2018 Cara meningkatkan nafsu makan secara alami Obat herbal diabetes menurunkan gula darah Obat herbal kusta terbaik Obat benjolan di leher tanpa operasi Obat Herbal Kanker Otak tuntas hingga akar Obat TBC alami aman tanpa efek samping Obat radang amandel sembuh tanpa operasi Obat asam urat alami tanpa efek sampinfg Obat tetes Sariawan secara alami Cara mengobati mata bengkak dengan cepat Obat tradisional kanker lambung terbaik Obat kanker paru paru Obat usus buntu alami tanpa operasi Obat asam lambung naik cara alami

August 20, 2018 8:59 PM

chenjinyan said:



August 22, 2018 11:27 PM

shenyuhang said:


August 23, 2018 10:27 PM

obat herbal koresterol terbaik said:

August 23, 2018 10:58 PM

obat usus buntu alami tanpa operasi said:

August 24, 2018 12:21 AM

obat benjolan di tubuh said:

August 30, 2018 7:40 PM

obat wasir said:

September 4, 2018 8:26 PM

obat katarak said:

September 16, 2018 5:46 PM

obat maag said:

September 17, 2018 7:10 PM

obat limfoma said:


September 23, 2018 6:50 PM

xiaojun said:

20180928 junda

September 27, 2018 10:49 PM

naborsa said:

October 18, 2018 12:00 AM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement