Azure SQL Database - Rebuild Index With Resumable Not Working

  • I'm running an index maintenance routine against a number of Azure SQL databases. I'm using the following query to get a list of indexes to Reorganize or Rebuild in each database:

    Declare @ReorgThreshold int = 5
    Declare @RebuildThreshold int = 30
    Declare @MinPageCount int = 100

    SELECT x.TableName, x.IndexName, x.PercentFragmentation,
            CASE WHEN x.PercentFragmentation < @RebuildThreshold THEN 'Reorganize' ELSE 'Rebuild' END as ActionType
    FROM
        (SELECT OBJECT_NAME(PS.object_id) AS TableName, I.name AS IndexName, IPS.avg_fragmentation_in_percent as PercentFragmentation
         FROM sys.dm_db_partition_stats as PS INNER JOIN
             sys.indexes as I ON PS.object_id = I.object_id AND PS.index_id = I.index_id AND I.type in (1/*Clustered index*/,2/*NonClustered index*/) CROSS APPLY
             sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') as IPS
         WHERE IPS.index_id < 1000 -- no xml, columstore, or spatial indexes
             AND IPS.avg_fragmentation_in_percent > @ReorgThreshold
             AND IPS.page_count >= @MinPageCount) as x

    And then I'm executing ALTER INDEX statements against each of the indexes returned. If the action is "Rebuild", this is the SQL I'm running:

    ALTER INDEX PK_My_Table ON dbo.My_Table 
    REBUILD WITH (ONLINE = ON , MAXDOP = 1, RESUMABLE = ON)

    Which executes successfully but doesn't seem to do anything. If I run the top query again, the index is still listed there with the same level of fragmentation. If I remove "RESUMABLE = ON" from the ALTER INDEX statement, the index actually appears to get rebuilt, and the top query won't return it. 

    I thought perhaps it was just running in the background, but selecting from sys.index_resumable_operations returns nothing. Am I missing something?

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply