March 13, 2009 at 9:51 am
mostly depends on your hardware and I/O
on the old SCSI drives it took a long time. i have an archive db on the new SATA drives with one of the tables being about a billion rows or so. index rebuild takes around 30 minutes per index even though the server only has 8GB of RAM. if you have enterprise edition then you can do it online as well.
if i follow best practices and split the I/O some more than i can get better performance. i can also upgrade to the new 450GB 15000rpm drives for faster performance. for 30 million row tables it usually takes around 10 minutes
and we have had instances where updating the statistics manually screwed up the optimizer. we had to manually drop all statistics, rebuild the indexes and things came back to normal
March 13, 2009 at 11:24 am
SQL Noob (3/13/2009)
mostly depends on your hardware and I/Oon the old SCSI drives it took a long time. i have an archive db on the new SATA drives with one of the tables being about a billion rows or so. index rebuild takes around 30 minutes per index even though the server only has 8GB of RAM. if you have enterprise edition then you can do it online as well.
if i follow best practices and split the I/O some more than i can get better performance. i can also upgrade to the new 450GB 15000rpm drives for faster performance. for 30 million row tables it usually takes around 10 minutes
and we have had instances where updating the statistics manually screwed up the optimizer. we had to manually drop all statistics, rebuild the indexes and things came back to normal
Agreed hardware makes a huge impact; if I rebuild indexes on my 32GB, 64-bit cluster speed is alot faster then on my 8GB 32-bit server. But I am talking about performace impact on the queries; 30 min and 10 min doesn't seem alot. But when havint multiple tables like that with over 4000 tables in the database that can add up quickly. And on system that are 24x7 those even any downtime or slowness I get called about so I am always cautious about full rebuilds... without pre-authorized downtime/maintenance window.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 13, 2009 at 11:35 am
most of our thresholds for rebuilds are 5% or 10% fragmentation just so that if it fails one or two days it's no big deal. i've noticed i still get index seeks even at 30% fragmentation so you can set a high level.
i used to get the same thing when we were on 32 bit. we had a scheduled maintenance 10 days out of the month and people would still call helpdesk at 3am. haven't had a call like this since we went 64bit. new hardware is dirt cheap these days. HP DL380 G5 is $2000. 32GB RAM is $1200 - $1400 depending on your pricing.
March 13, 2009 at 11:43 am
For what it's worth, it's not just the duration of the rebuild that matters. You are also impacting the server by locking the index for the duration (offline rebuild) or stressing tempdb and dealing with some overhead from row versioning (online rebuild). Those may be ultimately be more significant factors.
March 13, 2009 at 12:09 pm
Joseph (3/12/2009)
Hi Matt - i checked most of the indexes, the avg_fragment_size_in_pages is 1, 1.5,1.383 like this.. can you tell me what does that mean...is there any certain point to which avg_fragment_size_in_pages values should come up?
From BOL
avg_fragment_size_in_pages
float
Average number of pages in one fragment in the leaf level of an IN_ROW_DATA allocation unit.
NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.
NULL for heaps when mode = SAMPLED.
You should consider maintenance if avg_fragment_size_in_pages < 8, as SQL reads pages in extents (ie 8 pages). Supposedly there is some (lesser) benefit up to around 32 pages although I've never tested this.
March 13, 2009 at 3:48 pm
Thanks you so much Matt...now I got things little clearer...
Just my curious to know:- as we know that if the avg_fragmentation_in_percent is between 10% and 30% Reorganize will be the option (Microsoft Recommends). So I had an index whose avg_fragmentation_in_percent was 20.19% and avg_fragment_size_in_pages was 1. so I reorganized the index and checked again the fragmentation level of the index. none of the values has been changed...so I used REBUILD option and checked the fragmentation status. both values have been changed drastically. The avg_fragmentation_in_percent came to 0 and avg_fragment_size_in_pages became 35.
I have doubt. The sql server edition I am using is Standard. The Indexes are created with ONLINE = OFF earlier. Since the REORGANIZE works only when option ONLINE = ON, I suspect that would be reason for REORGANIZE not being worked.
Please correct me If I am wrong.
March 13, 2009 at 3:56 pm
One more thing I missed...if the index page size is too small, even if we alter the index with REBUILD or REORGANIZE, it will neglected, right?
if then, how big the size of index data pages has to be for defragmenting?
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply