How to perform index maintenance on Azure SQL Database

It’s been a long time since I wrote a blog post and I finally found some time 😀 I’ve played a lot recently with the new Azure SQL Database V12 version. It’s really cool because it has a lot of improvements if you compare it to v11. With the V12 version, you have almost the same functionalities like an on premise database and it consists also new functionalities like row-level security, dynamic data masking and the query store. Just like an on premise database, you also have to maintain your indexes and statistics because indexes can also get fragmented on an Azure SQL Database. With an on premise server or a VM in Azure, most DBA’s schedule an index maintenance job under the SQL Server Agent. However, with Azure SQL Database, you have to think inside the database, which means you don’t have any server functionalities, including SQL Server Agent. Luckily, the Azure platform provides the Azure Automation, which can be used as a replacement for the SQL Server Agent. In this blog post, I’m going to explain how you can schedule your index maintenance job or any other job that needs to be scheduled.

Create a new Azure Automation Account

First of all, you have to begin with creating a new Azure Automation Account. Go to the portal and select the Automation tab. Click the Create button to create the new Automation Account. Fill in the name of the account and the region. Choose the region that is the closest to you. r

Create an Azure Automation credential asset

As prerequisite, create an Azure Automation credential asset that contains the username and password for the target Azure SQL DB logical server. Click on the Automation Account that you have just created and select Assets. In this section, select the button “Add Setting” at the bottom.

Select the option “Add Credential”


Select the Credential type “Windows PowerShell Credential” because we’re going to use this credential in the PowerShell Workflow. Give the Credential a name.

Specify the username and the password that you will link to the credential. This will be the user that will connect to your SQL Azure Database.

Click on OK and wait until the credential is created.

Install your maintenance scripts

Make sure that you have installed your maintenance scripts/procedures on all your Azure Databases. In my example, I’ve been using the maintenance scripts of Ola Hallengren. For the index maintenance, I have to install the scripts IndexOptimize.sql and CommandExecute.sql. Make sure you download the latest version because Ola fixed an issue with the index optimize on Azure SQL Database on July 19th 2015. There is a small issue with the scripts. Ola uses cross database stored procedures, which is not supported in Azure DB at the moment. So, the @Database parameter will not work correctly. Please also check the comments of this blog post. You have to implement a workaround in the runbook.

Import the Maintenance Runbook

We have now setup all the prerequisites so we can start with creating a runbook in our Azure Automation account. A runbook is a PowerShell Workflow that needs to be created or imported. You can actually compare it to configuring a job step in the SQL Server Agent job. The runbook contains the SQL Scripts that need to be executed for the index maintenance and will be scheduled later on. Select your Automation Account and go to the runbook tab. Click on the button “Import” at the bottom to upload your PowerShell Workflow. Select your PowerShell script that you have created and upload it.

Here is my script that I have used.

<#
.SYNOPSIS
 Perform index maintenance

.DESCRIPTION
 This runbook provides an example of how Azure Automation can be used to accomplish common SQL Agent tasks in the cloud. 
 As prerequisite, please create an Azure Automation credential asset that contains the username and password for the target Azure SQL DB logical server ($SqlServerName).
 Make sure that you have installed the scripts IndexOptimize.sql and CommandExecute.sql of Ola Hallengren (https://ola.hallengren.com/downloads.html)
 The check for the MAXDOP value in the IndexOptimize.sql script is using sys.dm_os_sys_info, which is currently not supported
 So be sure you disable that check otherwise it will return an error.
 
.EXAMPLE
 SQLServerIndexMaintenance
 
.NOTES
 AUTHOR: Pieter Vanhove
 LAST EDIT: October 20, 2015 
#>

workflow SQLServerIndexMaintenance
{
 param
 (
 # Fully-qualified name of the Azure DB server 
 [parameter(Mandatory=$true)] 
 [string] $SqlServerName,
 
 # Credentials for $SqlServerName stored as an Azure Automation credential asset
 # When using in the Azure Automation UI, please enter the name of the credential asset for the "Credential" parameter
 [parameter(Mandatory=$true)] 
 [PSCredential] $Credential
 )
 
 inlinescript{
 
 # Setup credentials 
 $ServerName = $Using:SqlServerName
 $UserId = $Using:Credential.UserName
 $Password = ($Using:Credential).GetNetworkCredential().Password
 
 
 # Create connection to Master DB
 $MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
 $MasterDatabaseConnection.ConnectionString = "Server = $ServerName; Database = Master; User ID = $UserId; Password = $Password;"
 $MasterDatabaseConnection.Open();
 
 # Create command to query the current size of active databases in $ServerName
 $MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
 $MasterDatabaseCommand.Connection = $MasterDatabaseConnection
 $MasterDatabaseCommand.CommandText = 
 "
 select name from sys.databases
 where state_desc='ONLINE'
 
 "
 # Execute reader and return tuples of results <database_name, SizeMB>
 $MasterDbResult = $MasterDatabaseCommand.ExecuteReader()
 
 # Proceed if there is at least one database
 if ($MasterDbResult.HasRows)
 {
 # Create connection for each individual database
 $DatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
 $DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
 
 # Iterate through each database under $ServerName
 while($MasterDbResult.Read())
 {
 $DbName = $MasterDbResult[0]
 
 
 # Apply conditions for user databases (i.e., not master DB)
 if($DbName -ne "Master")
 {
 # Setup connection string for $DbName
 $DatabaseConnection.ConnectionString = "Server=$ServerName; Database=$DbName; User ID=$UserId; Password=$Password;"
 $DatabaseConnection.Open();
 
 # Create command for a specific database $DBName
 $DatabaseCommand.Connection = $DatabaseConnection
 
 Write-Output "Perform index maintenance on $DbName"

 # ExampleTable is a place holder for a table that holds a large volume of less important and expendable data
 # that can be truncated to save space on the database.

 $DatabaseCommand.CommandText ="
 EXECUTE dbo.IndexOptimize
 @Databases = '" + $DbName + "',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @UpdateStatistics = 'ALL',
 @OnlyModifiedStatistics = 'Y'
 "
 Write-Output $DatabaseCommand.CommandText
 $NonQueryResult = $DatabaseCommand.ExecuteNonQuery()
 
 # Close connection to $DbName
 $DatabaseConnection.Close()
 
 }
 } 
 
 # Close connection to Master DB
 $MasterDatabaseConnection.Close() 
 } 
 }
}

It’s based on the script that I have found on Codeplex. As you will notice, I have specified 2 parameters. $SqlServerName: this is the server name where we want to perform the index maintenance $Credential: This is the username that will be used to connect to the SQL Server. This will be linked to the credential that we have created in step 2 The workflow is first going to connect to the master database to retrieve all the online databases. As mentioned the install maintenance script part, I had to find a workaround because you cannot use cross DB stored procedures on Azure DB, which are used by the Ola’s IndexOptimize script. Once I get the list of all the databases, I connect to each of the DB’s separatly and execute the index optimize. Please not that the @Databases parameter should contain the current DB name. This solution is not (yet) as flexible as Ola’s solution but it’s a good start. Once the import is done, you will notice that the column Authoring still has a “New” Status. The runbook still needs to be published (see next chapter)

Test your Workflow and Publish

Before you can actually start using this Workflow, you have to publish it, however, I recommend to first test this if everything works fine. Once the Runbook has been created, you can click on it and go the Author section


In the “Draft” section you can see the Workflow that you have just imported. Click on the button “Test” at the bottom to test your Runbook. Before the Runbook is actually executed, you have to provide the 2 parameters. In my example, the Credential will be the AzureDBCredential that I have created in step 2. The name of my SQL Server is called pvhv12.database.secure.windows.net. This is the “SQL Server” where my databases are running on.


If all goes well, you should see an empty output pane with a status: COMPLETED
If not everything goes well, you will notice the errors in the output pane. As soon as you have tested the job and everything works fine, you can publish the runbook and start using it.

Schedule the runbook

The last step is to schedule the published runbook. This is actually the same like in the SQL Server Agent. You have to link a schedule to the job or in this case the runbook. In the runbook, click on the section “Schedule” and select “Link to a New Schedule”
Type in the name of your schedule. Again, the same principle as the name of a job schedule of the SQL Server Agent
Secondly, you need to configure the schedule. Currently you can choose between One time, Hourly or Daily. In my example, I want my Index Maintenance on a daily basis starting at 23:00.

In the final step, you have to specify the runbook parameters that will be used when the job is executed. This will be the same values that I have used in the test phase. Once the schedule is created, your index maintenance will be done on a daily basis.

Verify job history

Just like in the SQL Server Agent, you can also verify the job history of a Runbook. You can select “Jobs” in your runbook to verify the job history. When you click on one of the jobs, you can even see the error output, job summary and the parameters that have been used.

Summary

So summarize I will make a comparison between the Azure Automation and the SQL Server Agent Job.

Azure Automation SQL Server Agent Job
Create an Azure Automation Account Create an SQL Server Agent Job
Create a runbook Create a Job Step
Test the runbook Start Job at Step
Publish the runbook Save the job
Schedule the runbook Schedule the SQL Server Agent Job
View jobs of the runbook View History

 

18 comments on “How to perform index maintenance on Azure SQL Database
  1. Great blog! I was looking for a perfect blog about using Azure Automation for Azure SQL Maintenance for some time now :)

    One little addition that I ran into. When using this approach on large databases the script can take some time to execute which will result in a timeout using the example automation script.
    the addition: $TSQLCommand.CommandTimeout=0 will fix this issue

  2. Hi Pieter,

    First of all thanks for this useful article! I’ve deployed it on our development Azure environment. For some reason the job is finished in 1 second, even when I call the Stored Procedure manually from SSMS without any error messages.
    Do you have any suggestions regarding this or am I missing something?

    Thanks in advance,
    Teun

    • Hi Teun,

      Strange… Normally the stored procedure should generate “some” output, certainly when you execute it manually.
      Can you check this?

      Regards
      Pieter

  3. Pingback: ArcticDBA | There is always someone else…

  4. Hi Pieter,

    First of all, thanks for a great blog post. I’m having some issues though; I can’t seem to get the procedures to execute on any other database than MGMTDB (or my equivalent DBADB) where the procedure resides. The code for finding databases only sees the “local” database like this:

    SELECT [name] AS DatabaseName,
    CASE WHEN name IN(‘master’,’msdb’,’model’) THEN ‘S’ ELSE ‘U’ END AS DatabaseType,
    0 AS Selected,
    0 AS Completed
    FROM sys.databases
    WHERE [name] ‘tempdb’
    AND source_database_id IS NULL
    ORDER BY [name] ASC

    DatabaseName DatabaseType Selected Completed
    DBADB U 0 0
    master S 0 0

    where there’s actually several more databases. If I execute the above in the master database, I see all of the databases. I’m probably doing something basic wrong; would you mind pointing out what? :)

    • Hi Alexander,

      You are correct… Apparently I didn’t test this enough… I have the same issue.
      The problem is I’m not able to create the stored procedure in the master database, that’s not allowed.
      So, it looks like the scripts of Ola are not compatible with the Azure SQL Database.
      I’ll check it with him what the options are.

      I’ll keep you posted.
      Thanks for the feedback, much appreciated!

      Pieter

  5. Pieter, I e-mailed Ola and he told me what I had just realised by myself:

    “Since AzureDB does not support cross-database queries, you’ll have to create my procedures in every user database you want to use them in.”

    That kind of makes this way of automating said scripts somewhat untenable as I guess that would mean either one runbook per database or one potentially HUGE runbook to encompass all the databases (that still need to be kept up-to-date with the databases coming and going, which is … kind of a bummer :P) Interesting conundrum which I have yet to find a solution for, I’m afraid.

  6. This seems to work fantastic, but just have a few question you might know.

    1. There seems to be a few indexes that are always fragmented, so I could run this script once and then once again immediately following and a couple of indexes would not be defragmented. Is that a problem?

    2. When I run this script on a not massive database (1.5gb) it takes a few hours to run and the DTU is stuck at ~99% most of the time. It doesn’t appear to affect performance but is a concern.

    • Hi Craig,

      I doubt that the maintenance procedure is working fine… The script of Ola are not working as cross database queries are not allowed in Azure DB.
      This means the scripts of Ola cannot work as he is using this. I’ve updated the blog post and I am currently looking for a correct procedure.
      To answer your questions:

      1. Are you sure that there are enough pages used by the index? Indexes with fewer pages than 1000 are skipped for index maintenance.
      2. Correct, rebuilding indexes can be resource consuming. Same as on a “normal” SQL instance

      Regards
      Pieter

  7. How is it possible to install the scripts IndexOptimize.sql and CommandExecute.sql in Azure SQL? I get everytime the massage […] access denied on master db […].

    • Hi,

      You cannot install stored procedures in the master database on Azure SQL Database.
      You have to install the procedures in every user database.
      Please also check my other blog where I review the index maintenance

      Regards
      Pieter

  8. Two errors when I tried running the script in SQL Server Management Studio 2014

    Msg 40515, Level 15, State 1, Procedure DatabaseBackup, Line 331
    Reference to database and/or server name in ‘master.dbo.xp_instance_regread’ is not supported in this version of SQL Server.
    Msg 40515, Level 15, State 1, Line 4161
    Reference to database and/or server name in ‘msdb.dbo.sysjobs’ is not supported in this version of SQL Server.

    • Hi Guttorm,

      That is possible since Azure SQL Database does not support three-part-naming (DatabaseName.SchemaName.ObjectName).
      Please also review this blog post

      Regards
      Pieter

  9. Pingback: Running maintenance on Azure SQL databases | Dev @ Roadmap

  10. Here is a shorter powershell that I modified for maintaining a single database,
    also note I added $DatabaseCommand.CommandTimeout = 14400; since the above script was timing out after 30 seconds.

    workflow SQLServerIndexMaintenance
    {
    param
    (
    # Fully-qualified name of the Azure DB server
    [parameter(Mandatory=$true)]
    [string] $SqlServerName,

    # DATABASE NAME
    [parameter(Mandatory=$true)]
    [string] $DbName,

    # Credentials for $SqlServerName stored as an Azure Automation credential asset
    # When using in the Azure Automation UI, please enter the name of the credential asset for the “Credential” parameter
    [parameter(Mandatory=$true)]
    [PSCredential] $Credential
    )

    inlinescript{

    # Setup credentials
    $ServerName = $Using:SqlServerName
    $DbName = $Using:DbName
    $UserId = $Using:Credential.UserName
    $Password = ($Using:Credential).GetNetworkCredential().Password

    # Create connection for each individual database
    $DatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
    $DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand

    # Setup connection string for $DbName
    $DatabaseConnection.ConnectionString = “Server=$ServerName; Database=$DbName; User ID=$UserId; Password=$Password;”
    $DatabaseConnection.Open();

    # Create command for a specific database $DBName
    $DatabaseCommand.Connection = $DatabaseConnection

    # Limit maintainance to 4 hours (default is 30 secs)
    $DatabaseCommand.CommandTimeout = 14400;

    Write-Output “Perform index maintenance on $DbName”

    # ExampleTable is a place holder for a table that holds a large volume of less important and expendable data
    # that can be truncated to save space on the database.

    $DatabaseCommand.CommandText =”
    EXECUTE dbo.IndexOptimize
    @Databases = ‘” + $DbName + “‘,
    @FragmentationLow = NULL,
    @FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
    @FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @UpdateStatistics = ‘ALL’,
    @OnlyModifiedStatistics = ‘Y’

    Write-Output $DatabaseCommand.CommandText
    $NonQueryResult = $DatabaseCommand.ExecuteNonQuery()

    # Close connection to $DbName
    $DatabaseConnection.Close()

    }
    }

  11. Hi, Peter. Thank you for the script.

    I’ve made some changes I hope you’ll like:

    – Added try/catch/finally handling in case of errors
    – Added disposal of IDisposable objects.
    – Fetch list of databases into an array to release the connection to the master database as soon as practical
    – Used SQL instead of powershell to filter out unwanted databases by name
    – Added Write-Error statements so that runbook failures will show up in the Azure Diagnostics logs.

    See: https://gist.github.com/silverl/4394241149afdc6510a8a54c70836d79

    Regards,
    Larry

Leave a Reply

Your email address will not be published. Required fields are marked *