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

Jaymin Soni

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

Leave a Comment