Saturday, May 22, 2010

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 NameTypeIs Disabled?
F4111_PKCLUSTERED1
F4111_10NONCLUSTERED1
F4111_11NONCLUSTERED1
F4111_2NONCLUSTERED1
F4111_3NONCLUSTERED1
F4111_4NONCLUSTERED1
F4111_5NONCLUSTERED1
F4111_6NONCLUSTERED1
F4111_7NONCLUSTERED1
F4111_8NONCLUSTERED1
F4111_9NONCLUSTERED1


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 NameTypeIs Disabled?
F4111_10NONCLUSTERED1
F4111_11NONCLUSTERED1
F4111_2NONCLUSTERED1
F4111_3NONCLUSTERED1
F4111_4NONCLUSTERED1
F4111_5NONCLUSTERED1
F4111_6NONCLUSTERED1
F4111_7NONCLUSTERED1
F4111_8NONCLUSTERED1
F4111_9NONCLUSTERED1



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.
Subscribe to Jeff Stevenson's Technology Blog - Get an email when new posts appear

5 comments:

Ranjit said...

Jeff, this is nice information, thanks!

In a data load job if you depend upon disabling clustered index to disable all indexes, and re-enabling it, there may be a large cost involved. Keeping old fashioned individual index names may be faster, or we can use a dynamic sql to disable all non-clustered indexes.

A quick question - with ALL syntax is there an option to specify the type of index, something like "ALTER NON CLUSTERED INDEX ALL"

Regards!
Ranjit

jxs2151 said...

Ranjit,

Thanks for taking the time to comment.

I have a couple of scripts to disable and re-enable non-clustered indexes and find them long, complicated and generally unwieldy. Additionally, the typical circumstances when indexes are to be disabled for import for an EnterpriseOne system (most of my work) is prior to go-live during data conversion when the cost of re-enabling a clustered index does not have an impact.

Your point is taken - there is an impact. I mention this toward the end. Perhaps someday I will quantify this cost since we do deal with some very large tables in E1.

With ALTER INDEX ALL you cannot specify an index type. You can rebuild all indexes (clustered & non-clustered) or you can rebuild a single index (ALTER INDEX F4111_PK ON ARCDTA.dbo.F4111 REBUILD or ALTER INDEX F4111_2 ON ARCDTA.dbo.F4111 REBUILD once the clustered index is enabled) but you cannot specify only non-clustered index type.

Again, thank you for your comments.

Jeff Stevenson said...

I did some testing this weekend on a table with about 5 million records and found that rebuilding all indexes (clustered and non-clustered) took only slightly longer than rebuilding only the non-clustered indexes.

Rebuilding all indexes: 20:00
Rebuilding only non-clustered: 17:00

Given that it takes some time to create the SQL statements to disable and rebuild only non-clustered indexes I'll stand by my method of disabling the clustered index to disable all indexes prior to imports or updates.

Jack Vamvas said...

Nice article - to dynamically generate the Index disable and Index Rebuild check: http://www.sqlserver-dba.com/2012/01/sql-server-disable-indexes-and-rebuild-indexes-dynamically.html

Jeff Stevenson said...

Jack, there are indeed a ton of scripts out there to generate the scripts to disable indexes. The point of the whole article is that you can disable all clustered indexes (of which there may be many) on a table by disabling the clustered index utilizing a simple, one line script.

I appreciate your input but will stick with the clustered index disable method to disable non-clustered indexes. Again, thanks.