How to use Azure Automatic performance tuning ?

 

Automatic performance tuning in Azure SQL Database is a powerful feature that helps optimize query performance without manual intervention. It includes capabilities like automatic plan correction, index tuning, and query regression detection. Here's a step-by-step guide to enable and manage it, especially focused on query performance tuning:

---

Step-by-Step: Enable Automatic Performance Tuning in Azure SQL

Step 1: Open Azure Portal

• Go to https://portal.azure.com
• Sign in with your Azure account.

Step 2: Go to Your SQL Database

• Navigate to "SQL databases"
• Click on the target database you want to enable tuning for.

Step 3: Enable Automatic Tuning

• In the left-side menu, find and click on "Automatic tuning"
• You will see the following options:
  • Force Last Good Plan – Automatically reverts to a known good query plan if performance regresses.
  • Create Index – Azure creates recommended indexes automatically.
  • Drop Index – Azure drops unused or duplicate indexes.
• Toggle “ON” for the features you want (especially Force Last Good Plan for query tuning).
• Click "Save"

---

Step 4: Monitor Tuning Actions

• In the same Automatic tuning blade, scroll down to view tuning history.
• You’ll see actions taken by Azure like "Plan forced", "Index created", or "Query plan reverted".

---

Step 5: Review Query Performance Insights

• Go to "Query Performance Insight" under the database menu.
• You can view:
  • Top resource-consuming queries
  • Execution count
  • Duration trends
• Use this to validate if tuning improved performance.

---

Step 6: Use T-SQL (Optional) to Check Settings

Run the following T-SQL in Query Editor or SSMS:

-- Check current automatic tuning configuration
SELECT * FROM sys.database_automatic_tuning_options;

-- Enable Force Last Good Plan
ALTER DATABASE CURRENT 
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

---

Extra Tip: Use Query Store

• Query Store helps track query performance and is used by automatic tuning.
• Ensure it's enabled:

ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE = ON;