Rebuild Indexes in a SQL Azure Environment

As indexes grow they are not stored contiguously on disk and the database becomes fragmented. This results in degraded performance as more disk reads are required to load an index into memory.

It is best practice to regularly rebuild indexes. This can have a significant performance benefit.

Azure does not support rebuilding indexes using SQL Server Management Studio. It also does not support maintenance plans and does not use a SQL Server Agent. Therefore, a manual approach to rebuilding indexes is required.

The following SQL query returns a list of the indexes in the database, with the most fragmented first.

SELECT
OBJECT_SCHEMA_NAME(ips.OBJECT_ID) 'Schema',
OBJECT_NAME(ips.OBJECT_ID) 'Table',
i.NAME,
ips.index_id,
index_type_desc,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent,
page_count
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN
sys.indexes i
ON (ips.object_id = i.object_id)
AND
(
ips.index_id = i.index_id
)
ORDER BY
avg_fragmentation_in_percent DESC

A sample output of this query is shown below:

The guidance from Microsoft is that indexes be rebuilt if the fragmentation is over 30% and reorganized if the fragmentation is between 5 and 30%. Reorganizing an index is a faster, lightweight version of rebuilding and the indexes remain online.

If you want to rebuild an index the syntax is below:

ALTER INDEX IDX_Processes5 ON Processes REBUILD WITH (ONLINE = ON)

If the index can be unavailable for a short time you can speed up rebuilding time by specifying:

(ONLINE = OFF)

The syntax to reorganize an index is:

ALTER INDEX IDX_Processes5 ON Processes REORGANIZE

Manually rebuilding indexes can be time-consuming and error prone. A Microsoft Engineer has provided a stored procedure that builds a list of indexes in the database and rebuilds or reorganizes them as appropriate.

The code can be found here: https://raw.githubusercontent.com/yochananrachamim/AzureSQL/master/AzureSQLMaintenance.txt

After you create the stored procedure, run as follows:

EXEC AzureSQLMaintenance 'index'

We recommend you inspect and change the code as required. Run the procedure when the database is not busy. The procedure is processor and I/O intensive.

Schedule index rebuilding regularly - daily or weekly, depending on how quickly indexes are becoming fragmented.

Microsoft supplies Azure Automation to allow the scheduling of stored procedure calls.

For more information, see Managing Azure SQL databases using Azure Automation.