Sql-server – Creating indexes with t-sql scrips vs rebuild indexes in maintenance plan

bulk-insertperformancesql serversql-server-2008

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.

Best Answer

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.

Related Question