Faster index creation
-
I have a table that contains about 170 million rows. Monthly I drop all the indexes, load new data (about a million more records), and recreate the indexes. It's getting to where the index creation is just taking WAY too long. Any suggestions on how to cut the time down? I looked up some stuff on SORT_IN_TEMPDB and tried that with no success. tempdb is on another disk, but it still didn't speed it up. Indexes are taking about 1.5 hours a piece to create. There are 12 indexes on the table, so it's getting to be a bit much. PLEASE let me know if you have any tricks.
Thanks,
Chris
-
Try to create more files for the TEMPDB on more disks and use the SORT_IN_TEMPDB option.
Hope this would help
-
You could try not dropping the indexes, loading the data and then see which indexes need to be rebuild and then rebuild only those (there's a useful example on BOL on how to do this in a script - search for DBCC SHOWCONTIG).
Other than this, has anything changed with your disks?
-
maybe it isn't worth dropping the indexes any more and instead do the data load and defrag the indexes after the load. The data load may actually take less than rebuilding the indexes and as the defragmentation of indexes is an online operation so you may be online a lot faster or in fact never offline if you could load the data in multiple steps.
Just a thought.
regards,
Mark Baekdal
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server
-
If you are running Enterprise Edition, you can set up multiple indexes to build at the same time. Depending on your hardware, You could build your clustered index first, then set off one index per CPU (or virtual cpu if hyperthreading is enabled). You may see the index time go up a few minutes, but with the parallelizing, the overall time will be less.
-
Use the index create memory option in the sp_configure
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply