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

  1. Automated Performance tuning
  2. Automated Verification of Performance issues
  3. Automated Rollback and Self Correction
  4. Performance tuning History
  5. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.