CODEWITHSUNDEEP
sqlsql-serverazure-sql-database
Shehan Simen
Shehan Simen

Reputation: 1306

Azure SQL rebuild index script fails if many indexes to rebuild

I have a script to rebuild/reorganize indexes as follows in Azure SQL server. Problem is that it causes the application to hang because all the database sessions will be taken from this script. It is calling the rebuild sql statement inside a cursor, so I was expecting that it will rebuild the indexes one after the other. But it seems all the index rebuilds will run parallel, otherwise there is no reason why the database starts to hang and no connection can be made during this process. If there are few indexes to rebuild, then it is fine. The problem starts when there are like 60 indexes to rebuild. Is there a way to force to build the indexes one after the other? (sequentially , not in parallel).

CREATE PROCEDURE [dbo].[RebuildIndexes]
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @objectid int;
    DECLARE @indexid int;
    DECLARE @partitioncount bigint;
    DECLARE @schemaname nvarchar(130);
    DECLARE @objectname nvarchar(130);
    DECLARE @indexname nvarchar(250);
    DECLARE @partitionnum bigint;
    DECLARE @partitions bigint;
    DECLARE @frag float;
    DECLARE @command nvarchar(4000);
    -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
    -- and convert object and index IDs to names.
    SELECT
        object_id AS objectid,
        index_id AS indexid,
        partition_number AS partitionnum,
        avg_fragmentation_in_percent AS frag
    INTO #indexes_to_build
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 and page_count > 200
    ORDER BY avg_fragmentation_in_percent DESC;

    -- Declare the cursor for the list of partitions to be processed.
    DECLARE partitions CURSOR FOR SELECT * FROM #indexes_to_build;

    -- Open the cursor.
    OPEN partitions;

    -- Loop through the partitions.
    WHILE (1=1)
        BEGIN;
            FETCH NEXT
               FROM partitions
               INTO @objectid, @indexid, @partitionnum, @frag;
            IF @@FETCH_STATUS < 0 BREAK;
            SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
            FROM sys.objects AS o
            JOIN sys.schemas as s ON s.schema_id = o.schema_id
            WHERE o.object_id = @objectid;
            SELECT @indexname = QUOTENAME(name)
            FROM sys.indexes
            WHERE  object_id = @objectid AND index_id = @indexid;
            SELECT @partitioncount = count (*)
            FROM sys.partitions
            WHERE object_id = @objectid AND index_id = @indexid;

    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
            IF @frag < 30.0
                SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
            IF @frag >= 30.0
                SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (ONLINE = ON)';
            IF @partitioncount > 1
                SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
            EXEC (@command);
            PRINT N'Executed: ' + @command;
        END;

    -- Close and deallocate the cursor.
    CLOSE partitions;
    DEALLOCATE partitions;

    -- Drop the temporary table.
    DROP TABLE #indexes_to_build;
    COMMIT;
END;

Upvotes: 0

Views: 1271

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89071

Is there a way to force to build the indexes one after the other?

TSQL always executes sequentially. And your ALTER INDEX commands are no exception.

The COMMIT at the end is the problem. That procedure, as written, will only run with IMPLICIT_TRANSACTIONS on. With a COMMIT after the loop, you are accumulating exclusive locks (Sch-M) on the target indexes as you rebuild them. As you work through a large number of indexes, you will block more and more other sessions.

You should not do that. Instead don't use IMPLICIT_TRANSACTIONS or COMMIT after each index rebuild. EG:

    IF @partitioncount > 1
        SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
    EXEC (@command);
    PRINT N'Executed: ' + @command;
    COMMIT;

Upvotes: 1

Alberto Morillo
Alberto Morillo

Reputation: 15648

My suggestion is to use Ola Hallemgren's stored procedure name Index Optimize which you can download from here. Everybody use his scripts for index maintenance tasks.

You can run the stored procedure as shown below:

EXECUTE dbo.IndexOptimize
@Databases = 'mydbnamehere',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

For more information, please visit this Web page.

Upvotes: 0

Related Questions