February 7, 2018 at 7:54 pm
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