Performance Tuning Advisor

The Performance Tuning Advisor (PTA) in Azure SQL helps you identify and apply recommendations to improve database performance.

Key Features

Automated index recommendations
Query plan analysis
Dynamic management view (DMV) integration
Customizable workloads

Overview

Setup

Using PTA

API

PTA continuously monitors query performance and suggests indexes or statistics updates. Recommendations are based on query frequency, cost, and resource utilization.

Enable PTA using Azure portal, PowerShell, or Azure CLI.

az sql db update \
  --resource-group MyResourceGroup \
  --server myserver \
  --name mydb \
  --set performanceTuningAdvisor.enabled=true

View recommendations in the Azure portal or query the sys.dm_db_tuning_recommendations DMV.

SELECT *
FROM sys.dm_db_tuning_recommendations
WHERE state = 'pending';

Programmatically retrieve recommendations via REST API.

GET https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}/tuningRecommendations?api-version=2022-02-01-preview

Best Practices

Review each recommendation before applying.
Test index changes in a staging environment.
Schedule regular tuning cycles.
Combine PTA with query optimization techniques.