SQL Azure Index Maintenance using Azure Automation

Hi Folks,

Today we are going to see how we can schedule index maintenance job on SQL Azure where we don't have SQL Agent.

Firstly we are going to create the some fragmentation using the below script

use mytestdb
CREATE TABLE process_list
      transaction_id INT IDENTITY(1, 1)
                         PRIMARY KEY ,
      status_flag BIT ,
      last_update DATETIME2 ,
      transaction_type INT ,
      transaction_desc VARCHAR(25)

CREATE INDEX IX_giant_index_process_list
ON dbo.process_list (transaction_id, status_flag);

DECLARE @string_max_length TINYINT = 25;
DECLARE @i int = 0

DECLARE @StringMaxLen int = 25
INSERT  INTO process_list (status_flag, last_update, transaction_type, transaction_desc)
SELECT TOP (100000)
0 ,
len(LEFT (REPLACE(CAST (NEWID () AS NVARCHAR(MAX)),'-','') , ABS (CHECKSUM (NEWID ())) % @StringMaxLen + 1)) % 4 + 1,
    LEFT (REPLACE(CAST (NEWID () AS NVARCHAR(MAX)),'-','') , ABS (CHECKSUM (NEWID ())) % @StringMaxLen + 1)

In my case I selected BASIC tier & when I ran the above query here is how the spike looks like

Before performing updates & deletes
here is how my fragmentation looks like on my table

Now we are going to perform updates &deletes which results in fragmentation.

If you look at the below image you can see now my table is completely fragmented .

Here comes the most awaited part now we will make use of Azure automation
& schedule a job which would help us in overcoming the fragmentation.

1) Provision an Automation Account if you don’t have any, by going to https://portal.azure.com
and select New > Management > Automation Account

2) After creating the Automation Account, open the details and now click on Runbooks > Browse Gallery

3)Type on the search box the word “indexes” and the runbook “Indexes tables in an Azure database if they have a high fragmentation” appears:

4)if you see the above diagram this script is being written by Microsoft hence we can happily import it without any issues. Hence click on the import button & do provide a proper name for the same.

5) After importing the runbook, now we need to create a credential which can be done by making use of below screen shot

6) create An account for the credentials

7)Now we need to publish the run book of what we created select yes as the dialog box pops up

8) Now we are going to schedule a job by clicking on schedule button

9) schedule the job based on the needs as one time or recurring

11) once the job executes as per schedule you should see there is no fragmentation on the tables

12)If by any chance if you encounter any errors we can investigate them by using the errors tab.