Automate Your Rebuild Indexes And Update Statistics Activity

Are you working with Azure SQL Database?

Azure SQL Database is a fully managed platform as a service (PaaS) provided by Microsoft. In Azure most of the database maintenance activities including Updating, Patching, Making Backups, Monitoring etc are handled by Azure itself without any human input.

But there is a limitation for Azure Databases, It doesn’t provide automatic rebuild indexes facility. Also, there are no SQL Server agents available in Azure Database to execute these scripts.

As a result, Many DBA’s are encountering several challenges in day today life while maintaining database servers.

So now, the question is

How can we automate the Azure SQL Database index and statistics maintenance?

The answer is by using Elastic Job Agent by Azure.

Perform Index and Statistics Maintenance of Azure SQL Databases using an Elastic Job Agent.

Step 1: Before we create the Elastic Job Agent, We need to create a new database in the server in which we have to perform the maintenance activity.

To create a new SQL Database, open SQL Server management studio and connect to your SQL Server. Refer to the following image:

Connect to your SQL Server

Once you are connected to your database server, execute the following script to create a new database called “Jobs”.

Script :> Create database Jobs;

Step 2 : After the database is successfully created, Let us now configure our Elastic Job Agent. To do so, Go to the “All resources” section in Azure Portal, Search for “Elastic Job Agents” Or search directly from the search bar for the service.

Search Elastic Job Agent

After opening the Elastic Job Agents service, Click on (+Create) icon to create a new agent.

Create New Job Agent

Give a suitable name for your Elastic Job Agent, And Select the Database which we have created previously in SQL Server named “Jobs”

Review and Create Your Agent

Click on Next : Create and Review button and your Elastic Job Agent will be created under the name SKSElasticJobAgent.

Once our agent is configured successfully, we will now

A) Create database scoped credentials on the Elastic Agent Database

B) Define the Target Group and its Members

C) Create required logins on the Master database and Target Database

D) Create an Index and Statistics Maintenance Jobs

Step A) Create database scoped credentials

The database scoped credentials are used to connect to the target database. This credential must be created on the agent database. The following query creates a credential named JobExecuter. It is used to connect to the target database and execute the maintenance scripts. Execute the following T-SQL

This script will be executed on the agent database ‘Jobs’

CREATE MASTER KEY ENCRYPTION BY PASSWORD=’DemoServer123';
CREATE DATABASE SCOPED CREDENTIAL JobExecuter WITH IDENTITY = ‘JobUser’,
SECRET = ‘DemoServer123’;
GO

Step B) Define the target group and its member

Once our credentials are defined, we will create a target group. To do that, execute the following script on the agent database i.e Jobs

This script will be executed on the agent database ‘Jobs’

EXEC jobs.sp_add_target_group ‘ProductionTargetServerGroup’
GO

The above script creates a target group named ProductionTargetServerGroup.

Once the target group is created, execute the following script to add the server as a member of the target group. Execute following T-SQL script on agent database.

This script will be executed on the agent database ‘Jobs’

EXEC jobs.sp_add_target_group_member
‘ProductionTargetServerGroup’,
@target_type = N’SqlDatabase’,
@server_name=’a*******.database.windows.net’,
@database_name =N’Company’ -- Database name for which we will rebuild indexes
GO

Here Target Group is like one parent node(Server) under which we can add multiple Target Members (Databases).

Once target group and members have been created, we will create required logins on master and target database.

Step C) Create required logins on the master database and target database

Now, we will create a SQL login on the master database. The login name and password must be the same that we used as an identity to create a database scoped credential. Execute the following T-SQL script on the master database (system database).

This script will be executed on master ‘System database’ database

CREATE LOGIN JobUser WITH PASSWORD = ‘DemoServer123’;

The above script creates a user named JobUser. Next, we will create a user on the target database. Make sure that the user must have appropriate permissions on the target database. Here I am granting db_owner permission to make sure that the SQL job executes successfully. Execute the following script:

This script will be executed on target database ‘Company’

Create user JobUser from login SQLJobUser
ALTER ROLE db_owner
ADD MEMBER [JobUser] ;
GO

The above script creates a user named JobUser, and the db_owner permission is also granted to the user.

Step D) Create SQL Job for index maintenance of Azure SQL Database

The script to create the SQL Job must be executed on the agent database. The following code creates a SQL Job named “Index Maintenance”. Execute the following script.

This script will be executed on the agent database ‘Jobs’

EXEC jobs.sp_add_job @job_name=’Index Maintenance’, @description=’This Job performs index maintenance on every Saturday at 12:00 AM’

I have created a stored procedure named sp_db_maintenance on the agent database. If the index fragmentation percentage is less than 30%, then it reorganizes the index, and index fragmentation is higher than 30%, than it rebuilds the entire index. Following is the code:

Execute this create procedure script on the databases you want to run your rebuild indexes activity for.

/*This script will be executed on(company)database*/
Create procedure sp_index_maintenance
As
begin
DECLARE @DBName varchar(500)
declare @SQLCmd nvarchar(max)
declare @FregmentedIndexes int
declare @i int=0
declare @TableName varchar(500)
declare @indexName varchar(500)
declare @SchemaName varchar(500)
declare @FregmentationPercent float
declare @RebuildCommand nvarchar(max)
set @DBName= ‘Company’ — Database name on which you have to rebuild indexes
if exists (select name from tempdb.sys.tables where name like ‘%#FregmentedIndexes%’)
drop table #FregmentedIndexes
create table #FregmentedIndexes
(
ID int identity (1,1),
TableName varchar(500),
indexName varchar(500),
SchemaName varchar(500),
Fregmentation_Percentage float
)
truncate table #FregmentedIndexes
set @SQLCmd=’SELECT distinct b.name,c.name,d.name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(‘’’ +@DBName +’’’), null, null, null, null) a
inner join [‘+@DBName+’].sys.tables b on a.object_id=b.object_id
inner join [‘+@DBName+’].sys.indexes c on a.object_id=c.object_id
inner join [‘+@DBName+’].sys.schemas d on b.schema_id=d.schema_id
Where
b.schema_id>1'
Print @SQLCmd
insert into #FregmentedIndexes (TableName,indexName,SchemaName,Fregmentation_Percentage)
exec sp_executesql @SQLCmd
set @FregmentedIndexes=(select count(1) from #FregmentedIndexes)
while @i<@FregmentedIndexes
begin
select top 1 @TableName = TableName, @SchemaName=SchemaName, @indexName = indexName, @FregmentationPercent = Fregmentation_Percentage from #FregmentedIndexes
if @FregmentationPercent >30
Begin
set @RebuildCommand =’Alter index [‘+@indexName + ‘] on [‘+ @SchemaName+ ‘].[‘+@TableName+’] Rebuild’
exec (@RebuildCommand)
End
Else if @FregmentationPercent < 30
Begin
set @RebuildCommand =’Alter index [‘+@indexName + ‘] on [‘+ @SchemaName+ ‘].[‘+@TableName+’] REORGANIZE’
exec (@RebuildCommand)
set @UpdateStatistics= ‘UPDATE STATISTICS [‘+@indexName + ‘] on [‘+ @SchemaName+ ‘].[‘+@TableName+’]’
exec (@UpdateStatistics)
End
set @i=@i+1
delete from #FregmentedIndexes where TableName=@TableName and indexName=@indexName
End
End

Now, we will create a job step to execute the stored procedure across all the servers within the target group. Execute the following T-SQL script.

This script will be executed on the agent database ‘Jobs’

EXEC jobs.sp_add_jobstep @job_name=’Index Maintenance’,
@command=N’ exec sp_db_maintenance’,
@credential_name=’JobExecuter’,
@target_group_name=’ProductionTargetServerGroup’

The above script creates a job step named Execute Index Maintenance in SQL Job named Index Maintenance job. We want to run this Job on every Saturday at 12:00 AM. To configure the schedule, execute the following T-SQL code.

This script will be executed on the agent database ‘Jobs’

EXEC jobs.sp_update_jo
@job_name=’Index Maintenance’,
@enabled=1,
@schedule_interval_type=’Weeks’,
@schedule_interval_count=1,
@schedule_start_time= N’20220210 12:00';

Test the SQL Job

Once the job is created, you can use [jobs].[sp_start_job] stored procedure. The following script starts the execution of the script.

This script will be executed on the agent database ‘Jobs’

exec [jobs].[sp_start_job] ‘Index Maintenance’

You can see the execution status of the Job from the Overview page of the Elastic job agent. See the following image:

Your Job Has Been Successfully Executed

As you can see that SQL Job is executed successfully.

You can see the execution status of the job by querying the [jobs_internal].[job_executions] and [jobs_internal].[jobs] tables. Following is the query that populates the name of the job, status of the job, start time and end time of the job.

select b.name,a.lifecycle,start_time, end_time from
[jobs_internal].[job_executions] a
inner join
[jobs_internal].[jobs] b on
a.job_id=b.job_id
where b.name=’Index Maintenance’

Following is the output:

Jobs Which Are Succeeded

To check the scheduled jobs

 SELECT * FROM jobs.jobs

Your Scheduled Jobs

To delete the target group

Exec [jobs].sp_delete_target_group @target_group_name = ‘ProdTargetServer’

Go head and create your first Elastic Job to automate your indexes.

Summary

In this article, I have explained how we can use Elastic Job Agent to automate the rebuild indexes and update statistics for your Azure SQL Databases.

If you face any problem while configuring your job, feel free to comment down below. Happy Coding :)