September 16, 2013 at 10:42 pm
I'm using the below query to find when was my statistics last updated. But I wanted to know how stale my statistics are ? Is there any script to get that information?
'USE [?];
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')
begin
SELECT ''CHECKING STATS FOR '' + DB_NAME() AS ''DATABASE NAME''
SELECT OBJECT_NAME(A.OBJECT_ID) AS ''TABLE NAME''
, A.NAME AS ''INDEX NAME''
, STATS_DATE(A.OBJECT_ID,A.INDEX_ID) AS ''STATS LAST UPDATED''
FROM SYS.INDEXES A
JOIN SYS.OBJECTS B
ON B.OBJECT_ID = A.OBJECT_ID
WHERE B.IS_MS_SHIPPED = 0
ORDER BY OBJECT_NAME(A.OBJECT_ID),A.INDEX_ID
end'
September 17, 2013 at 4:06 am
There's no direct way to know for sure. You can run DBCC show_statistics and see the date the stats were updated, but that doesn't mean much. Maybe the table hasn't been updated either. Instead, you need to know the data so that you can compare what you should be seeing in the statistics to what you are seeing in the data. Most of the time the automatic maintenance will be adequate, but sometimes you will need to intervene.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 17, 2013 at 5:24 am
If you suspect a poorly performing query you could check the execution plan to see what the cardinality estimates are too. If there is skew (estimated 1, actual thousands and visa versa) then this could be an indicator of stale stats.
September 17, 2013 at 5:36 am
You can use sys.sysindexes.
The column rowmodctr contains the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.
http://technet.microsoft.com/en-us/library/ms190283.aspx
[font="Verdana"]Markus Bohse[/font]
September 17, 2013 at 12:24 pm
so there is no way to list the staled statistics in sql server? other than getting when stats were last updated?
I want to update stats that are staled not all stats. Please advise
September 17, 2013 at 12:29 pm
gary1 (9/17/2013)
so there is no way to list the staled statistics in sql server? other than getting when stats were last updated?I want to update stats that are staled not all stats. Please advise
There is no way to know, in an automated fashion, that statistics are out of date. You have to be able to see the data and interpret if the statistics need to be updated. Sorry.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 17, 2013 at 2:34 pm
The problem is in the definition of 'stale'
Old stats aren't necessarily stale. If the table hasn't had a data modification in 6 months, then 6 month old stats are fine. You can use the rowmodctr value (which is no longer accurate) or the columns in sys.dm_db_stats_properties, but then the question becomes how many rows have to change before the stats are 'stale'? I've had tables where if 1% of the values in a column changed the stats needed to be updated or queries went really bad. I've had others where 50% of the rows could have changed and there would have been no effect.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply