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

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.

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 (http://blog.SQLAuthority.com)

About these ads

38 thoughts on “SQL SERVER – Find Statistics Update Date – Update Statistics

  1. Pinal, the problem is really not when stats where updated (generally) but how to find out when stats are out of date, which can be anywhere from a few hours to a few days, depending on activity on the table. SQL 2000 had a rowmodctr that gave a good indication of that but 2005 using a colmodctr now which is very confusing and difficult to go by. Any ideas?

    Thanks

    Malathi

    Like

    • Hi Malathi,

      You can use the below query to find all the Tables and Index with number of days statistics being old

      SELECT OBJECT_NAME(A.object_id) AS Object_Name, A.name AS index_name, STATS_DATE(A.OBJECT_ID, index_id) AS StatsUpdated ,
      DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DaysOld
      FROM sys.indexes A
      INNER JOIN sys.tables B ON A.object_id = B.object_id
      WHERE A.name IS NOT NULL
      ORDER BY DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DESC

      Thsi works fine with Both SQL Server 2005 and 2008

      Like

      • Hi Arun, thank you for the query, actually this only tells you how old the stats are, not if they are outdated. Stats are considered outdated in relation to the volume of data on the table. They can be outdated in hours on a very active table or can stay up to date for several days if the table is static. Giving users a way to find outdated statistics has been a suggestion given to the SQL Server Development team for some days now, if i find the link i will post.

        Malathi

        Like

  2. Hi Pinal,
    Is it a good option to allow sql server to automatically create statisitics for tables. Would it affect performance.
    I have heard two sides about this feature, any thoughts?
    Thank you

    Like

    • AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS works fine if the size of table is small and less updates are happening. If you program does bulk loading or mass updating of data, This option would slow down the process and create CXPACKET locks, so it would be good to set this options off and then manually update statistics after data loading or processing is complete.

      Decide by reviewing the scenario on how your database and application is used

      Like

  3. Hi Pinal,

    I Update indexes with full scan weekly. so when I run:

    SELECT name AS index_name,
    STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
    FROM sys.indexes

    I expect it to show me that all indexes were updated this weekend. But there are several records which look like:

    index_name StatsUpdated
    clust 2005-10-14 01:36:26.140
    clust 2005-10-14 01:36:26.140

    What does it mean?

    And,
    How do I know that the statistics are out of date ( if in case I need to update statistics with full scan more often)

    thanks.

    Like

  4. We have one database around 3TB and we enable “autostats on” . However, we are running UPDATE STATS on entire database on weekly basis and we noticed high IO during the update stats job is running. Is update status causing high IO?

    Like

    • Please advice how to make autostats off. The syntax i used script –
      exec sp_autostats table_name ‘OFF’
      giving error …

      Like

  5. Hello Pinal,

    Thanks for that post on statistics.

    I would like to ask you a little bit more on statistics though.

    I have prepared a script for optimization of some of my stored procedures. I did it with Database Engine Tuning Advisor. That script contains a lot of non-clustered indexes and lots of statistics creation. Due to certain circumstances I would like to ignore statistics and remove them from the script. So the question is what worst can be expected for that optimization plan without statistics in it? Will non-clustered indexes be performing reasonably well without them?

    Thanks, Grettir

    Like

  6. Hello Grettir,

    Updated statistics help in creating the better optimized execution plan. Even SQL Server automatically update statistics on the basis of Update Statistics database level setting but skip for some columns that are not repeatedly used. So statistics is usually recommonded to keep updated.

    Regards,
    Pinal Dave

    Like

  7. Where do I find the last update for stats that were created on columns of a table, but are not indexes?

    We cannot use sys.indexes as there are no rows for these stats in this view.

    Like

  8. Hi Steve,
    With respect to your question to findout how to get the statistics update date for non Index Columns, Use this:

    SELECT STATS_DATE(OBJECT_ID,STATS_ID),* FROM SYS.STATS WHERE OBJECT_ID = OBJECT_ID(‘TABLE_NAME’)

    Thanks,
    Surya

    Like

  9. Hi Pinal

    I want to retrive all statistics update date (Index statistics and auto created statistics ) is it possible for all table in databases

    I want a script

    Regards
    Jayant Dass
    [phone number removed]

    Like

  10. Hi Pinal,

    I have a quick question for you..I am rebuilding index with RECOMPUTE OFF..Will it only update Index statistics or any other table related statistics..In case No.Do you suggest running UPDATE STATS WITH FULL SCAN as an additional option ?

    Like

  11. Pinal,

    I have an Update Statistics task (all databases, all objects, all existing statistics) in a Maintenance Plan that runs weekly. My statistics all either have a STATS_DATE within the past week or NULL. Why would a statistics object not be updated by the Update Statistics task? Should the ones not getting updated be dropped? (I am using SQL Server 2005.)

    Thanks,
    Mark

    Like

  12. 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.

    Like

  13. 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 = '#'

    Like

  14. 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

    Like

  15. Pingback: SQL SERVER – Weekly Series – Memory Lane – #013 « SQL Server Journey with SQL Authority

  16. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s