获取Azure索引推荐的JSON响应：API端点与存储位置问询

Let's break down your two questions clearly and directly:

1. HTTP Endpoint for Retrieving Index Recommendations #

Yes, Azure provides a REST API endpoint to fetch index recommendations via the Azure Management Plane. You can use the Advisor Recommendations API for individual databases, which returns Azure Advisor's curated index suggestions (along with other advisor recommendations like performance tweaks or security fixes).

The endpoint format is:

GET https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}/advisorRecommendations?api-version=2021-11-01

• You’ll need to authenticate with an Azure AD token (via OAuth 2.0) and hold appropriate RBAC permissions (like SQL DB Contributor or Reader) for the target resources.
• There’s no single endpoint to pull recommendations for all databases in a subscription directly. Instead, you’ll need to:
  1. Enumerate all SQL servers in the subscription using the SQL Servers List API
  2. For each server, list all its associated databases
  3. Call the advisor recommendations endpoint for each database individually

2. Index Recommendations Storage in Master Database #

Azure Advisor’s index recommendations aren’t stored directly in the master database of your SQL server. However, the real-time missing index suggestions generated by the database engine (one of the data sources Azure Advisor uses) are exposed via dynamic management views (DMVs) in each individual user database—not the master database.

Key DMVs for missing index details include:

• sys.dm_db_missing_index_details: Returns granular information about missing indexes
• sys.dm_db_missing_index_groups: Maps missing index details to logical index groups
• sys.dm_db_missing_index_group_stats: Provides usage statistics to prioritize high-impact indexes

You can query these views in a user database with a sample query like:

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
  'CREATE NONCLUSTERED INDEX IX_' + OBJECT_NAME(mid.object_id) + '_' +
    REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') +
    CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END +
    REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '')
  + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') +
    CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ', ' ELSE '' END +
    ISNULL(mid.inequality_columns, '') + ')' +
    ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.object_id
FROM sys.dm_db_missing_index_group_stats migs
INNER JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

If you’re specifically after Azure Advisor’s curated recommendations (which include estimated impact and implementation guidance), stick to the REST API mentioned earlier—these aren’t stored in database-internal tables/views.

内容的提问来源于stack exchange，提问作者Kartik Nadiger