July 26, 2009 at 2:00 pm
Hi,
We have SQL Server 2005 EE x64 with SP3. I have created maintenance plan tasks in below order:
1) Database Ingritycheck
2) Rebuild Indexes
3) UpdateStatistics
4) History cleanup
These above 4 jobs running weekly on Sunday.
Is the above Order of running jobs correct?
Is UpdateStatistics necessary? Because in SQL Server 2005 statistics will be updated automatically, so you guys run this UpdateStatistics weekly?
and this UpdateStatistics job takes 30 mins for all databases BUT when I execute SP_UPDATESTATS for all databases, it takes only 10 mins. Why this big difference between automated job & manually running updatestatistics?
Is UpdateStatistics job created by Maintenance paln & SP_UPDATESTATS different? OR are the both do same work?
are you guys do the REBUILD INDEXES weekly by default OR select Highly active databases and do REBUILD INDEXES for them? I'm doing REBUILD INDEXES weekly for all databases..please advice me
July 26, 2009 at 2:31 pm
Do the update stats before the reindex. Update stats will update all statistics with a sample, index rebuild will also update the stats on the indexes with full scan. Hence, do the stats update after the index rebuild and you'll be reducing the accuracy of some stats.
Auot update (and sp_updatestats) only update statistics that SQL thinks are outdated. If is possible for stats that aren't considered out of date to be sufficiently wrong to cause poor performance on some queries. Especially on bigger tables.
Personally, I wouldn't use the rebuild indexes maint plan on anything other than a small, infrequently used DB. It rebuilds everything, regardless of how fragmented it is. Grab one of the scripts that check fragmentation first. There's a good one on this site: http://www.sqlfool.com
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
July 26, 2009 at 11:49 pm
thank you very much,
I'm also thinking to rebuild or reindex based on the fragmentation level instead Rebuild all the indexes.
Could you please clarify me that how often the Update statistics job has to run? weekly or daily?
Is it fine to run the rebuild or reindex job based on the fragmentation level weekly? or it should be run daily?
July 27, 2009 at 1:59 am
Mani (7/26/2009)
Could you please clarify me that how often the Update statistics job has to run? weekly or daily?
Often enough that your stats don't get out of date to the point that it's affecting queries. No way to give you a hard and fast fixed number, it varies by system.
Is it fine to run the rebuild or reindex job based on the fragmentation level weekly? or it should be run daily?
Depends how fast your indexes get fragmented. Again, no way to give a hard and fast number.
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
July 27, 2009 at 10:40 pm
Could you plz explain me the below
July 27, 2009 at 10:45 pm
Often enough that your stats don't get out of date to the point that it's affecting queries. No way to give you a hard and fast fixed number, it varies by system
.
how should we know that stats got outdated? Is there any script/procedure to run continuously to monitor for out of date stats & if that procedure finds the out dated stats, it should run the update statistics job?
Is this the way you guys do?
Depends how fast your indexes get fragmented. Again, no way to give a hard and fast number.
Is there any script/procedure to run continuously to monitor for index fragmentation & if that procedure finds that indexes are fragmented, it should run the index reorganize/rebuild job?
Is this the way you guys do? Can we do index reorganize\rebuild on the fly during business hours?
right now I'm blindly running these 2 jobs weekly? I would like to know how other DBAs follow for highly transaction databases?
thanks
August 10, 2009 at 6:19 pm
I'm running the UpdateStatistics job weekly and its taking 40 mins. Does this weekly update statistics enough?
JOB RUN:DBMaintenancePlan.UpdateStatistics' was run on 8/9/2009 at 1:00:00 AM
DURATION:0 hours, 40 minutes, 5 seconds
STATUS: Succeeded
MESSAGES:The job succeeded. The Job was invoked by Schedule 23 (DBMaintenancePlan.UpdateStatistics). The last step to run was step 1 (UpdateStatistics).
thanks
August 21, 2009 at 10:11 pm
Please advice...
August 22, 2009 at 1:45 am
Mani (8/10/2009)
I'm running the UpdateStatistics job weekly and its taking 40 mins. Does this weekly update statistics enough?
Maybe.
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply