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
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.
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 = '#'
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
Hi Pinal,
Please ans the above questions.
Setu Kumar
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
Hi Pinal,
if we apply Update Statistics where the data will stored ..?
Regards,
Brahma
Hi Pinal,
Please answer for above question..
Regards,
Brahma
What are the tables
thanks exactly what I was looking for.
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?
Is there any way to find out stats create date?
Why there is null value for stats date? I have checked in my local
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_%’)