Would you disable CLUSTERED index before bulk insert? Disabling only NON-CLUSTERED indexes for load performance.
Would you disable CLUSTERED index before bulk insert? Disabling indexes for load performance.
It is a good practice to disable indexes before mass insert. However, before you disable index, ask yourself a question – Do you really need to disable clustered and all non-clustered indexes?
I recommend do not drop or disable clustered index. By doing so, you are making your table unavailable and in result, you cannot do insertion process.
Let’s say you have disabled clustered index with below command now what?ALTER INDEX I_USERPK ON dbo.USER DISABLE
GO
It worked but with lots of warning. It has also disabled all non-clustered indexes on table as well.
Now if you run insert or select statement on USER table, you will get below error
The query processor is unable to produce a plan because the index 'I_USERPK' on table or view 'USER' is disabled.
Simple you have made your table unavailable.
I would say never disable clustered index on table and it is good practice to disable non-clustered index before insertion.
Below are stored procedure to disable and enable non-clustered indexes on single table or all tables on DB.
Disable non-clustered indexes with stored procedure - with help of cursor example
CREATE PROCEDURE [dbo].[disableAllIndexes]
--ALTER PROCEDURE [dbo].[disableAllIndexes]
@table_name VARCHAR(15) –parameter to pass table name as argument
AS
BEGIN
--START MSSQL Transaction
BEGIN TRANSACTION;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--DECLARE variables here
DECLARE @alter_query NVARCHAR(400)
--DECLARE AND SET COUNTER.
DECLARE @Counter INT
SET @Counter = 0
--TRY block
BEGIN TRY
--Declare Cursor
DECLARE index_cursor_disable CURSOR FOR
SELECT N'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + ' DISABLE'
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.type_desc = 'NONCLUSTERED'
AND I.name IS NOT NULL
AND I.is_disabled = 0
AND T.name LIKE @table_name
-- OPEN cursor
OPEN index_cursor_disable
-- FETCH records into variable to run alter query
FETCH NEXT FROM index_cursor_disable INTO @alter_query
--LOOP UNTIL RECORDS ARE AVAILABLE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Counter = @Counter + 1
PRINT @alter_query
EXECUTE sp_executesql @alter_query
--FETCH NEXT RECORD INTO THE VARIABLES.
FETCH NEXT FROM index_cursor_disable INTO @alter_query
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
--CLOSE THE CURSOR.
CLOSE index_cursor_disable
DEALLOCATE index_cursor_disable
DECLARE @ErrorNumber VARCHAR(50),@ErrorProcedure VARCHAR(50), @ErrorLine VARCHAR(100), @ErrorMessage VARCHAR(400),
@ErrorSeverity VARCHAR(100), @ErrorState VARCHAR(100)
SELECT @ErrorNumber = ERROR_NUMBER();
SELECT @ErrorProcedure = ERROR_PROCEDURE()
SELECT @ErrorLine = ERROR_LINE()
SELECT @ErrorMessage = ERROR_MESSAGE()
SELECT @ErrorSeverity = ERROR_SEVERITY()
SELECT @ErrorState = ERROR_STATE()
PRINT 'ROLLING BACK TRANSACTION'
PRINT 'ErrorProcedure - '+@ErrorProcedure
PRINT 'ErrorNumber - '+@ErrorNumber
PRINT 'ErrorLine - '+@ErrorLine
PRINT 'ErrorMessage - '+@ErrorMessage
PRINT 'ErrorSeverity - '+@ErrorSeverity
PRINT 'ErrorState - '+@ErrorState
ROLLBACK TRANSACTION
END
END CATCH
IF @@TRANCOUNT > 0
BEGIN
--CLOSE THE CURSOR.
CLOSE index_cursor_disable
DEALLOCATE index_cursor_disable
PRINT CAST(@Counter AS VARCHAR) +' Indexes Found'
PRINT 'All above listed NONCLUSTERED INDEXES are disabled'
COMMIT TRANSACTION
END
END
Find and rebuild all NON-CLUSTERED indexes in selected database with stored procedure (cursor example)
CREATE PROCEDURE [dbo].[rebuildAllIndexes]
--ALTER PROCEDURE [dbo].[rebuildAllIndexes]
AS
BEGIN
--START MSSQL Transaction
BEGIN TRANSACTION;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--DECLARE variables here
DECLARE @alter_query NVARCHAR(400)
--TRY block
BEGIN TRY
--Declare Cursor
DECLARE index_cursor_rebuild CURSOR FOR
SELECT 'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + ' REBUILD'
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.type_desc = 'NONCLUSTERED'
AND I.name IS NOT NULL
AND I.is_disabled = 1
--DECLARE AND SET COUNTER.
DECLARE @Counter INT
SET @Counter = 0
-- OPEN cursor
OPEN index_cursor_rebuild
-- FETCH records into variable to run alter query
FETCH NEXT FROM index_cursor_rebuild INTO @alter_query
--LOOP UNTIL RECORDS ARE AVAILABLE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Counter = @Counter + 1
PRINT @alter_query
EXECUTE sp_executesql @alter_query
--FETCH NEXT RECORD INTO THE VARIABLES.
FETCH NEXT FROM index_cursor_rebuild INTO @alter_query
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
--CLOSE THE CURSOR.
CLOSE index_cursor_rebuild
DEALLOCATE index_cursor_rebuild
DECLARE @ErrorNumber VARCHAR(50),@ErrorProcedure VARCHAR(50), @ErrorLine VARCHAR(100), @ErrorMessage VARCHAR(400),
@ErrorSeverity VARCHAR(100), @ErrorState VARCHAR(100)
SELECT @ErrorNumber = ERROR_NUMBER();
SELECT @ErrorProcedure = ERROR_PROCEDURE()
SELECT @ErrorLine = ERROR_LINE()
SELECT @ErrorMessage = ERROR_MESSAGE()
SELECT @ErrorSeverity = ERROR_SEVERITY()
SELECT @ErrorState = ERROR_STATE()
PRINT 'ROLLING BACK TRANSACTION'
PRINT 'ErrorProcedure - '+@ErrorProcedure
PRINT 'ErrorNumber - '+@ErrorNumber
PRINT 'ErrorLine - '+@ErrorLine
PRINT 'ErrorMessage - '+@ErrorMessage
PRINT 'ErrorSeverity - '+@ErrorSeverity
PRINT 'ErrorState - '+@ErrorState
ROLLBACK TRANSACTION
END
END CATCH
IF @@TRANCOUNT > 0
BEGIN
--CLOSE THE CURSOR.
CLOSE index_cursor_rebuild
DEALLOCATE index_cursor_rebuild
PRINT CAST(@Counter AS VARCHAR) +' Indexes Found'
PRINT 'All above listed NONCLUSTERED INDEXES are rebuilded'
COMMIT TRANSACTION
END
END