SQL Server Script: When were Statistics last updated October 30th, 2012

Vinod Kumar

At my job I often help customers with Performance tuning their SQL Server environments and their application. One of the common recommendation we give is to check their maintenance plans and see if the Statistics of tables are up-to-date. This is a very common exercise and given that you have 1000s of tables to monitor, you might want an easy script to figure out which are those tables / indexes that need attention. I am giving a simple script that we use at our environments to identify the same.

OBJECT_NAME(ind.object_id) AS ‘Table Name’, 
ind.name AS ‘Index Name’, 
STATS_DATE(ind.object_id, ind.index_id) AS ‘Statistics Date’
FROM  sys.indexes ind
WHERE OBJECTPROPERTY(object_id,‘IsMSShipped’)=0

With the above script the output would be as below:


Look at the “Statistics Date” column and this gives you information of how dated the statistics are. Once you have figured out the statistics are out-of-date by few weeks or months, use the UPDATE STATISTICS command to update the same. Yet another command used for this purpose is sp_updatestats.

Also, even if you have AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNC (Statistics documentation) options enabled inside your database Statistics *can* be out-of-date. So analyze your environments from time-to-time and then come up with your updating strategy.

Tags: , , , , , , ,

This entry was posted on Tuesday, October 30th, 2012 at 20:50 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “SQL Server Script: When were Statistics last updated”

  1. Samith C Valsalan says:

    Hi Sir

    I heard that statics update will Improve the performance of the DB ,
    I would like to know what happening inside DB once we updating the statics and how it helps Sql to improve the performance.

    Thanks in advance ..

    • Vinod Kumar says:

      Samith – SQL Server is a cost based optimizer and it used statistics to perform queries and find the potential cost of executing a query. SQL Server doesn’t actually read the rows on the disk directly to calculate the cost, statistics are used behind the scenes to get a rough plan before execution on actual rows.

      When there are tons of update and delete’s then there is a possibility for statistics to be out-of-date and hence when SQL Server uses this, it has gets into a problem that what it guessed to what it finds inside a SQL Server actually are different. Now this causes performance glitches and hence updating of stats helps here.

      http://blogs.extremeexperts.com/2010/09/03/performance-tuning-statistics-io/ gives you the IO statistics and I am referencing this for your needs. This is very basic and I have used it till date.

      • Samith C Valsalan says:

        Thank u Sir ..

        I would like to know more about optimization ,
        can u please suggest me any books/blogs ?

        • Vinod Kumar says:

          There is no one book or one location. You will need to start playing with SQL Server and learn by optimizing. It is a continuous learning process and I have had loads of fun doing that till date.

Leave a Reply