Database Recommended Actions - List By Database Advisor

Gets list of Database Recommended Actions.

GET https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}/advisors/{advisorName}/recommendedActions?api-version=2015-05-01-preview

URI Parameters

Name In Required Type Description
subscriptionId
path True
  • string

The subscription ID that identifies an Azure subscription.

resourceGroupName
path True
  • string

The name of the resource group that contains the resource. You can obtain this value from the Azure Resource Manager API or the portal.

serverName
path True
  • string

The name of the server.

databaseName
path True
  • string

The name of the database.

advisorName
path True
  • string

The name of the Database Advisor.

api-version
query True
  • string

The API version to use for the request.

Responses

Name Type Description
200 OK

Successfully retrieved the list of database recommended actions.

Other Status Codes

*** Error Responses: ***

  • 400 InvalidIdentifier - The identifier contains NULL or an invalid unicode character.

  • 404 SubscriptionDoesNotHaveServer - The requested server was not found

  • 404 DatabaseDoesNotExist - The requested database was not found

  • 404 SubscriptionDoesNotHaveElasticPool - The requested elastic pool was not found

  • 404 AdvisorNotFound - The requested advisor was not found.

  • 404 RecommendedActionNotFound - The requested recommended action was not found.

  • 404 AdvisorNotFound - The requested advisor was not found.

  • 404 RecommendedActionNotFound - The requested recommended action was not found.

  • 405 NotSupported - This functionality is not supported.

  • 409 Conflict - Request could not be processed because of conflict in the request.

  • 503 ServiceTemporarilyUnavailable - Feature temporarily unavailable.

Examples

Sample Request

GET https://management.azure.com/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/CreateIndex/recommendedActions?api-version=2015-05-01-preview

Sample Response

[
  {
    "id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/CreateIndex/recommendedActions/IR_[CRM]_[DataPoints]_4821CD2F9510D98184BB",
    "name": "IR_[CRM]_[DataPoints]_4821CD2F9510D98184BB",
    "type": "Microsoft.Sql/servers/databases/advisors/recommendedActions",
    "location": "East Asia",
    "kind": "",
    "properties": {
      "recommendationReason": "",
      "validSince": "2017-03-01T14:38:04Z",
      "lastRefresh": "2017-03-01T14:38:04Z",
      "state": {
        "currentValue": "Active",
        "lastModified": "2017-03-01T14:38:05Z"
      },
      "isExecutableAction": true,
      "isRevertableAction": true,
      "isArchivedAction": false,
      "score": 1,
      "implementationDetails": {
        "method": "TSql",
        "script": "CREATE NONCLUSTERED INDEX [nci_wi_DataPoints_B892614093BAC56295EF6018BD4CB51B] ON [CRM].[DataPoints] ([Name],[Money],[Power]) INCLUDE ([Hour], [System], [LastChanged]) WITH (ONLINE = ON)"
      },
      "errorDetails": {},
      "estimatedImpact": [
        {
          "dimensionName": "ActionDuration",
          "unit": "Seconds",
          "absoluteValue": 1440
        },
        {
          "dimensionName": "SpaceChange",
          "unit": "Megabytes",
          "absoluteValue": 209.3125
        }
      ],
      "observedImpact": [],
      "timeSeries": [],
      "details": {
        "indexName": "nci_wi_DataPoints_B892614093BAC56295EF6018BD4CB51B",
        "indexType": "NONCLUSTERED",
        "schema": "[CRM]",
        "table": "[DataPoints]",
        "indexColumns": "[Name],[Money],[Power]",
        "includedColumns": "[Hour], [System], [LastChanged]"
      }
    }
  },
  {
    "id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/CreateIndex/recommendedActions/IR_[dbo]_[DataPoints]_F5D2F347AA22DB46E4CC",
    "name": "IR_[dbo]_[DataPoints]_F5D2F347AA22DB46E4CC",
    "type": "Microsoft.Sql/servers/databases/advisors/recommendedActions",
    "location": "East Asia",
    "kind": "",
    "properties": {
      "recommendationReason": "",
      "validSince": "2017-03-01T14:38:05Z",
      "lastRefresh": "2017-03-01T14:38:05Z",
      "state": {
        "currentValue": "Success",
        "actionInitiatedBy": "User",
        "lastModified": "2017-03-01T14:38:05Z"
      },
      "isExecutableAction": true,
      "isRevertableAction": true,
      "isArchivedAction": false,
      "executeActionStartTime": "2017-03-01T14:38:05Z",
      "executeActionDuration": "PT1M",
      "executeActionInitiatedBy": "User",
      "executeActionInitiatedTime": "2017-03-01T14:38:05Z",
      "score": 3,
      "implementationDetails": {
        "method": "TSql",
        "script": "DROP INDEX [nci_wi_DataPoints_609E4B7D6A3813990ED44B28B340C8FC] ON [dbo].[DataPoints]"
      },
      "errorDetails": {},
      "estimatedImpact": [
        {
          "dimensionName": "ActionDuration",
          "unit": "Seconds",
          "absoluteValue": 5040
        },
        {
          "dimensionName": "SpaceChange",
          "unit": "Megabytes",
          "absoluteValue": 120
        }
      ],
      "observedImpact": [
        {
          "dimensionName": "AffectedQueriesCpuUtilization",
          "unit": "CpuCores",
          "changeValueAbsolute": -12.7,
          "changeValueRelative": -0.9
        },
        {
          "dimensionName": "CpuUtilization",
          "unit": "CpuCores",
          "changeValueAbsolute": -12.7,
          "changeValueRelative": -0.3175
        },
        {
          "dimensionName": "QueriesWithImprovedPerformance",
          "unit": "Count",
          "absoluteValue": 12
        },
        {
          "dimensionName": "QueriesWithRegressedPerformance",
          "unit": "Count",
          "absoluteValue": 1
        },
        {
          "dimensionName": "SpaceChange",
          "unit": "Megabytes",
          "absoluteValue": 130.742187
        },
        {
          "dimensionName": "VerificationProgress",
          "unit": "Percent",
          "absoluteValue": 0
        }
      ],
      "timeSeries": [],
      "details": {
        "indexName": "nci_wi_DataPoints_609E4B7D6A3813990ED44B28B340C8FC",
        "indexType": "NONCLUSTERED",
        "schema": "[dbo]",
        "table": "[DataPoints]",
        "indexColumns": "[Name],[Money]",
        "includedColumns": "[Power],[Pineapple]",
        "indexActionStartTime": "2017-03-01T14:38:05.337",
        "indexActionDuration": "00:01:00"
      }
    }
  },
  {
    "id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/CreateIndex/recommendedActions/IR_[dbo]_[Employees]_560E15A98D14CA09BDFB",
    "name": "IR_[dbo]_[Employees]_560E15A98D14CA09BDFB",
    "type": "Microsoft.Sql/servers/databases/advisors/recommendedActions",
    "location": "East Asia",
    "kind": "",
    "properties": {
      "recommendationReason": "",
      "validSince": "2017-03-01T14:38:05Z",
      "lastRefresh": "2017-03-01T14:38:05Z",
      "state": {
        "currentValue": "Active",
        "lastModified": "2017-03-01T14:38:05Z"
      },
      "isExecutableAction": true,
      "isRevertableAction": true,
      "isArchivedAction": false,
      "score": 3,
      "implementationDetails": {
        "method": "TSql",
        "script": "CREATE NONCLUSTERED INDEX [nci_wi_Employees_8C18C2AF4267DC77793040782641CCDE] ON [dbo].[Employees] ([City], [State]) INCLUDE ([Postal]) WITH (ONLINE = ON)"
      },
      "errorDetails": {},
      "estimatedImpact": [
        {
          "dimensionName": "ActionDuration",
          "unit": "Seconds",
          "absoluteValue": 17
        },
        {
          "dimensionName": "SpaceChange",
          "unit": "Megabytes",
          "absoluteValue": 128
        }
      ],
      "observedImpact": [],
      "timeSeries": [],
      "details": {
        "indexName": "nci_wi_Employees_8C18C2AF4267DC77793040782641CCDE",
        "indexType": "NONCLUSTERED",
        "schema": "[dbo]",
        "table": "[Employees]",
        "indexColumns": "[City], [State]",
        "includedColumns": "[Postal]"
      }
    }
  }
]

Definitions

ImplementationMethod

Gets the method in which this recommended action can be manually implemented. e.g., TSql, AzurePowerShell.

IsRetryable

Gets whether the error could be ignored and recommended action could be retried. Possible values are: Yes/No

RecommendedAction

Database, Server or Elastic Pool Recommended Action.

RecommendedActionCurrentState

Current state the recommended action is in. Some commonly used states are: Active -> recommended action is active and no action has been taken yet. Pending -> recommended action is approved for and is awaiting execution. Executing -> recommended action is being applied on the user database. Verifying -> recommended action was applied and is being verified of its usefulness by the system. Success -> recommended action was applied and improvement found during verification. Pending Revert -> verification found little or no improvement so recommended action is queued for revert or user has manually reverted. Reverting -> changes made while applying recommended action are being reverted on the user database. Reverted -> successfully reverted the changes made by recommended action on user database. Ignored -> user explicitly ignored/discarded the recommended action.

RecommendedActionErrorInfo

Contains error information for an Azure SQL Database, Server or Elastic Pool Recommended Action.

RecommendedActionImpactRecord

Contains information of estimated or observed impact on various metrics for an Azure SQL Database, Server or Elastic Pool Recommended Action.

RecommendedActionImplementationInfo

Contains information for manual implementation for an Azure SQL Database, Server or Elastic Pool Recommended Action.

RecommendedActionInitiatedBy

Gets who initiated the execution of this recommended action. Possible Value are: User -> When user explicitly notified system to apply the recommended action. System -> When auto-execute status of this advisor was set to 'Enabled', in which case the system applied it.

RecommendedActionMetricInfo

Contains time series of various impacted metrics for an Azure SQL Database, Server or Elastic Pool Recommended Action.

RecommendedActionStateInfo

Contains information of current state for an Azure SQL Database, Server or Elastic Pool Recommended Action.

ImplementationMethod

Gets the method in which this recommended action can be manually implemented. e.g., TSql, AzurePowerShell.

Name Type Description
AzurePowerShell
  • string
TSql
  • string

IsRetryable

Gets whether the error could be ignored and recommended action could be retried. Possible values are: Yes/No

Name Type Description
No
  • string
Yes
  • string

RecommendedAction

Database, Server or Elastic Pool Recommended Action.

Name Type Description
id
  • string

Resource ID.

kind
  • string

Resource kind.

location
  • string

Resource location.

name
  • string

Resource name.

properties.details
  • object

Gets additional details specific to this recommended action.

properties.errorDetails

Gets the error details if and why this recommended action is put to error state.

properties.estimatedImpact

Gets the estimated impact info for this recommended action e.g., Estimated CPU gain, Estimated Disk Space change

properties.executeActionDuration
  • string

Gets the time taken for applying this recommended action on user resource. e.g., time taken for index creation

properties.executeActionInitiatedBy

Gets if approval for applying this recommended action was given by user/system.

properties.executeActionInitiatedTime
  • string

Gets the time when this recommended action was approved for execution.

properties.executeActionStartTime
  • string

Gets the time when system started applying this recommended action on the user resource. e.g., index creation start time

properties.implementationDetails

Gets the implementation details of this recommended action for user to apply it manually.

properties.isArchivedAction
  • boolean

Gets if this recommended action was suggested some time ago but user chose to ignore this and system added a new recommended action again.

properties.isExecutableAction
  • boolean

Gets if this recommended action is actionable by user

properties.isRevertableAction
  • boolean

Gets if changes applied by this recommended action can be reverted by user

properties.lastRefresh
  • string

Gets time when this recommended action was last refreshed.

properties.linkedObjects
  • string[]

Gets the linked objects, if any.

properties.observedImpact

Gets the observed/actual impact info for this recommended action e.g., Actual CPU gain, Actual Disk Space change

properties.recommendationReason
  • string

Gets the reason for recommending this action. e.g., DuplicateIndex

properties.revertActionDuration
  • string

Gets the time taken for reverting changes of this recommended action on user resource. e.g., time taken for dropping the created index.

properties.revertActionInitiatedBy

Gets if approval for reverting this recommended action was given by user/system.

properties.revertActionInitiatedTime
  • string

Gets the time when this recommended action was approved for revert.

properties.revertActionStartTime
  • string

Gets the time when system started reverting changes of this recommended action on user resource. e.g., time when index drop is executed.

properties.score
  • integer

Gets the impact of this recommended action. Possible values are 1 - Low impact, 2 - Medium Impact and 3 - High Impact

properties.state

Gets the info of the current state the recommended action is in.

properties.timeSeries

Gets the time series info of metrics for this recommended action e.g., CPU consumption time series

properties.validSince
  • string

Gets the time since when this recommended action is valid.

type
  • string

Resource type.

RecommendedActionCurrentState

Current state the recommended action is in. Some commonly used states are: Active -> recommended action is active and no action has been taken yet. Pending -> recommended action is approved for and is awaiting execution. Executing -> recommended action is being applied on the user database. Verifying -> recommended action was applied and is being verified of its usefulness by the system. Success -> recommended action was applied and improvement found during verification. Pending Revert -> verification found little or no improvement so recommended action is queued for revert or user has manually reverted. Reverting -> changes made while applying recommended action are being reverted on the user database. Reverted -> successfully reverted the changes made by recommended action on user database. Ignored -> user explicitly ignored/discarded the recommended action.

Name Type Description
Active
  • string
Error
  • string
Executing
  • string
Expired
  • string
Ignored
  • string
Monitoring
  • string
Pending
  • string
PendingRevert
  • string
Resolved
  • string
RevertCancelled
  • string
Reverted
  • string
Reverting
  • string
Success
  • string
Verifying
  • string

RecommendedActionErrorInfo

Contains error information for an Azure SQL Database, Server or Elastic Pool Recommended Action.

Name Type Description
errorCode
  • string

Gets the reason why the recommended action was put to error state. e.g., DatabaseHasQdsOff, IndexAlreadyExists

isRetryable

Gets whether the error could be ignored and recommended action could be retried. Possible values are: Yes/No

RecommendedActionImpactRecord

Contains information of estimated or observed impact on various metrics for an Azure SQL Database, Server or Elastic Pool Recommended Action.

Name Type Description
absoluteValue
  • number

Gets the absolute value of this dimension if applicable. e.g., Number of Queries affected

changeValueAbsolute
  • number

Gets the absolute change in the value of this dimension. e.g., Absolute Disk space change in Megabytes

changeValueRelative
  • number

Gets the relative change in the value of this dimension. e.g., Relative Disk space change in Percentage

dimensionName
  • string

Gets the name of the impact dimension. e.g., CPUChange, DiskSpaceChange, NumberOfQueriesAffected.

unit
  • string

Gets the name of the impact dimension. e.g., CPUChange, DiskSpaceChange, NumberOfQueriesAffected.

RecommendedActionImplementationInfo

Contains information for manual implementation for an Azure SQL Database, Server or Elastic Pool Recommended Action.

Name Type Description
method

Gets the method in which this recommended action can be manually implemented. e.g., TSql, AzurePowerShell.

script
  • string

Gets the manual implementation script. e.g., T-SQL script that could be executed on the database.

RecommendedActionInitiatedBy

Gets who initiated the execution of this recommended action. Possible Value are: User -> When user explicitly notified system to apply the recommended action. System -> When auto-execute status of this advisor was set to 'Enabled', in which case the system applied it.

Name Type Description
System
  • string
User
  • string

RecommendedActionMetricInfo

Contains time series of various impacted metrics for an Azure SQL Database, Server or Elastic Pool Recommended Action.

Name Type Description
metricName
  • string

Gets the name of the metric. e.g., CPU, Number of Queries.

startTime
  • string

Gets the start time of time interval given by this MetricInfo.

timeGrain
  • string

Gets the duration of time interval for the value given by this MetricInfo. e.g., PT1H (1 hour)

unit
  • string

Gets the unit in which metric is measured. e.g., DTU, Frequency

value
  • number

Gets the value of the metric in the time interval given by this MetricInfo.

RecommendedActionStateInfo

Contains information of current state for an Azure SQL Database, Server or Elastic Pool Recommended Action.

Name Type Description
actionInitiatedBy

Gets who initiated the execution of this recommended action. Possible Value are: User -> When user explicitly notified system to apply the recommended action. System -> When auto-execute status of this advisor was set to 'Enabled', in which case the system applied it.

currentValue

Current state the recommended action is in. Some commonly used states are: Active -> recommended action is active and no action has been taken yet. Pending -> recommended action is approved for and is awaiting execution. Executing -> recommended action is being applied on the user database. Verifying -> recommended action was applied and is being verified of its usefulness by the system. Success -> recommended action was applied and improvement found during verification. Pending Revert -> verification found little or no improvement so recommended action is queued for revert or user has manually reverted. Reverting -> changes made while applying recommended action are being reverted on the user database. Reverted -> successfully reverted the changes made by recommended action on user database. Ignored -> user explicitly ignored/discarded the recommended action.

lastModified
  • string

Gets the time when the state was last modified