Faster index creation | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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
Why you are droping the index. You can insert data and reindex the table with DBCC REINDEX http://www.sql-server-performance.com/rebuilding_indexes.asp
quote: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.
Is clustered index on identity column?
In which order do you drop and recreate the indexes? —
Frank Kalis
SQL Server MVP
http://www.insidesql.de
]]>