How to know when statistics were last updated in SQL Server?

Sometimes the query behaves very badly because of outdated statistics.There are two ways in which we can see the time of last updated statistics for a table or indexed view. 

1: We can get last updated statistics information using DBCC SHOW_STATISTICS().
2: We can  also get last updated statistics information through STATS_DATE() function 
     and sys.stats system catalog view.

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 as 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:

How to Use:

The following TSQL code will returns all statistics information for the PK_RG_REGISTRATION index of the Rg_Registration table.

USE [TestVK]
GO 
DBCC SHOW_STATISTICS('RG_REGISTRATION',PK_RG_REGISTRATION)

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

Using sys.stats system catalog view

According to Microsoft BOL, 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 below:

SELECT OBJECT_NAME(object_id) AS [ObjectName],[name] AS [StatisticName],
STATS_DATE([object_id], [stats_id]) AS [StatisticUpdateDate] 
FROM sys.stats WHERE OBJECT_ID=OBJECT_ID('RG_REGISTRATION');

Now we can see the query returns date of the  recent update for statistics on a table RG_Registration.


Reference: Microsoft Books Online

Thanks For Reading This Post!!!