September 15, 2007 at 3:14 am
This is not really a problem that I'm having, more a quest for information as to what a rebuild index process is doing in SQL Seerver Manager...
We have a large table, 333m rows and occasionaly we need to do bulk insert (c 20m rows). The table has 4 non-clustered indexes but no clustered indexes. Prior to the insert we disable the indexes (in SSM) to improve the insert speed. After the insert we rebuild the indexes using the 'Rebuild All' option of the Index menu in SSM.
It will take approx 1 day (24hrs) before the 'rebuild' panel will emerge and during this time performance monitor shows a great deal of disk-activity on the drives that contain the Indexes.
The rebuild panel then appears with options for each Index, we select 'OK' and the indexes rebuld, usually within 2 - 3 hours.
I'm really interested to know what is happening in the intermediate period between issuing the command and the panel appearing and why so long?
September 17, 2007 at 3:09 am
my guess is that it calculates fragmentation of the index - this seems to be the only 'variable' property displayed on the dialog. maybe you would consider rebuilding indexes using T-SQL rather?
...and your only reply is slàinte mhath
September 17, 2007 at 3:14 am
thatnks for your help
I thought it might be fragmentation.
I'm looking at the T-SQL option as it runs so much faster, but I wnated to make sure that there wasn't an intermediate step that we were missing.
thanks again
September 19, 2007 at 11:56 am
Yes, always use good 'ol T-SQL to do indexes on large tables. Also you may want to look into the index building options like ONLINE, ALLOW_ROW_LOCKS, and ALLOW_PAGE_LOCKS or you may wind up making the table completely inaccessable for days, depending on the speed of your disk subsystem. Pay close attention to write performance, particularly in the area of physical disk bytes written per second.
Sometimes things like this are better handled in a different filegroup on a different set of physical disks as well.
September 19, 2007 at 1:24 pm
Besides, if you disable indexes prior to loading and then rebuild them, I would suggest to drop them and create anew.
September 20, 2007 at 7:13 am
is it really faster? i did some profiler tracing of a small db that is 99% inserts before and after putting indexes on a table. pretty much no difference at all in speed.
September 20, 2007 at 7:42 am
SSQ_Noob
I'm not sure who this question was directed at ?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply