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

February 6, 2013 — 13 Comments

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.

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

  1. 

    This is awesome. Thank you! I’m going to build a ScheduledJob in Mobile Azure Services to rebuild my indexes on a schedule. Maintenance-free index health! You rock!

    Liked by 1 person

  2. 

    Should be worth mentioning that REORGANIZE is supported in SQL AZURE. It wasn’t in the past

    Like

  3. 

    And for Sql Server On Premise? good patterns and practices about Index maintenance?

    Like

    • 

      I have a few other posts around Indexes, because they did provoke a few interesting learning moments for me. What I learnt essentially boils down to the following : The best practices that we should have been using On-Premise are absolutely necessary once we start leveraging Platform Services.

      Like

Trackbacks and Pingbacks:

  1. SQL Azure Index Maintenance | Code is for humans - July 2, 2013

    […] I did some tweaking to my ancient indexing script to bring it up to date for SQL Azure.  No idea if it’s perfect but in the least it seems to work fairly well.  Too bad […]

    Like

  2. Steps Towards Optimized Windows Azure SQL Databases | Alexandre Brisebois - November 30, 2013

    […] should also consider rebuilding indexes on a regular basis. Fragmented indexes take up unnecessary space and can be quite slow. Setting up an automated job […]

    Like

  3. Indexes are Crucial on Windows Azure SQL Database! | Alexandre Brisebois - January 26, 2014

    […] in production, I strongly recommend monitoring the index fragmentation and to rebuild them regularly. This will keep the overall performance closer to being […]

    Like

  4. Using PowerShell to Rebuild #Azure SQL Database Indexes « Alexandre Brisebois - August 16, 2014

    […] few months ago I wrote a blog post titled “Don’t Forget About Index Maintenance on Azure SQL Database“. Since then, Microsoft Azure SQL Database has changed a lot. We aren’t as concerned […]

    Like

  5. Rebuilding SQL Database indexes using Azure Automation - Un paseo por las nubes - October 8, 2015

    […] Don’t forget about Index Maintenance on Windows Azure SQL Database […]

    Like

  6. Indexes in SQL Azure | news - October 16, 2015

    […] This guy says go ahead rebuild: https://alexandrebrisebois.wordpress.com/2013/02/06/dont-forget-about-index-maintenance-on-windows-a&#8230; […]

    Like

  7. Monitorización de SQL II | Pensando bajo la lluvia - June 6, 2016

    […] Mantenimiento de indices. Al realizar inserciones y borrados de datos los indices se van degradando (fragmentando). La siguiente consulta permite obtener el grado de fragmentación de los indices de la base de datos. Si este es elevado (por ejemplo, superior al 20%) deberíamos reconstruir el indice (ALTER INDEX {0} ON {1} REBUILD WITH (STATISTICS_NORECOMPUTE = ON, ONLINE=ON). Más información en el enlace Mantenimiento de indices. […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s