I received an email from friend today asking how he can see when statistics were last updated in SQL Server. Well, in SQL Server, there are two ways to access the last modified date of a statistic, which are:

1. Through the header information using DBCC SHOW_STATISTICS.
2. Through STATS_DATE() function and sys.stats system catalog view.

1) Through the header information using DBCC SHOW_STATISTICS

According to Microsoft Books Online, DBCC SHOW_STATISTICS returns the header, histogram, and vector density based on the data stored in the statistics object. The syntax lets you specify a table or indexed view along with a target index name, statistics name, or column name (see below):

USE [<Database_Name>]
GO
DBCC SHOW_STATISTICS (table_or_indexed_view_name 
                     ,index_or_statistics_name_coloumn_name') 
[ WITH [ NO_INFOMSGS ] < option > [ , n ] ]
< option > :: =
    STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM

Example: Returning all statistics information

The following example returns all statistics information for the IXC_ServerChecks_Results_LoadID index of the ServerChecks.Results table.

USE [SQLComplianceDW]
GO
DBCC SHOW_STATISTICS('ServerChecks.Results'
                     ,IXC_ServerChecks_Results_LoadID)

The first result set of the DBCC SHOW_STATISTICS command returns the header information, including when the statistics were last updated. To only return the header information about the statistic, you executed DBCC SHOW_STATISTICS with STATS_HEADER option.

USE [SQLComplianceDW]
GO
DBCC SHOW_STATISTICS('ServerChecks.Results', IXC_ServerChecks_Results_LoadID) WITH STAT_HEADER

Note that STAT_HEADER, HISTOGRAM and DENSITY_VECTOR options are only available in SQL Server 2005 and later releases.

As you can see from above example, DBCC SHOW_STATISTICS returns statistics information for the index name, statistics name, or column name of the specified table or in indexed view, however, if you only want to see the statistics update date for all statistics objects that exists for the tables, indexes, and indexed views in the database, you query sys.stats and use STATS_DATE() function.

2) Through STATS_DATE() function and sys.stats system catalog view

According to SQL Server Books Online, sys.stats system catalog view is the best way to see each statistics object information that exists for the tables, indexes, and indexed views in the database. This catalog view exists in SQL Server 2005 and later. You can use this system catalog view with STATS_DATE() function, to view most recent update date for each statistics object that exists for the tables, indexes, and indexed views in the database. This function accepts two parameters, that is, object_id, stats_id. To determine date when the statistics where last updated, you execute sys.stats system catalog view with STATS_DATE() function, as follow:

SELECT OBJECT_NAME(object_id) AS [ObjectName]
      ,[name] AS [StatisticName]
      ,STATS_DATE([object_id], [stats_id]) AS [StatisticUpdateDate]
FROM sys.stats

As you can see, this query returns date of the most recent update for statistics on a table or indexed view.

References

• Microsoft Reference: DBCC SHOW_STATISTICS (Transact-SQL)
• Microsoft Reference: STATS_DATE (Transact-SQL)
• Microsoft Reference: sys.stats (Transact-SQL)