Database Recommended Actions - Update

Updates a database recommended action.

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

URI Parameters

Name In Required Type Description
advisorName
path True

string

The name of the Database Advisor.

databaseName
path True

string

The name of the database.

recommendedActionName
path True

string

The name of Database Recommended Action.

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.

subscriptionId
path True

string

The subscription ID that identifies an Azure subscription.

api-version
query True

string

The API version to use for the request.

Request Body

Name Required Type Description
properties.state True

RecommendedActionStateInfo

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

Responses

Name Type Description
200 OK

RecommendedAction

Successfully updated the specified database recommended action.

Other Status Codes

*** Error Responses: ***

  • 400 FeatureDisabledOnSelectedEdition - User attempted to use a feature which is disabled on current database edition.

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

  • 400 InvalidRecommendedActionUpsertRequest - The update recommended action request body does not exist or has no properties or state object.

  • 400 InvalidRecommendedActionState - The specified state for recommended action is invalid

  • 404 DatabaseDoesNotExist - The requested database was not found

  • 404 SubscriptionDoesNotHaveServer - The requested server 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

PATCH 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/IR_[CRM]_[DataPoints]_4821CD2F9510D98184BB?api-version=2021-11-01

{
  "properties": {
    "state": {
      "currentValue": "Pending"
    }
  }
}

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": "Pending",
      "actionInitiatedBy": "User",
      "lastModified": "2017-06-20T15:11:15Z"
    },
    "isExecutableAction": true,
    "isRevertableAction": true,
    "isArchivedAction": false,
    "executeActionInitiatedBy": "User",
    "executeActionInitiatedTime": "2017-06-20T15:11:15Z",
    "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]"
    }
  }
}

Definitions

Name Description
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 explicity 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

RecommendedActionErrorInfo

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

properties.estimatedImpact

RecommendedActionImpactRecord[]

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

RecommendedActionInitiatedBy

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

RecommendedActionImplementationInfo

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

RecommendedActionImpactRecord[]

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

RecommendedActionInitiatedBy

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

RecommendedActionStateInfo

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

properties.timeSeries

RecommendedActionMetricInfo[]

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

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

ImplementationMethod

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 explicity 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

RecommendedActionInitiatedBy

Gets who initiated the execution of this recommended action. Possible Value are: User -> When user explicity 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

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.

lastModified

string

Gets the time when the state was last modified