April 12, 2011 at 10:55 am
What is common setup for maintenace plan besides backup, cleanup histories and older backups?
We also do a weekend job On Sunday- to check db integrity, reorganize index then update statistics.
I read a post http://sql-server-performance.com/Community/forums/p/29498/155881.aspx
It says it's not a good idea after rebuild index do an update statistics, is that true? we do reorgornize index instead of rebuild index then do an update statistics, does it make difference?
Should we use rebuild or reorganize index?
Any good way of doing this?
Thanks
April 12, 2011 at 11:26 am
rebuild does an update stats as well. reorg doesn't touch the stats.
If you can work the stats in your schedule, just do it. Autoupdate stats is good, but far from perfect...
April 12, 2011 at 2:52 pm
rebuild does an update stats as well. reorg doesn't touch the stats
A little clarification...an index rebuild will update index statistics with a full scan. Column statistics are not updated. An index reorganize does not update any statistics.
Auto update statistics will update statistics with the default sampling rate. Whether or not the sampling rate is good enough really depends on the nature of the table and your usage.
So, if you have the maintenance window to update statistics - then it is a good idea to get it done. If not, then you would need to identify any statistics that are not good enough and schedule just those statistics to be updated.
If you use the maintenance plan task to schedule the update statistics, and that is scheduled right after your rebuild operation, and the rebuild is rebuilding every index - then make sure you check the column statistics only and perform a full scan.
I that is not the case, you can use the procedure sp_updatestats - which will identify any statistics that are out of date and need to be updated. However, if you use this procedure - by default it will use the default sampling rate. To force the sampling rate to be the same as the previous time you can use the RESAMPLE option.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply