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)

 

 

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

Leave a Reply

Your email address will not be published. Required fields are marked *