March 8, 2014 at 3:31 pm
what is the best way to find stale Statistics, i can find when Statistics was last updated. is there a way to tell which table stats are missing
March 8, 2014 at 4:43 pm
If you have a job that runs frequently that updates statistics, or have auto statistics turned on, then they should be fairly up to date. To see when they were last updated or to see how many rows were modified since your last update, check out the rowmodctr field in sysindexes.
Check this article out, I think it will get you going!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 8, 2014 at 5:15 pm
thanks. i dont see a link i m missing something.
March 9, 2014 at 10:11 am
My Bad 😉 Was just teasing:
http://www.mssqltips.com/sqlservertip/2628/how-to-find-outdated-statistics-in-sql-server-2008/
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 17, 2014 at 5:20 am
Here is part of the logic I use to refresh statistics:
select Object_name(object_id) AS TableName
, name as StatName
, STATS_DATE(object_id, stats_id) AS statistics_update_date
from sys.stats
where name not like '_W%'
AND name not like '_d%'
AND Object_name(object_id) NOT LIKE 'sys%'
AND Object_name(object_id) NOT LIKE '!OLD%'
UNION
select Object_name(object_id) AS TableName
, name as StatName
, STATS_DATE(object_id, index_id) AS statistics_update_date
from sys.indexes
where name not like '_W%'
AND name not like '_d%'
AND Object_name(object_id) NOT LIKE 'sys%'
AND Object_name(object_id) NOT LIKE '!OLD%'
March 17, 2014 at 5:34 am
djj (3/17/2014)
where name not like '_W%'
Why are you explicitly excluding the automatically created statistics?
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
March 17, 2014 at 5:40 am
As stated this is part of the code I use to generate UPDATE STATISTICS command that I run manually. And since I did not create the indexes, I do not update statistics for them. This may be a bad thing but I did not know if it would help or hurt.
March 17, 2014 at 5:56 am
The automatically created stats are ones which the Query Optimiser requests be created to help it generate optimal execution plans. Keeping them updated is as important or more important than updating stats on indexes. They at least get updated when the index is rebuilt.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply