Azure SQL Database Automatic Tuning Feature

What is Azure Automatic Tuning Feature

Microsoft Azure SQL Database Automatic tuning one of the monitoring and tuning features we can use it on Azure SQL for more information about other monitoring and tuning services check this Article, Automatic tuning is a fully managed intelligent performance service used for collecting the Azure SQL Database Workload and monitor queries executed on a database continuously, and it is working based on AI Artificial intelligence and ML Machine Learning technologies, with Automatic tuning Azure SQL will automatically capture all of the performance issues and analyze it then will create a new index or drop index based on the performance case, even one of the capabilities of this services after applying the performance solution it will be under validating based on the AI and ML technologies and if the Azure Automatic tuning found this solution not fixed the issue, it will rollback it automatically, all of this actions will be recorded in the logs to be able to track it

Automatic tuning Provide Full feature support

Automatic tuning Provide Full feature support Azure SQL Single Database, Azure Elastic Pool Database
Automatic tuning Supported SQL Server 2017 or higher but in SQL Server On-premises 2017 or higher it depends on SQL Server Query Store feature this means you should enable the Query Store First For More information About Query Store Check this Article and for Automatic tuning on SQL Server 2017 Check this Article
Automatic tuning Supported Azure SQL Managed instance but not Full support it is partially Support means it will give you the availability to capture the performance issue and capture the Database Workload and monitor the Execute queries on Database to only correct the query plan by forcing the last good query plan. But creating, removing any index not supported and rollback the recommendation after applying it not supported

Query Store already enabled by default on Azure SQL Database and there some mentoring performance feature on Azure SQL depends on Query store like (Azure Performance recommendation, Query Performance insight) For more information about this point check this Microsoft post

Automatic tuning capabilities

Automated Performance tuning
Automated Verification of Performance issues
Automated Rollback and Self Correction
Performance tuning History
Performance Workload monitoring

Enable Automatic Tuning Options

The Feature you can enable it on Server level or Database level on Azure in Server level you can enable it using Azure Portal but for Database level you can enable it using Azure Portal or Using T-SQL command, also the users can enable the Automatic Tuning on Azure SQL Database should be a member in SQL Database contributor or on higher-level access like SQL Server Contributor, SQL Managed Instance Contributor, Contributor, and Owner.

Keep Following

Published by Mustafa EL-Masry

I am Microsoft database consultant working as a Database administrator for more than +10 Years I have very good knowledge about Database Migration, Consolidation, Performance Tuning, Automation Using T-SQL, and PowerShell and so many other tasks I do it in multiple customers here in KSA and as of now, I am working in Bank Albilad managing the core banking system that is hosted in SQL Server Database 8 TB. Also, I am Microsoft certified 2008 and 2016 in SQL Server (2x MCTS, 2x MCTIP, MCSA, MCSE) and I am Microsoft Certified Trainer (MCT) also I am azure Certified (AZ-900, AZ-103) also I was awarded by Microsoft Azure Heroes 3 times as (Azure Content hero, Azure Community hero and Azure Mentor) For more information check my page https://mostafaelmasry.com/about-me/ View all posts by Mustafa EL-Masry