March 31, 2015 at 7:11 am
Hi all,
when I run:
SELECT
name AS StatisticsName,
STATS_DATE(object_id, stats_id) AS StatisticsUpdatedDate
FROM sys.stats
ORDER BY StatisticsUpdatedDate desc
GO
I get 1000+ rows on my Prod Database. Most of them are 1 month or older. Now how do I find out where is thethreshold? which one need to update example: older than one month or 2 months...?
March 31, 2015 at 7:42 am
This will give you operational info behind the stats where there have modifications. You will have to interpret this info, cos 10000 modifications is not worth an update statistics if your table has 100 mill rows.
SELECT object_name(a.object_id),name, b.*,
STATS_DATE(a.object_id, a.stats_id) AS StatisticsUpdatedDate
FROM sys.stats a
cross apply sys.dm_db_stats_properties (a.object_id, a.stats_id) b where modification_counter >0 and a.object_id >100
ORDER BY StatisticsUpdatedDate desc
GO
March 31, 2015 at 7:59 am
There isn't a threshold. A table which never gets updated could have stats 5 years old which are still good. A table which gets updated thousands of times a second could have stats an hour old which are horribly out of date. It's a hell of a lot more complex than 'older than x months'
If you have time in maintenance windows to update all stats with full scan, do so. Then watch for tables which seem to get stats-related problems and update those more frequently.
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 31, 2015 at 12:03 pm
I'd suggest looking at the statistics in more detail, especially on the tables that are the biggest or that are called the most frequently. Get a sense of how the data changes in those tables and if the statistics accurately reflect those changes. That will drive your decisions. DBCC SHOW_STATISTICS
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply