Viewing the Last Updated Date for Statistics

Question: You talked about when statistics get updated last week. How can I see when the statistics were last updated?

Answer: Actually, this question is much more interesting than it seems. Most people think the only way to access the "last modified date" for a statistic is to access it through the header information using DBCC SHOW_STATISTICS:

It's true that the first result set shows the "header" information including when the statistics were last updated (second column in first result set) but how can you use this data programmatically?

Related: Update the Statistics on Recently Modified Tables Only

In SQL Server 2005, they added STAT_HEADER (as well as DENSITY_VECTOR and HISTOGRAM) as options to DBCC SHOW_STATISTICS. Each of these, when added, will produce ONLY that particular tabular data set. As a result, you can INSERT/EXEC the output from the statistics header (or any of the others) into a table. Once the data's in a table then you can programmatically analyze it. For example, you could run automation routines that do things like:

How can you programmatically analyze the statistics header?

You need to pre-create the table and then use dynamic string execution.

CREATE TABLE StatisticsHeader(     Name                   sysname,     Updated                datetime,     [Rows]                 bigint,     RowsSampled            bigint,     Steps                  tinyint,     Density                decimal(9,5),     AverageKeyLength       decimal(9,5),     StringIndex            nchar(3),     FilterExpression       nvarchar(1000),     UnfilteredRows         bigint)goINSERT StatisticsHeaderEXEC ('DBCC SHOW_STATISTICS (''tablename'', ''index/statistics name'')      WITH stat_header')goSELECT * FROM StatisticsHeadergo

With the StatisticsHeader table you can analyze all aspects of when the statistics were last created/updated as well as whether or not it was handled through a fullscan or a sampling. This really allows for a lot of automation options!

But, if you just want the date (and date alone), I have a far easier way to get it.

I just need the date! Isn’t there an easier way?

There’s a simple (but not well-known) function called stats_date that’s been around since the 6x days. However, where you use it has changed over the years. When column-level statistics first came out (in SQL Server 7.0) they shared IDs with indexes. In total, you could not have more than 249 nonclustered indexes AND column-level statistics. To find information about statistics, you’d use sysindexes.

Related: Making the Most of Automatic Statistics Updating

In SQL Server 2005, they separated the IDs for column-level statistics from nonclustered index IDs and created a catalog view to see them: sys.stats. However, sys.stats shows ALL statistics – even those on indexes. As a result, if you’re looking for last updated date for all of your statistics then just query sys.stats:

SELECT OBJECT_NAME(object_id) AS ObjectName,    STATS_DATE(object_id, stats_id) AS StatisticsDate,    *FROM sys.stats

Related: The Importance of SQL Server Statistics

Have fun!
kt