question

Heisenberg avatar image
0 Votes"
Heisenberg asked Cathyji-msft edited

disable-enable indexes vs drop-recreate during bulk insert operation.

hello,
im in a process of developing ssis package to bulk load data into few tables. some of the tables are very large. hence im planning to disable indexes before bulk insert operation and then enabling it again. My question is disbling vs dropping indexes (and enabling / creating after bulk insert) which operation will give me best insert performance? will they both same from performance standpoint?

second question is im not planning on dropping or disabling clustered indexes as i believe it will not give me performance improvement since clustered indexes are same as heap tables without index on it. Let me know if my understanding is correct.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You cannot disable the clustered index - it would need to be dropped and then recreated. If you disable a clustered index - you disable all access to the table. As for which is better - I find disabling the non-clustered indexes to be easier, as they can be enabled in one shot using 'ALTER INDEX ALL ON {table} REBUILD;'. If you drop them - then you have to maintain a separate script with all indexes defined and it is too easy for someone to add a new index without updating that script.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

I go with Jeffery. Disabling is a lot easier, since you can re-enable in a single command. And you don't need to keep script of the indexes around. From a performance point of view, there is no difference.. A disabled index is a sleeping beauty and incurs no overhead.

As for the clustered index, my experience is that it usually better to have it in place when you load the data, but it may depend on whether the data is sorted similar to the clustered index or completely misaligned,

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @SQLServerBro,

My question is disbling vs dropping indexes (and enabling / creating after bulk insert) which operation will give me best insert performance? will they both same from performance standpoint?

Disabling a clustered index has additional effects. The data in the table still exists, but will be inaccessible for anything other than a drop or rebuild operation. All related nonclustered indexes and views are also unavailable. Foreign key constraints that reference the table are disabled. Queries against the table will fail.

Disabling a nonclustered index will deallocate the index pages – the space is freed in the database.

The biggest difference between disabling and dropping a nonclustered index is whether the index definition stays around.

To disable in index, issue an ALTER INDEX command.

 ALTER INDEX IX_IndexName ON Schema.TableName DISABLE;
 GO

If you want to re-enable the index, you need to rebuild it – there is no ALTER INDEX…ENABLE command. The rebuild works like this:

 ALTER INDEX IX_IndexName ON Schema.TableName REBUILD;
 GO

To drop a clustered or nonclustered index, issue a DROP INDEX command.

 DROP INDEX IndexName ON Schema.TableName;
 GO

Refer to the bog What’s Better: Disabling vs. Dropping Indexes?

second question is im not planning on dropping or disabling clustered indexes as i believe it will not give me performance improvement since clustered indexes are same as heap tables without index on it. Let me know if my understanding is correct.

No. Removing indexes prior to large inserts on a table, including when using SQL Bulk Insert, may be a best practice to increase performance.

Refer to the old thread what are sql server index disable advantages?


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Heisenberg avatar image
0 Votes"
Heisenberg answered Cathyji-msft edited

Thank you all for the insight. I'm planning to keep clustered index and disable non-clustered index seems to be the better strategy. If i disable clustered index, i can not run INSERT operation on it , so disabling them is not going to be a viable solution.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SQLServerBro,

Thanks for your reply.
By the way, did the reply(s) could help you? If the response helped, do "Accept Answer". If it is not, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

There are a lot of variables when doing bulk inserts. The bottom line is you will need to test it for your specific situation and see what works best for you.

I have had times where I bulk load 15 tables, and only 3 of them really benefit from dropping/disabling the non-clustered indexes and recreating them and the others are only marginally faster, so I don't bother.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.