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:

DBCC SHOW_STASTISTICS ('tablename', 'index or statistics name')

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?

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:

If the stats are more than a week old

OR

If the stats were last updated using a sampling (rows sampled < rows)

then, update statistics with a fullscan.

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

INSERT StatisticsHeader
EXEC ('DBCC SHOW_STATISTICS (''tablename'', ''index/statistics name'') 
     WITH stat_header')
go

SELECT * FROM StatisticsHeader
go

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.

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

Have fun!
kt

Comments

Plain text