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

Question

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

Heisenberg 261

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.

Jeffrey Williams 1,896 Reputation points

2021-06-15T20:16:41.207+00:00

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.

Fatih Bati 1

You can use this procedure:



create procedure dbo.sp_idnex_disable_rebuild
(
	@schema_name varchar(255)
  , @table_name	 varchar(255)
  , @operation	 varchar(10)
  , @index_type	 int
)
as
begin

	declare @sql as nvarchar(max) = N'';

	select	@sql = @sql
			+ N'alter index [' + i.[name] + N'] on '
			+ @schema_name + N'.' + @table_name
			+ N' ' + @operation + N';' + char(13) + char(10)
	from	sys.indexes as i
	where	i.type = @index_type
			and object_name(i.object_id) = @table_name
			and object_schema_name(i.object_id) = @schema_name;

	exec(@sql);

end;
go

/*
@operation:
	disable
	rebuild

@index_type:
	1 - clustered
	2 - nonclustered

usage:
	-- to disable all non clustered indexes on schema_name.table_name table
	exec dbo.sp_idnex_disable_rebuild 'schema_name', 'table_name', 'disable', 2

	-- to rebuild all non clustered indexes on schema_name.table_name table
	exec dbo.sp_idnex_disable_rebuild 'schema_name', 'table_name', 'rebuild', 2
	
	-- to disable the clustered index on schema_name.table_name table
	exec dbo.sp_idnex_disable_rebuild 'schema_name', 'table_name', 'disable', 1

	-- to rebuild the clustered index on schema_name.table_name table
	exec dbo.sp_idnex_disable_rebuild 'schema_name', 'table_name', 'rebuild', 1

*/

4 answers

Your answer

Jeffrey Williams 1,896 Reputation points

2021-06-15T20:16:41.207+00:00

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.
Fatih Bati 1 Reputation point

2024-03-18T09:28:01.2133333+00:00

You can use this procedure:

create procedure dbo.sp_idnex_disable_rebuild ( @schema_name varchar(255) , @table_name varchar(255) , @operation varchar(10) , @index_type int ) as begin declare @sql as nvarchar(max) = N''; select @sql = @sql + N'alter index [' + i.[name] + N'] on ' + @schema_name + N'.' + @table_name + N' ' + @operation + N';' + char(13) + char(10) from sys.indexes as i where i.type = @index_type and object_name(i.object_id) = @table_name and object_schema_name(i.object_id) = @schema_name; exec(@sql); end; go /* @operation: disable rebuild @index_type: 1 - clustered 2 - nonclustered usage: -- to disable all non clustered indexes on schema_name.table_name table exec dbo.sp_idnex_disable_rebuild 'schema_name', 'table_name', 'disable', 2 -- to rebuild all non clustered indexes on schema_name.table_name table exec dbo.sp_idnex_disable_rebuild 'schema_name', 'table_name', 'rebuild', 2 -- to disable the clustered index on schema_name.table_name table exec dbo.sp_idnex_disable_rebuild 'schema_name', 'table_name', 'disable', 1 -- to rebuild the clustered index on schema_name.table_name table exec dbo.sp_idnex_disable_rebuild 'schema_name', 'table_name', 'rebuild', 1 */

Answer 1

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.

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

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,

Answer 3

Heisenberg 261

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.

CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2021-06-18T06:30:30.567+00:00

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.

Answer 4

Tom Phillips 17,771

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.

Share via

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

4 answers

Your answer