Database Recommended Actions - Update

Service:

SQL Database

API Version:

2021-11-01

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

Update database recommended action

Sample request

HTTP

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"
    }
  }
}

Java

import com.azure.resourcemanager.sql.fluent.models.RecommendedActionInner;
import com.azure.resourcemanager.sql.models.RecommendedActionCurrentState;
import com.azure.resourcemanager.sql.models.RecommendedActionStateInfo;

/**
 * Samples for DatabaseRecommendedActions Update.
 */
public final class Main {
    /*
     * x-ms-original-file:
     * specification/sql/resource-manager/Microsoft.Sql/stable/2021-11-01/examples/DatabaseRecommendedActionUpdate.json
     */
    /**
     * Sample code: Update database recommended action.
     * 
     * @param azure The entry point for accessing resource management APIs in Azure.
     */
    public static void updateDatabaseRecommendedAction(com.azure.resourcemanager.AzureResourceManager azure) {
        azure.sqlServers().manager().serviceClient().getDatabaseRecommendedActions().updateWithResponse(
            "workloadinsight-demos", "misosisvr", "IndexAdvisor_test_3", "CreateIndex",
            "IR_[CRM]_[DataPoints]_4821CD2F9510D98184BB",
            new RecommendedActionInner()
                .withState(new RecommendedActionStateInfo().withCurrentValue(RecommendedActionCurrentState.PENDING)),
            com.azure.core.util.Context.NONE);
    }
}

To use the Azure SDK library in your project, see this documentation. To provide feedback on this code sample, open a GitHub issue

Sample response

Status code:

200

{
  "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