Azure SQL Database可恢复重建索引执行后无效果问题

Hey there, I’ve helped a handful of folks work through exactly this issue—you run a resumable index rebuild, the statement succeeds, but when you check fragmentation again, it’s still the same. Let’s break down the most likely culprits and how to fix them:

1. The Resumable Rebuild Is Still Running (or Paused) #

Unlike a regular index rebuild that runs synchronously, resumable operations execute in the background by default. So even if your ALTER INDEX statement returned success, the actual rebuild might still be in progress, or it could have paused due to resource constraints or space issues.

To check the status, query the sys.index_resumable_operations system view:

SELECT 
    OBJECT_NAME(object_id) AS table_name,
    name AS index_name,
    state_desc,
    percent_complete,
    start_time,
    last_pause_time
FROM sys.index_resumable_operations
WHERE OBJECT_ID = OBJECT_ID('YourSchema.YourTable') 
  AND name = 'YourIndexName';

• If state_desc is RUNNING: Give it more time—large indexes can take hours or even days to rebuild in the background, especially if your Azure SQL instance is under resource pressure.
• If state_desc is PAUSED: Look for reasons like insufficient disk space or DTU/CPU throttling. You can resume the operation with:

  ALTER INDEX YourIndexName ON YourSchema.YourTable RESUME;
  

2. Your Rebuild Statement Is Incomplete #

Resumable index rebuilds require the ONLINE = ON parameter (it’s a mandatory dependency for resumable operations in Azure SQL). Double-check your statement to make sure it includes this:

-- Correct resumable rebuild syntax
ALTER INDEX IX_YourIndex ON dbo.YourTable
REBUILD WITH (
    RESUMABLE = ON,
    ONLINE = ON,
    MAXDOP = 2 -- Adjust based on your instance's capacity
);

If you omitted ONLINE = ON, the statement should have thrown an error—but if somehow it didn’t (unlikely), the rebuild wouldn’t behave as expected.

3. You’re Using Outdated Fragmentation Stats #

The sys.dm_db_index_physical_stats DMV can return cached or sampled data if you use the default LIMITED scan mode. To get accurate, up-to-date fragmentation metrics, use the DETAILED scan mode (note: this is more resource-intensive, so run it during off-peak hours):

SELECT 
    OBJECT_NAME(object_id) AS table_name,
    name AS index_name,
    avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    OBJECT_ID('YourSchema.YourTable'),
    NULL,
    NULL,
    'DETAILED'
);

You can also update statistics for the table first to ensure all metadata is fresh:

UPDATE STATISTICS YourSchema.YourTable WITH FULLSCAN;

4. Resource Throttling Is Slowing (or Halting) the Rebuild #

Azure SQL databases (especially DTU-based tiers) have resource limits. If your instance is hitting DTU, CPU, or memory caps, the resumable rebuild might be throttled to avoid impacting other workloads.

Check resource usage via:

• The Azure Portal: Navigate to your SQL database > Metrics and monitor DTU percentage, CPU, and storage.
• The sys.dm_db_resource_stats DMV:

  SELECT 
      end_time,
      avg_cpu_percent,
      avg_log_write_percent,
      avg_data_io_percent
  FROM sys.dm_db_resource_stats
  ORDER BY end_time DESC;
  

If you see consistent high usage, consider scaling up your tier temporarily to let the rebuild complete, or schedule the rebuild during low-traffic periods.

5. Edge Cases to Rule Out #

• Partitioned Indexes: If your index is partitioned, make sure you’re rebuilding all partitions (use ALTER INDEX ... REBUILD PARTITION = ALL if you didn’t specify individual partitions).
• Heap Tables: Non-clustered indexes on heaps won’t fix heap fragmentation, but the index itself should show reduced fragmentation once rebuilt.
• Disabled Indexes: You can’t rebuild a disabled index—verify the index status with sys.indexes:

  SELECT name, is_disabled FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('YourSchema.YourTable');
  

内容的提问来源于stack exchange，提问作者Brian Campbell