Don’t Forget About Index Maintenance on Windows Azure SQL Database

Granted that Azure SQL Database takes care of a lot of maintenance tasks usually done by DBAs, you should definitely keep an eye on those Indexes. Indexes get fragmented and eat up lots of precious space. Furthermore, fragmented indexes make for slower queries.

I’m currently using the following technique to keep my Indexes healthy so that I may offer a constant end user experience. Furthermore, giving my Indexes regular doses of love prevents my database from growing too fast, which results in drastic cost savings. Throughout my test period, I noticed that fragmented indexes for large amounts of records would eat up close to 4gb of extra data. Once the Indexes are rebuilt, that extra space is recuperated and I am able to push back the dreaded moment where I have to pay for more SQL Database storage.

To automate this maintenance process we need the ability to find problematic Indexes. The following Stored Procedure will return a list of Index names and their average fragmentation as a percent values. Be sure to replace the DatabaseName with the name of the database you are working with.

CREATE PROCEDURE [dbo].[GetIndexFragmentation]
    @tableName nvarchar(max)
AS
SELECT name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (
       DB_ID(N'DatabaseName')
     , OBJECT_ID(@tableName)
     , NULL
     , NULL
     , NULL) AS a
JOIN sys.indexes AS b 
ON a.object_id = b.object_id AND a.index_id = b.index_id;

An MSDN article tells us that between 5% to 30% of average fragmentation we should issue a ALTER INDEX REORGANIZE statement. Unfortunately, this statement is not supported on Azure SQL Database so I recommend going directly to an

ALTER INDEX REBUILD WITH (ONLINE = ON) statement.

Use the following c# code to execute a REBUILD statement per Index. It uses the ReliableModel described in a previous post.

Calling the RebuildFragmentedIndexes method with a valid table name and connection string, will find the problematic Indexes that are related to the specified table and rebuild them.

string fragmentationSP = "EXEC [dbo].[GetIndexFragmentation] @tableName = {0}";
string rebuildTemplate = "ALTER INDEX {0} ON {1} REBUILD WITH " +
                         "(STATISTICS_NORECOMPUTE = ON, ONLINE=ON);";
private void RebuildFragmentedIndexes(string tableName, string connectionString)
{
    var result = ReliableModel.Query(u =>
        {
            return u.Context
                    .Database
                    .SqlQuery<IndexFragmentation>(fragmentationSP, tableName)
                    .ToList();
        }, () => new StockUnitOfWork(connectionString), 10);

    result.Where(r => r.avg_fragmentation_in_percent > 10)
            .ToList()
            .ForEach(r =>
    {
        var query = string.Format(rebuildTemplate,
                            r.name,
                            tableName);

        ReliableModel.DoWithoutTransaction(u =>
            {
                u.Context
                    .Database
                    .ExecuteSqlCommand(query);
            }, () => new StockUnitOfWork(connectionString), 10);
    });
}

internal class IndexFragmentation
{
    internal string name { get; set; }
    internal double avg_fragmentation_in_percent { get; set; }
}

Entity Framework and TransactionScope transactions are limited to 10 minutes. This configuration is in the machine.config and cannot be changed in Azure. The REBUILD statement is executed without a transaction because the execution time of this statement can easily exceed 10 minutes. Be sure that your connection and command timeouts are set to acceptable amounts of time.