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 (https://blog.sqlauthority.com)
43 Comments. Leave new
Excellent…
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
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
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
Sorry i meant ‘stats are considered outdated in relation to volume of activity on the table, not volume of data’.
HI have you found allready a script to detect out of date statistics?
Great query! I was looking for something for all objects. Thanks :-)
I don’t why but for some reason the query that Mr. Pinal Dave provided us to check the statistics is not working but I found Arun Raj is working best.
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
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
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.
Hello Manjot,
By which command you update the statistics. SQL Server only update statistics if that is stale and ignore very small tables.
For tables and indexes SQL server uses a rowmodctr column in sysindexes tables.
For more details about when SQL Server decide to update statistics please see the kb article: https://support.microsoft.com/en-us/help/195565/statistical-maintenance-functionality-autostats-in-sql-server
Regards,
Pinal Dave
I update statistics using maintenance plan “update statistics” with full scan
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?
Please advice how to make autostats off. The syntax i used script –
exec sp_autostats table_name ‘OFF’
giving error …
Hello Raj,
During UPDATE STATS data from tables is analyzed. This analyzed process read the data from disk that causes the high I/O.
Regards,
Pinal Dave
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
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
Thank you Pinal.
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.
Can the statistics last update date be grabbed for SQL Server 2000 too?
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
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]
Can i run exec sp_updatestats aginst each database in sqlserver ,is it enough for performance.
Thanks for this Pinal, I’ve been searching for this for a while.
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 ?
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
Hi Pinal,
Is is possible to obtain the date when the update statistics was run on the whole database.
Thanks
S2
Hello!
Just wanted to let you know that these scripts helped me research the fact that it is important to think about when to create indexes when using temporary tables (for reporting or other purposes). You can read about the research at: