December 20, 2012 at 7:45 pm
Hi,
I was working on updating statistics for a few databases. I had tried to find out the following without much success.
1. How to determine if the statistics for a table/index requires update?
2. What would be the ideal threshold for scheduling the update statistics job?
3. The statistics gets updated automatically when the indexes are rebuilt. If (for ex.) I have a job that rebuilds indexes once in 2 weeks, I need to check if the statistics are out-of-date after a week and based on the result I would execute sp_updatstats. Is there a way to get this accomplished?
Any inputs/pointers please....
December 21, 2012 at 12:51 am
Mohammed Imran Ali (12/20/2012)
2. What would be the ideal threshold for scheduling the update statistics job?
There isn't one. There's no single threshold that works for all tables in all circumstances.
Generally... tables that are 'append' should get more frequent stats updates. (append meaning data is added at the 'end' of an index, like an orders table always gets higher dates)
Tables that change seldom are probably OK with the 20% threshold from auto update
Tables that have their data distribution changed a lot need more frequent stats updates.
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply