SQL SERVER – Find Statistics Update Date – Update Statistics

Statistics are one of the most important factors of a database as it contains information about how data is distributed in the database objects (tables, indexes etc). It is quite common to listen people talking about not optimal plan and expired statistics. Quite often I have heard the suggestion to update the statistics if query is not optimal. Please note that there are many other factors for query to not perform well; expired statistics are one of them for sure.

If you want to know when your statistics was last updated, you can run the following query.

USE AdventureWorks
GO
SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('HumanResources.Department')
GO

SQL SERVER - Find Statistics Update Date - Update Statistics statsupdate

Solarwinds

If due to any reason you think that your statistics outdated and you want to update them, you can run following statement. In following statement, I have specified an additional option of FULLSCAN, which implies that the complete table is scanned to update the statistics.

USE AdventureWorks;
GO
UPDATE STATISTICS HumanResources.Department
WITH FULLSCAN
GO

Please note that you should only run update statistics if you think they will benefit your query and when your server is not very busy. If you have auto update “usually” on, the SQL Server takes care of updating stats when necessary. Here, is a quick example where you can see the updating of older statistics using fullscan.

SQL SERVER - Find Statistics Update Date - Update Statistics statsupdate1

Statistics is very deep subject, and a lot of things can be discussed over it. We will discuss about many other in-depth topics in some other article in future.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQLAuthority News – Download Sample Database for Microsoft SQL Server
Next Post
SQLAuthority News – Download White Paper – Troubleshooting Performance Problems in SQL Server 2008

Related Posts

43 Comments. Leave new

  • Hi,

    I need Find Statistics Update Date – Update Statistics for entire database,your script will work for individual database,but I need for entire database.

    Reply
  • For entire database you can do something like this. Modify the query for your own needs

    exec sp_msforeachdb @command1=’
    use [#];
    if ”#” not in (”tempdb”)
    begin
    print ”Database: #”
    select tab.name,ind.name ,stats_date(ind.id,ind.indid), rows
    from sysobjects tab, sysindexes ind
    where tab.id=ind.id and tab.id > 99 and stats_date(ind.id,ind.indid) < dateadd(day,-7,getdate())
    order by stats_date(ind.id,ind.indid),ind.name;
    end
    ', @replacechar = '#'

    Reply
  • Hi Pinal,
    How can I get the when the last time a particular stats is used? That would tell me that whether that stat should be updated or not?
    Vivek

    Reply
  • Hi Pinal,

    Please ans the above questions.
    Setu Kumar

    Reply
  • Hi Pinal,
    Thanks for your nice Post and i have seen few of Updated Date of Statistics is Null in our database,Is there any Specifice reason?

    Regards,
    Saurabh

    Reply
  • Hi Pinal,

    if we apply Update Statistics where the data will stored ..?

    Regards,
    Brahma

    Reply
  • Hi Pinal,

    Please answer for above question..

    Regards,
    Brahma

    Reply
  • What are the tables

    Reply
  • thanks exactly what I was looking for.

    Reply
  • I have update the stats and auto_update_stats is also set ON but still I am seeing Null in output for statsUpdated. What is the problem behind this?

    Reply
  • Is there any way to find out stats create date?

    Reply
  • Why there is null value for stats date? I have checked in my local

    Reply
    • Thomas Franz
      May 14, 2019 1:43 pm

      Old question, but I citate from the BOL (https://docs.microsoft.com/de-de/sql/t-sql/functions/stats-date-transact-sql): “Returns NULL if a statistics blob was not created.”

      In my database this is the case for clustered ColumnStore indexes and I assume the STATS_DATE() function will return NULL for disabled indexes too.

      BTW: the second parameter of this function is not the index_id but the stats_id (even if it is usually the same).

      So you can simply run

      SELECT STATS_DATE(s.object_id, s.stats_id) dt, s.*
      FROM sys.stats AS s
      WHERE s.object_id = OBJECT_ID(‘dbo.table’)
      ORDER BY dt
      ;
      to see the update date even on the autocreated (single column) statistics, that not behalf to an index (‘_WA_SYS_%’)

      Reply

Leave a Reply

Menu