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 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.
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.
Search for sqlserver modules and import the following modules created by matteott_msft.
It shows import succeeded in the notification however if you check in the modules, it still showing as Importing.
It takes a few minutes to import the module completely.
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.
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
It creates credentials in your automation account, as shown below.
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.
|
1 2 3 |
CREATE LOGIN myazureautomation WITH PASSWORD = 'India@123' GO |
Now, switch database context to your Azure SQL database and create a user and assign db_owner permissions.
|
1 2 3 4 5 6 |
CREATE USER myazureautomation FROM LOGIN myazureautomation ALTER ROLE db_owner ADD MEMBER myazureautomation ; GO |
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 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
$azureSQLCred = Get-AutomationPSCredential -Name "myazureautomation" #Enter the name for your server variable $SQLServerName = Get-AutomationVariable -Name "SqlServer" #Enter the name for your database variable $database = Get-AutomationVariable -Name "Database" Write-Output "Azure SQL Database serverFQDN" Write-Output $SQLServerName Write-Output "Azure SQL Database name" Write-Output $database Write-Output "Your Azure SQL credential name for Automation is:" Write-Output $azureSQLCred |
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.
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
$azureSQLCred = Get-AutomationPSCredential -Name "myazureautomation" #Enter the name for your server variable $SQLServerName = Get-AutomationVariable -Name "SqlServer" #Enter the name for your database variable $database = Get-AutomationVariable -Name "Database" Write-Output "Azure SQL Database serverFQDN" Write-Output $SQLServerName Write-Output "Azure SQL Database name" Write-Output $database Write-Output "Your Azure SQL credential name for Automation is:" Write-Output $azureSQLCred Invoke-Sqlcmd -ServerInstance $SQLServerName -Credential $azureSQLCred -Database $database ` -Query "exec [dbo].[AzureSQLMaintenance] @Operation='all' ,@LogToTable=1" -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose |
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
You can scroll down and view the updated statistics script as well for Azure SQL DB.
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.
Publish the azure automation 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.
In the below screenshot, we created a schedule to execute the runbook every Sunday at 12 AM IST.
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
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023