Through the course of developing ETL packages using SSIS within SQL Server 2012, you may run into the problem that your ETL routines take far longer than expected. While there are many methods of improving ETL times, one method that might prove useful is to disable the indexes being used on the table load.
When you insert data into a table that has an index on it, SQL Server modifies the index after the insert to reflect the new data. Since you are (typically) loading a large amount of data at once, this work does not need to be done every time an insert occurs. You can save time by disabling the index before your Data Flow Task and rebuilding the index afterwards. This method can be especially useful if the table being loaded is large and had multiple non-clustered indexes composed of multiple columns.
However, if you choose to add an index later on to improve performance, you will need to go back and modify either your package or the stored procedure that calls the code to disable these indexes to disable and rebuild the new index you just created. Depending on the architecture of your solution, this may be difficult to achieve. Another consideration is that the DBA implementing the index may be unaware that an SSIS package is disabling indexes, which undermines this tactic.
Luckily, SQL Server 2012 addresses this possibility by allowing you to disable all indexes on a table. The syntax is as follows:
ALTER INDEX ALL ON dbo.[Table Name] DISABLE ALTER INDEX ALL ON dbo.[Table Name] REBUILD
Remember that the correct keyword here is “REBUILD” – not “ENABLE.” Using this method, you should be able to improve your ETL performance in a way that is resistant to future changes.