Transact SQL – Disabling Non-Clustered Indexes

 

Background

Reviewing available options for loading data into a couple of reporting tables.

 

SQL Server Engine

The SQL Server Engine is best used as a relational engine and it is best to rely on it to do things in batches rather than record by record.

Record Changes

When records are added or deleted, the corresponding indexes are processed as well.

NonClustered Indexes

During updates, beneficial non-clustered indexes are reviewed and chosen if useful.

They are updated, if their data is one of the ones changed.

Because of potential for changes and the consequent Halloween effect, Spools can be brought in to safeguard, as well.

Cost

In effect, non-clustered indexes can be costly.

 

Code

Back to our current need, we might want to disable non-clustered indexes before loading data and re-enable them once the data has been loaded.

 

Stored Procedure

dbo.sp_IndexMaintenanceEnable


use master
go

if object_id('[dbo].[sp_IndexMaintenanceEnable]') is null
begin

	exec('create procedure [dbo].[sp_IndexMaintenanceEnable] as select 1/0 ')

end
go

alter procedure [dbo].[sp_IndexMaintenanceEnable]
(
	  @schema	  sysname = 'dbo'
	, @object	  sysname = null
	, @enable	  bit = 1
	, @scriptOnly bit = 1
)
as

begin

	set nocount on;
	set XACT_ABORT on;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

	declare @tblIndex TABLE
	( 

		  [id]			int     not null identity(1,1)
		, [schemaName]	sysname not null
		, [objectName]  sysname not null
		, [indexid]		int		not null
		, [indexName]	sysname not null
	)


	declare	@id			 int     
	declare @schemaName  sysname
	declare @objectName  sysname 
	declare @indexid	 int
	declare @indexName	 sysname 

	declare @idMax		int

	declare @FORMAT_SQL_INDEX_DISABLE	varchar(600);
	declare @FORMAT_SQL_INDEX_ENABLE	varchar(600);

	declare @strSQL						varchar(600);

	set @FORMAT_SQL_INDEX_DISABLE = 'ALTER INDEX [%s] on [%s].[%s] DISABLE';

	set @FORMAT_SQL_INDEX_ENABLE = 'ALTER INDEX [%s] on [%s].[%s] REBUILD';

	; with [cteObject]
	(
		  [schemaName]
		, [objectName]
		, [indexid]
		, [indexName]
	)
	as
	(
		select 
				  tblSS.[name]
				, tblSO.[name]
				, tblSI.[index_id]
				, tblSI.[name]

		from   sys.[schemas] tblSS
 
		inner join sys.objects tblSO

				on tblSS.[schema_id] = tblSO.[schema_id]

		inner join sys.indexes tblSI

				on tblSO.[object_id] = tblSI.[object_id]

		where  tblSO.[type] = 'U'

		/* Skip Heaps */	
		and    tblSI.[index_id] != 0

		/* Skip Clustered Indexes */
		and    tblSI.[index_id] != 1

		and   tblSS.[name] = isNull(@schema, tblSS.[name])
		
		and   tblSO.[name] = isNull(@object, tblSO.[name])
			
	)
	insert into	@tblIndex
	(
		  [schemaName]
		, [objectName]
		, [indexid]
		, [indexName]
	)
	select
		  [schemaName]
		, [objectName]
		, [indexid]
		, [indexName]

	from cteObject

	set @idMax = ( select max([id]) from @tblIndex )
	set @id = 1

	while (@id <= @idMax)
	begin

		set @strSQL = null;

		select 
				  @schemaName = tblI.[schemaName]
				, @objectName = tblI.[objectName]
				, @indexid = tblI.[indexid]
				, @indexName = tblI.[indexName]

		from @tblIndex tblI

		where tblI.id = @id
		
		if (@enable = 1)
		begin

			exec master.dbo.xp_sprintf		
					  @strSQL output
					, @FORMAT_SQL_INDEX_ENABLE
					, @indexName
					, @schemaName
					, @objectName

		end
		else if (@enable = 0)
		begin

			exec master.dbo.xp_sprintf		
					  @strSQL output
					, @FORMAT_SQL_INDEX_DISABLE
					, @indexName
					, @schemaName
					, @objectName

		end

		if (@strSQL is not null)
		begin

			print @strSQL

			if (@scriptOnly = 0)
			begin

				exec(@strSQL)

			end

		end

		set @id = @id + 1

	end


end

go



EXEC sys.sp_MS_marksystemobject '[dbo].[sp_IndexMaintenanceEnable]'
go


Try


	declare @schema sysname 
	declare @object sysname
	declare @enable bit = 1


	set @schema = 'dbo'
	set @object = 'contacts'
	set @enable  = 0
	
	exec [Assist].[dbo].[sp_IndexMaintenanceEnable]
		 @schema =  @schema
		 , @object = @object
		 , @enable = @enable



Output

 

Source Control

GitHub

DanielAdeniji/SQLServerIndexManagementEnable
Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s