Rajendra Gupta
Azure SQL Database properties

Azure Automation: Automate Azure SQL Database indexes and statistics maintenance

April 27, 2021 by

Azure SQL Database is a PaaS solution for migrating your on-premises databases to the Azure cloud infrastructure. It is a managed service and Azure manages the infrastructure, database availability, backup restore, and compute resources.

Database maintenance is a regular task for database administrators for optimized performance for your application queries. In an on-premise SQL Server, usually, we configure SQL Server Agent jobs with the custom T-SQL scripts or use database maintenance plans for regular performing index maintenance based on a defined threshold.

  • Do we need to do index maintenance on Azure SQL Databases as well?
  • How can we do index maintenance on Azure databases?

Yes, it is a misconception that we do not need to perform database maintenance on Azure databases. Uses are responsible for index and statistics maintenance on these databases other your performance might degrade over time. However, by default, it has the following configurations in regards to statistics.

  • Auto Create Statistics: True
  • Auto Create Incremental Statistics: False
  • Auto Update Statistics: True
  • Auto Update Incremental Statistics: True

Azure SQL Database properties

Azure Database does not have any SQL Server agent to run the scripts. Therefore, we do have a question here:

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

In this article, we use azure automation accounts and runbooks for scheduling maintenance scripts. Refer to the following articles to become familiar with azure automation.

Implement database maintenance for Azure SQL Database

We use the following steps for index and statistics maintenance for the Azure database.

AzureSQLMaintenance stored procedure

We can write maintenance scripts or performing index maintenance in Azure SQL. I would recommend using AzureSQLMaintenance stored procedure. It is a custom stored procedure developed by Microsoft’s Yochanan Rachamim. It is suitable for Azure SQL and compatible with its supported features.

To use this stored procedure, download it from GitHub, execute it on your SQL database.

AzureSQLMaintenance stored procedure

You can run Execute AzureSQLMaintenance to get a brief overview of its parameters, their supported values.

  • @Operation: Its supported values are index, statistics or all.
  • @Mode: Smart(default) or dummy
    • smart: It checks only modified statistics and choose index maintenance by % of fragmentation
    • dummy: It checks all statistics or indexes
  • @logtotable:
    • 1 to log output in a [AzureSQLMaintenanceLog] table (auto-created).
    • 0: disable logging

Import sqlserver module for Azure automation

In this article, we use the cmdlet from the sqlserver PowerShell module. By default, this module is not installed for Azure runbooks. In the azure automation account, navigate to Modules -> Browse gallery.

Browse gallery

Search for sqlserver modules and import the following modules created by matteott_msft.

sqlserver modules

It shows import succeeded in the notification however if you check in the modules, it still showing as Importing.

Import status

It takes a few minutes to import the module completely.

Module stats

Create Azure SQL Database and automation credential

We need to create credentials in Azure SQL DB and map it with the automation accounts. Navigate to the credentials option in the Azure automation dashboard and click on Add a credential.

automation credential

In the new credential window, enter the following details:

  • Azure automation credential name
  • Description
  • User name and password: Enter the user name and password of your choice. We need to create the same login and user in the Azure SQL database

New automation credential

It creates credentials in your automation account, as shown below.

View credentials

Now, connect to Azure SQL DB using SQL Server Management Studio and create the login that we specified in the automation credential using the below script.

CREATE LOGIN script

Now, switch database context to your Azure SQL database and create a user and assign db_owner permissions.

Give DB_OWNER permissions

Create the variables to use in the runbooks

In the Azure runbook, we can define the variables inside the PowerShell script, or you can define variables in the azure automation account. If you create variables in an automation account, you can refer to those variables in different scripts.

For example, in the below screenshot, we define two variables.

  • sqlserver: It stores the FQDN of my azure SQL Server, i.e. azuredemoinstance.database.windows.net
  • database: Azure SQL DB in which we want to do index and statistics maintenance

To create the variables, navigate to the azure automation account and click on Variables -> New Variable.

Create the variables to use in the runbooks

Create runbook for index maintenance in Azure SQL database

In this section, we create a runbook using azure automation for performing index and statistics maintenance. It executes the AzureSQLMaintenance stored procedure that we created earlier in this article.

In the below, the first part of the script, we do the following actions.

  • Get the azure credentials details using the cmdlet Get-AutomationPSCredential and stores them into the variable $azureSQLCred variable
  • It fetches the information from variable sqlserver and stores it into another variable $SQLServerName
  • Similarly, it fetches the azure SQL database name from the database variable and stores into $database. It uses the cmdlet Get-AutomationVariable for fetching the information

Create runbook for index maintenance

Before we proceed further, let’s run this notebook to check whether the variable information is accurate. Click on Test Pane, followed by Start.

As shown below, it writes the Azure SQL server, database names and credentials information.

Test runbook

Now, we connect to the Azure SQL database using the Invoke-Sqlcmd cmdlet. We pass the following arguments in this cmdlet.

  • Credential: We have credentials stored in the $azureSQLCred variable; therefore, specify the variable name here
  • Database: Enter the variable name in which we stored the azure SQL DB name
  • Query: In this parameter, we specify the index and statistics maintenance stored procedure executes the query
  • We also specify the connection and query timeouts in the PowerShell script

Invoke-Sqlcmd cmdlet

Now, perform a test run of the azure runbook. As shown below, it returns the output as below:

  • Displays the total number of indexes, their average fragmentation and number of fragmented indexes
  • It prints statistics information such as total modification and modified statistics
  • It prints the alter index statements that stored procedures execute for index reorg or rebuild

print statistics information

You can scroll down and view the updated statistics script as well for Azure SQL DB.

update statistics script

In the stored procedure, we specified the parameter @LogToTable value 1. Therefore, it captures queries, status, start time and end time in the [AzureSQLMaintenanceLog] table. You can query this table and view the output, as shown below.

parameter @LogToTable

Publish the azure automation runbook.

Publish runbook

Once we have tested and published the runbook, we can link to an existing schedule or create a new schedule. Click on the Link to schedule.

Click on Link to schedule

In the below screenshot, we created a schedule to execute the runbook every Sunday at 12 AM IST.

View linked schedule

Conclusion

In this article, we automated index and statistics maintenance for the Azure SQL database using Azure automation runbooks. You should do regular database maintenance for the optimal performance of your indexes.

Table of contents

Azure Automation: Export Azure SQL Database to Blob Storage in a BACPAC file
Azure Automation: Create database copies of Azure SQL Database
Azure Automation: Automate Azure SQL Database indexes and statistics maintenance
Azure Automation: Automate Pause and Resume of Azure Analysis Services
Azure Automation: Automate data loading from email attachments using Azure Logic Apps
Azure Automation: Building approval-based automated workflows using Azure Logic Apps
Azure Automation: Auto-scaling Azure SQL database with Azure Logic Apps
Azure Automation: Analyzing Twitter sentiments using Azure Logic Apps
Azure Automation: Use Azure Logic Apps to import data into Azure SQL Database from Azure Blob Storage
Azure Automation: Publish LinkedIn posts and tweets automatically using Azure Logic Apps
Azure Automation: Export Azure SQL database data to CSV files using Azure Logic Apps
Azure Automation: Translate documents into different languages using Cognitive Services with Azure Logic Apps
Azure Automation: Azure Logic Apps for face recognition and insert its data into Azure SQL Database

Rajendra Gupta
Azure, SQL Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

1,245 Views