When were statistics updated?–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I ran across the STATS_DATE function recently, and it’s one that I hadn’t used in production code. I’m not sure how this escaped me, as it was added in SQL Server 2008, but I rarely see it written about, so it’s not just me.

This function takes an object_id and a stats_id, and returns the date the statistics were last updated. The statistics id is the id from sys.stats and doesn’t necessarily correspond to the index ID.

As a quick example, if you look at the Sales.SalesOrderHeader table in AdventureWorks2012, you can run this:

SELECT STATS_DATE ( 1266103551 , 2) 

This should return a simple date. I don’t know if you’ll have the same date in your database, but I assume this is the default date for the sample database.

2016-06-06 14_07_56-Phone

Obviously these stats are out of date.

Or are they? I don’t use this database a lot and haven’t changed the data in this table that I’m aware of. In that case, they may be up to date.

This can be a handy function, but remember, the age of stats only matters if you’ve had data changes. However with having an understanding of both pieces of information, you might use this to accelerate statistics rebuilds ahead of what AUTO STATISTICS might do.

SQLNewBlogger

This was a good chance to dig into and look at how a function works in SQL and how I might use it. You could write this easily.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

2 Responses to When were statistics updated?–#SQLNewBlogger

  1. papynormand says:

    Thanks for this excellent post about a topic I have studied when I bought the SQL Server 2008 Developer Edition. Easy to understand , it is a real pleasure to read and I will do some tests to try to deep my understanding of this nice and useful function.
    Cheers!!!

  2. papynormand says:

    Sorry for this useless new post , it is only to check the notify new comments.

Comments are closed.