LLI Wiki — My Digital Knowledge Base

Index and statistics maintenance on Azure SQL elastic pools

Why less is more, resumable online rebuild, statistics updates and scheduling across many databases without saturating the pool.

Index maintenance is a habit inherited from on-premises servers: rebuild everything on Sunday night. On Azure SQL Database, and even more in elastic pools where hundreds of databases share the same CPU, IO and log budget, that habit is expensive. This article explains how I maintain indexes and statistics today: less work, targeted, and scheduled so that the pool never notices.

Why less is often more

My rule: do not rebuild by default. Rebuild only large indexes (several thousand pages), with high fragmentation, that are scanned by real queries. Reorganize or leave the rest alone.

SELECT TOP (50)
    OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
    OBJECT_NAME(ips.object_id)        AS table_name,
    i.name                            AS index_name,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
JOIN sys.indexes AS i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.page_count > 5000
  AND ips.avg_fragmentation_in_percent > 30
  AND i.index_id > 0
ORDER BY ips.page_count DESC;

LIMITED mode reads only the upper levels of the index: it is the cheap way to scan.

Resumable online rebuild

When a rebuild is justified, make it online and resumable. It applies to Azure SQL Database and lets you stop after a time budget and continue the next night, instead of losing hours of work:

ALTER INDEX IX_Orders_Status_Date_Id ON dbo.Orders
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);

-- state of paused or running operations
SELECT name, state_desc, percent_complete FROM sys.index_resumable_operations;

ALTER INDEX IX_Orders_Status_Date_Id ON dbo.Orders PAUSE;
ALTER INDEX IX_Orders_Status_Date_Id ON dbo.Orders RESUME;

Points from the documentation worth remembering: RESUMABLE requires ONLINE; it is not supported for REBUILD ALL, columnstore or filtered indexes; SORT_IN_TEMPDB is not supported; and while an operation is paused both the old and the new index consume space and are updated by DML. Do not leave an operation paused for weeks.

Statistics

Statistics are where the real gain is. Auto-update statistics is on by default, but its threshold can leave large tables stale for a long time. I look at the modification counter and refresh what changed:

SELECT OBJECT_NAME(s.object_id) AS table_name, s.name, sp.rows, sp.modification_counter, sp.last_updated
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
  AND sp.modification_counter > 0.1 * sp.rows
ORDER BY sp.modification_counter DESC;

UPDATE STATISTICS dbo.Orders WITH SAMPLE 20 PERCENT;

Use FULLSCAN only on the few statistics that need accuracy; a default sample is enough for most.

Scheduling across many databases

The classic mistake is to start the job on all databases at once. In a pool, the sum of all rebuilds hits the shared limits. My constraints:

Two ways to run it. Elastic jobs run T-SQL on target groups (a pool, a server) and can limit concurrency with the @max_parallelism parameter of jobs.sp_add_jobstep (maximum number of databases per pool running the step at the same time); schedules are in UTC. Azure Batch (or any scheduler running a script) is my choice when I need logic that T-SQL does not give: a loop over pools, a stop at a given hour, a report mail, retries and logs per database.

flowchart LR
  A["Scheduler : night window"] --> B["List pools and databases"]
  B --> C["For each pool : one database at a time"]
  C --> D["Check candidates : size, fragmentation, stats"]
  D --> E["Resumable online rebuild + update statistics"]
  E --> F{"Window closed ?"}
  F -- "yes" --> G["PAUSE resumable operations"]
  F -- "no" --> C
  G --> H["Report"]

Lessons learned

Mastodon LLIMastodon SocialFosstodon