Automating Azure SQL Database index maintenance

Vijay Kumar 2,016

Hi,

I am planning to implement Automating index maintenance on Azure SQL Database.

What is the best approach and ways to implement?

there are 10 DB's.

Mike Ubezzi 2,776 Reputation points

2020-10-20T16:29:44.9+00:00

Hi, @Vijay Kumar - I wanted to follow up with you to see if you have any additional questions or if the solution I provided as an answer is useful to you? Another option is to use the automatic tuning feature. Please see: Automatic tuning in Azure SQL Database and Azure SQL Managed Instance

Automatic tuning is a fully managed intelligent performance service that uses built-in intelligence to continuously monitor queries executed on a database, and it automatically improves their performance. This is achieved through dynamically adapting database to the changing workloads and applying tuning recommendations. Automatic tuning learns horizontally from all databases on Azure through AI and it dynamically improves its tuning actions. The longer a database runs with automatic tuning on, the better it performs.

The first option I mentioned gives you more control.

Regards,
Mike

Accepted answer

Mike Ubezzi 2,776 Reputation points

2020-10-17T03:23:27.87+00:00
Hi, @Vijay Kumar - Please take a look at the following TechCommunity blog specific to your ask: Automating Azure SQL DB index and statistics maintenance using Azure Automation

The general steps are the following where the blog has the detailed steps:

Azure automation account

Import SQLServer module

Add Credentials to access SQL DB

Add a runbook to run the maintenance

Schedule task

Please let the forum know if you have any additional questions or need assistance deploying solution detailed in the document.
Please sign in to rate this answer.
Vijay Kumar 2,016 Reputation points

2020-10-20T00:46:02.063+00:00

Hi Mike,

I have successfully created.

Here are the few quesitons?

Can use same runbook for another SQL DB (like sharing runbook)? instead of creating new runbook
--> My plan is to implement same index mainteance plan for another 3 SQL Db's.

How to create alert if jobs fails?

Repon Haque 0 Reputation points

2023-06-06T16:18:15.15+00:00

Hi, Is there a way to trigger the email that the job has been completed successfully from the above steps? Thanks
Sign in to comment

0 additional answers