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.
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!
LikeLiked by 1 person
You might also want to loo at Azure Automation, they have a runbook to do just that https://gallery.technet.microsoft.com/scriptcenter/Indexes-tables-in-an-Azure-73a2a8ea and I have a newer post that have a PowerShell script to do this as well https://alexandrebrisebois.wordpress.com/2014/08/16/using-powershell-to-rebuild-azure-sql-database-indexes/
LikeLike
Should be worth mentioning that REORGANIZE is supported in SQL AZURE. It wasn’t in the past
LikeLike
That’s a good point. May be good to keep in mind.
LikeLike
And for Sql Server On Premise? good patterns and practices about Index maintenance?
LikeLike
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.
LikeLike