Say I have the a data disk that is 50x faster than the LOG and tempdb (measured by Random Write speed) disk. (Don't ask why that's something we'll be fixing if needed)

I have a table that's got 19 million rows with about 30 indexes (so basically non trivial).

How much of a factor would the disk speed of the LOG & tempdb than if I was to have the same disk speeds as the data disk.

Edit: Some more stats.

We have moved the LOG to the RAID data drive as test and unfortunately there was not improvement in the speed of the the REBUILD. It still too 1 hour.

  • tempdb is on it's own Raid10 array
  • LOG is on it's own Raid10 array
  • Data is on it's own Raid10 array

  • RAM > 100 Gb (yes 100Gb) and no limit on SQL using it for data or index creation

Note this is not a transactional system, rather a Data Warehouse.

  • how much RAM do you have in the system? what kind of disks are we talking about; RAID or single disks? – Max Vernon Apr 12 '13 at 2:49
  • @MaxVernon - i've edited the question. – Preet Sangha Apr 12 '13 at 3:11
  • so, you are rebuilding 30 indexes with 19 millions rows in under an hour? – Max Vernon Apr 12 '13 at 3:13
  • Yes about an hour – Preet Sangha Apr 12 '13 at 3:13
  • 1
    As ALTER INDEX REBUILD can be minimally logged with the BULK logged recovery model, can you consider switching models for the sake of a faster rebuild? – KookieMonster Apr 12 '13 at 10:43

Indexes are treated the same as any other data modification i.e. they are written to the log. The performance of your log will directly impact the performance of all writes to your database. If your LOG drive is 50 times slower that your data drive, you have serious performance limitations and should consider rectifying that as soon as possible.

| improve this answer | |
  • Thanks man. Please see my update. We've just repeated our test after moving the log. It still took 1 hour. – Preet Sangha Apr 12 '13 at 1:37
  • move the log file to a dedicated drive, preferably a dedicated RAID 10 array. Move TempDB to an SSD. – Max Vernon Apr 12 '13 at 2:48
  • 1
    19,000,000 X 30 = 570,000,000 index entries. Not knowing the column types, I am guessing there is a lot of string data. Doing all that in one hour is pretty good, in my humble opinion. – datagod Apr 12 '13 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.