I have a SQL 2000 sp3a server on Windows 2000 sp4. Running dual proc server with hyper threading enabled, 3gb memory attached to a HP EVA 5000 SAN.
One of the tables is 67gb and contains 140,000,000 rows. Recently someone dropped the clustered indexe so i`m trying to put it back (i've dropped the non clustered indexes as no point leaving them there whilst clustered builds).
The problem i am having is the rebuild is taking forever!! It ran for 23 hours before someone rebooted the server (!). The database is currently recovering from the reboot but i need to work out what is causing the appalling performance so i can get the index rebuilt. There are no reported hardware problems.....
There are multiple file groups involved and i found i was getting an extent allocation rate of 1.5 extents a second and same for deallocation.
Yep spot on, period of time for reporting is up to 6 months.
I agree with everything you say but its the classic third party product scenario where we can not change anything if we want support etc etc.
The 23 hrs + is my real problem though it should not be taking this long to build the index. Ohh well, time to put the data into a new table and go from there (once the database has recovered, its been 4 hrs so far).
If tempdb is on separate disks from the filegroup containing your data, you can try to create the index with SORT_IN_TEMPDB. I had to check if it was valid for clustered indexes, and it looks like it is. Here is the bit from BOL:
Originally Posted by BOL
Specifies that the intermediate sort results used to build the index will be stored in the tempdb database. This option may reduce the time needed to create an index if tempdb is on a different set of disks than the user database, but it increases the amount of disk space used during the index build.
It may not be much, and it may blow your tempdb up in size, but it is certainly worth a shot on a test server.