March 10, 2009 at 8:39 pm
Hi,
need a little advice,
If I reorganize an index should we or will it be good if we update the statisitics of indexes after reorganizing. if we do this two things, how much resoureces it will take all together? will total resources taken by reorganizing & stattistics update equal to rebuild.
please advice.
Thanks
Joseph
March 10, 2009 at 9:29 pm
As always, it depends, but we've always found it better to update the stats comparatively frequently (weekly is our standard), particularly under SQL2k5 where we've found queries against tables with skewed data distribution can be prone to choosing comparatively inefficient query plans. If you've got (or can make) the time to do it do so.
If you use sp_updatestats with the default parameters it will be very quick, depending as much on the number of tables as on the size of the tables because it only samples the larger tables. We have a 1TB database with ~14,000 tables in which it takes around 30 minutes to run the update stats, but for a 1TB database with ~150 tables it takes around 20 minutes. In both databases rebuilding the clustered index on a 300GB table takes around 2-3 hours, and a reorg of those same tables takes 16+ hours. There's a lot of other factors involved too though, so don't take this as gospel but rather an indication.
March 11, 2009 at 5:51 am
When and how often to update statistics and reorganize/rebuild indexes is really dependent on your system. You do need to worry about the order of these operations though. I would suggest that any statistics update operations precede index operations. The principal reason for this is because most of the time, you're going to use a sampling of the data for the statistics update, the default. But, when you rebuild an index, it does a full scan of the data. That means it's statistics after a rebuild are better than the statistics after a standard update.
"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
March 12, 2009 at 7:50 am
Thanks guys...appreciate it...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply