Disable Indexes During SQL Import

SQL Server indexes are useful objects, greatly decreasing the time needed to find and return data in database tables.  Unfortunately, the opposite occurs during record inserts to tables with indexes.  When one is inserting a record or two, or even a hundred, the penalty generally isn't large enough to warrant spending time disabling the indexes to improve the insert operation performance.  However, if your organization is going to be inserting a large number of records into an indexed table it becomes worth the time to optimize the performance.

I am not going to spend a lot of time on an index tutorial but here's the basics that we need to know for this article: there are two types of indexes in SQL Server, Clustered and Non-Clustered.   A clustered index has the special property of determining how data is physically stored, meaning that the index is intimately connected with the physical data itself while a non-clustered index is not.  We want to disable non-clustered indexes in the quickest, most efficient manner possible and leave clustered indexes enabled.  Remember this, it will be important later.

In order to speed the import of our large amount of data we want to disable all non-clustered indexes.  The easiest way of doing this is to disable the clustered index since whenever we disable a clustered index, it disables all the non-clustered indexes on the table as well.  An interesting side-effect that saves us the time of disabling each non-clustered index individually but presents us with a serious problem: once a clustered index is disabled, users can not access the underlying table data.

Let me say that again: once a clustered index is disabled, users can not access the underlying table data.  So, we want the ease of disabling all indexes at once but need to mitigate the rather pesky side-effect of losing access to the data that occurs when one disables a clustered index.  The way to do that is to disable the clustered index, then re-enable it, leaving the non-clustered indexes disabled.

Follow this procedure:

1. Disable all indexes on a table by disabling the clustered index
2. Rebuild only the clustered index (since a rebuild is the method for re-enabling an index), leaving the non-clustered indexes disabled
3. Check disabled indexes
4. Check to make sure no clustered indexes are disabled in the database
5. Import the data
6. Rebuild all indexes (since a rebuild is the method for re-enabling an index) on a table

This will disable all indexes, re-enable the clustered index so we can access the data, then re-enable all indexes after the import.  Needless to say, a table with only a clustered index should be left alone.  Lather, rinse, repeat for all tables affected by the import.



Here's some code:

Disable all indexes on a table by disabling the clustered index

--SQL Script begin
 ALTER INDEX clusteredindexname ON databasename.schemaname.tablename DISABLE
--SQL Script end

Example:

--SQL Script begin
ALTER INDEX F4111_PK ON ARCDTA.dbo.F4111 DISABLE
--SQL Script end


If we check now to see which indexes are disabled we will see that all of them, including the clustered index, are in fact, disabled.

--SQL Script begin
SELECT name, type_desc, is_disabled FROM ARCDTA.sys.indexes WHERE name like '%F4111%'
and is_disabled = 1
--SQL Script end

Index Name	Type	Is Disabled?
F4111_PK	CLUSTERED	1
F4111_10	NONCLUSTERED	1
F4111_11	NONCLUSTERED	1
F4111_2	NONCLUSTERED	1
F4111_3	NONCLUSTERED	1
F4111_4	NONCLUSTERED	1
F4111_5	NONCLUSTERED	1
F4111_6	NONCLUSTERED	1
F4111_7	NONCLUSTERED	1
F4111_8	NONCLUSTERED	1
F4111_9	NONCLUSTERED	1

We now need to re-enable access to the F4111 data by re-enabling the clustered index F4111_PK.


Rebuild clustered index

--SQL Script begin
 ALTER INDEX clusteredindexname ON databasename.schemaname.tablename REBUILD
--SQL Script end

Example:

--SQL Script begin
ALTER INDEX F4111_PK ON ARCDTA.dbo.F4111 REBUILD
--SQL Script end

This will rebuild only the clustered index, re-enabling access to the data.


Check disabled indexes

Now, checking the disabled indexes on F4111 shows that only the non-clustered indexes are disabled:

--SQL Script begin
SELECT name, type_desc, is_disabled FROM databasename.sys.indexes WHERE name like '%TABLENAME%'
and is_disabled = 1
--SQL Script end

Example:

--SQL Script begin
SELECT name, type_desc, is_disabled FROM ARCDTA.sys.indexes WHERE name like '%F4111%'
and is_disabled = 1
--SQL Script end

Index Name	Type	Is Disabled?
F4111_10	NONCLUSTERED	1
F4111_11	NONCLUSTERED	1
F4111_2	NONCLUSTERED	1
F4111_3	NONCLUSTERED	1
F4111_4	NONCLUSTERED	1
F4111_5	NONCLUSTERED	1
F4111_6	NONCLUSTERED	1
F4111_7	NONCLUSTERED	1
F4111_8	NONCLUSTERED	1
F4111_9	NONCLUSTERED	1

Note: You can also disable and rebuild an index from SQL Server Management Studio Object Explorer.

Check to make sure no clustered indexes are disabled in the database

Prior to conducting the data import it would behoove us to ensure that no clustered indexes are disabled.

--SQL Script begin
USE databasename
select * from sys.indexes where type_desc = 'CLUSTERED'
and is_disabled = 1
--SQL Script end

Example:

--SQL Script begin
USE ARCDTA
select * from sys.indexes where type_desc = 'CLUSTERED'
and is_disabled = 1
--SQL Script end

The results set should be empty and you can go ahead and perform your import.


Rebuild all indexes

Once the import completes we can rebuild all indexes which will both re-enable the indexes and update them with the new data.

--SQL Script begin
ALTER INDEX ALL ON databasename.schemaname.tablename REBUILD
--SQL Script end

Example:

--SQL Script begin
ALTER INDEX ALL ON ARCDTA.dbo.F4111 REBUILD
--SQL Script end

or rebuild all indexes in the entire database:

--SQL Script begin
USE databasename
EXEC sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD'
--SQL Script end

Example:

--SQL Script begin
USE ARCDTA
EXEC sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD'
--SQL Script end


A quick check of disabled indexes should find none.

--SQL Script begin
SELECT name, type_desc, is_disabled FROM databasename.sys.indexes WHERE name like '%TABLENAME%'
and is_disabled = 1
--SQL Script end

Example:

--SQL Script begin
SELECT name, type_desc, is_disabled FROM ARCDTA.sys.indexes WHERE name like '%F4111%'
and is_disabled = 1
--SQL Script end


Warnings: Keep in mind that during the time your clustered index is disabled users will not be able to access the data so either execute this on a quiesced system or re-enable it immediately after disabling it.  If you have a large number of records already in the table it is probably a good idea to do this during off hours since the index rebuild might take a while.  In addition, during the time when the non-clustered indexes are disabled users will not be utilizing those indexes, probably slowing read times.  Since the non-clustered indexes are not re-enabled until after the import, this impact will last the entire time of the import plus the time it takes to rebuild the indexes, which could be significant if there is a large amount of data in the table(s).

Should you find that the risks noted above are worth it, you will find that the performance of the imports will be much better than with the indexes enabled.