SQL Server: Use STATS_DATE to know the most recent update for statistics on a table or indexed view

Hi Friends,

In one of my recent trainings, when I was demonstrating the internals of STATISTICS, a participant asked if we can find out the last update date of the statistics.

Yes, we can do that using STATS_DATE.

Here is a small example.

Create a table and insert some test data.

USE tempdb
GO
 
	 
CREATE TABLE Contact(
       FirstName nvarchar(100),
       LastName nvarchar(100),
       Phone nvarchar(30),
       Title nvarchar(20)
)
GO
-- Populate the table with a few rows.
INSERT INTO Contact
   VALUES(N'Amit',N'Bansal',N'917234852342',N'Mr')
INSERT INTO Contact
   VALUES(N'SarabPreet',N'Singh',N'759832758475',N'Mr')
INSERT INTO Contact
   VALUES(N'Amit',N'Karkhanis',N'729347283423',N'Mr')
INSERT INTO Contact
   VALUES(N'Rakesh',N'Mishra',N'659837598345',N'Dr')
INSERT INTO Contact
   VALUES(N'Piyush',N'Bajaj',N'72348729834234',N'Mr')
GO

Let us check the statistics.

sp_helpstats N'dbo.Contact', 'ALL'
GO

You will observe that there are no statistics right now on the table. Let us execute a simple query to generate a statistics.

SELECT * FROM Contact WHERE LastName = N'Bansal'
GO

Now, check again:

sp_helpstats N'dbo.Contact', 'ALL'
GO

You would see that statistics are created on LastName column. Now, the requirement is to find out the last/most recent update date for this statistics. The syntax for STATS_DATE is:

select STATS_DATE (object id,stats id)

We can get the object id and stats id from sys.stats system catalog.

Run the sp_helpstats SP again (as shown above) and copy the stats name and execute the following query by replacing the stats name with yours.

select * from sys.stats
where name = '_WA_Sys_00000002_1920BF5C'

You shall now have the object id and stats id which you can use in the STATS_DATE query as follows:

select STATS_DATE (421576540,2)

Here is the output on my system:

———————–

2011-10-28 14:07:38.290

(1 row(s) affected)

You can manually update the stats and run STATS_DATE again which will reflect the updated date.

UPDATE STATISTICS dbo.Contact;
 
select STATS_DATE (421576540,2)

———————–

2011-10-28 14:43:19.047

(1 row(s) affected)