Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I’m using SQL Server 2008 and I am running several (15) scripts each day to bulk insert to tables. Each script drops the table at the beginning. I create indexes in the scripts. Some of the scripts reference the tables that were created with previous scripts. I want to improve the bulk insert operations but also want to keep the indexes to improve query performances. Does it make sense to create the indexes using maintenance plan rather than creating them in the script? Will it improve the bulk insert performance if I create the indexes in the maintenance plan at the end of all scripts run? Thanks.

share|improve this question
    
From my experience, depending on the scope of the operations being done in the script and the size of the data sets, it may be beneficial to generate one set of indexes for the insert/update process and then drop those and generate another set of indexes for use in querying. I would suggest playing around with different configurations and study the query plans generated in each setup until you find one that maximizes efficiency. –  Davenport Mar 8 '13 at 20:24

1 Answer 1

Removing the impact of updating your non-clustered indexes during the inserts is a great way to improve bulk insert performance and something we do in my own environments. However, I think a better alternative to you than dropping the table and/or indexes, then recreating them post-insert is to instead disable then rebuild the index. This process removes some over head of the index creation, but accomplishes the same goal of getting the indexes out of the way.

The command to do this would be:

  • Disable: ALTER INDEX foo ON bar DISABLE
  • Re-enable/rebuild: ALTER INDEX foo ON bar REBUILD

Depending on how you execute your ETL loads, you can build this as two steps and script out the index disable and rebuild (SQL 2008 syntax):

--Disable Indexes
DECLARE @v_sql nvarchar(max);
SELECT @v_sql = '';
SELECT @v_sql += 'ALTER INDEX '+QUOTE_NAME(name,'[')+' DISABLE;'+char(10)
FROM sys.indexes
WHERE object_id=OBJECT_ID('bar') AND type =2;

EXEC sp_executesql @v_sql;

--Rebuild Indexes
DECLARE @v_sql nvarchar(max);
SELECT @v_sql = '';
SELECT @v_sql += 'ALTER INDEX '+QUOTE_NAME(name,'[')+' REBUILD WITH(ONLINE=ON);'+char(10)
FROM sys.indexes
 WHERE object_id=OBJECT_ID('bar') AND type =2;

EXEC sp_executesql @v_sql;

Note, by declaring type=2, we are only disabling/rebuilding non-clustered indexes. You don't want to disable/rebuild your clustered indexes as part of this process.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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