Azure SQL Basics - Automatic Tuning and Performance recommendation

Note that this post is a part of the series. You can view all posts in this series here: Azure SQL and Data Factory Basics - Index

In this post, we will be discussing the performance recommendation and automatic tuning provided by Azure. To make your SQL database perform better we need some insights to get better recommendations and it is provided by default by Azure.

Performance Recommendations

Performance overview provides a summary of your database performance and helps you with performance tuning and troubleshooting.

(Per Microsoft documentation) the performance recommendation options available for single and pooled databases in Azure SQL Database are:

• Create index recommendations - Recommends the creation of indexes that may improve the performance of your workload.
• Drop index recommendations - Recommends removal of redundant and duplicate indexes daily, except for unique indexes, and indexes that were not used for a long time (>90 days). Please note that this option is not compatible with applications using partition switching and index hints. Dropping unused indexes is not supported for Premium and Business Critical service tiers.
• Parameterize queries recommendations (preview) - Recommends forced parameterization in cases when you have one or more queries that are constantly being recompiled but end up with the same query execution plan.
• Fix schema issues recommendations (preview) - Recommendations for schema correction appear when the SQL Database service notices an anomaly in the number of scheme-related SQL errors that are happening on your SQL database. Microsoft is currently deprecating the "Fix schema issue" recommendations.

Automatic Tuning

Azure SQL Database automatic tuning provides peak performance and stable workloads through continuous performance tuning based on AI and machine learning. 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 by dynamically adapting the 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. Azure SQL Database automatic tuning might be one of the most important features that you can enable to provide stable and peak performing database workloads.

What can automatic tuning do for you:

• Automated performance tuning of Azure SQL databases
• Automated verification of performance gains
• Automated rollback and self-correction
• Tuning history
• Tuning action T-SQL scripts for manual deployments
• Proactive workload performance monitoring
• Scale-out capability on hundreds of thousands of databases
• The positive impact to DevOps resources and the total cost of ownership