Many users on Azure SQL Server do not realize they have to do their own maintenance on Indexes. This index will slowly become fragmented and the performance will decrease over time. Azure SQL does not have a Job scheduler (agent) like on premise. In this post I’ll describe how to schedule a maintenance job from an ASP.NET Core application.
Index Fragmentation
The following query will give the current fragmentation on indexes:
SELECT name
,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (null, null, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id;
All indexes with 5% to 30% fragmentation can be reorganized and > 30% should be rebuilt. Reorganizing is a far cheaper operation than rebuilding the index. If you have never rebuilt or reorganized an index in the database, there is probably a lot of fragmentation.
Maintenance Script / Stored procedure
You can create a maintenance script yourself or take a working one like AzureSQLMaintenance script. This script is developed by Yochanan Rachamim – How to maintain azure sql indexes and statistics/. The script is a stored procedure that checks the fragmentation and then rebuild or reorganize indexes that need maintenance.
Migrations
To deploy the script into your database, it can be added with an Enitity Framework Migration. This way you can be sure that when you create a new database, the script is deployed and available to your application. Create a new migration and add the following code to the class:
public partial class SQLMaintenance : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(@"
if object_id('AzureSQLMaintenance') is null
exec('create procedure AzureSQLMaintenance as /*dummy procedure body*/ select 1;')
GO
ALTER Procedure [dbo].[AzureSQLMaintenance]
(... maintenance procedure for above link...)
end
");
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("DROP PROCEDURE [dbo].[AzureSQLMaintenance]");
}
}
Schedule maintenance
For scheduling I’ve created a scheduler based on my blog post Run scheduled background tasks in ASP.NET Core. The scheduler is triggered on a cron expression. When the task is registered as IHostedService in startup (services.AddSingleton();), you have scheduled daily database maintenance at 5 past 2 in the morning.
public ScheduleMaintenanceTask(IServiceScopeFactory serviceScopeFactory, DBContext context) : base(serviceScopeFactory)
{
_context = context;
}
private readonly DBContext _context;
protected override string Schedule => "5 2 * * *"; //Five minutes after two
public override Task ProcessInScope(IServiceProvider serviceProvider)
{
using (var connection = _context.Database.GetDbConnection())
{
await connection.OpenAsync().ConfigureAwait(false);
using (var sqlCommand = connection.CreateCommand())
{
sqlCommand.CommandText = "AzureSQLMaintenance";
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandTimeout = 300000;
sqlCommand.ExecuteNonQuery();
}
}
}
Final thoughts
Scheduling maintenance is actually very simple. However many develpers do forget that it is needed. Because of the very nice advice tooling in Azure SQL it is assumed that Microsoft will take care if it. It whould be nice if the advice tooling would give an indication on when the database needs some maintenance. It looks like Microsoft is working on this for a future version (see comments) of Azure SQL Server.
{ Think Rethink } 