Database Advisors - Update

Reference

Service:: SQL Database

API Version:: 2021-11-01

Updates a database advisor.

PATCH https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}/advisors/{advisorName}?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.
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.autoExecuteStatus	True	AutoExecuteStatus	Gets the auto-execute status (whether to let the system execute the recommendations) of this advisor. Possible values are 'Enabled' and 'Disabled'

Responses

Name	Type	Description
200 OK	Advisor	Successfully updated the specified database advisor.
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 DataWarehouseNotSupported - Automatic tuning is not supported for SQL Data Warehouse. 400 InvalidAdvisorUpsertRequest - The update advisor request body does not exist or has no properties object. 400 InvalidAdvisorAutoExecuteStatus - Specified auto-execute status for the advisor is not allowed. 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 AutomaticTuningOptionNotFound - The requested automatic tuning option was not found. 405 NotSupported - This functionality is not supported. 409 Conflict - Request could not be processed because of conflict in the request. 409 DisabledBySystem - This option is disabled by system 409 DefaultAdvisorStateNotAllowedInCustomDbMode - DEFAULT advisor state is not allowed in CUSTOM mode 503 ServiceTemporarilyUnavailable - Feature temporarily unavailable.

Name

Type

Description

200 OK

Advisor

Successfully updated the specified database advisor.

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 DataWarehouseNotSupported - Automatic tuning is not supported for SQL Data Warehouse.
400 InvalidAdvisorUpsertRequest - The update advisor request body does not exist or has no properties object.
400 InvalidAdvisorAutoExecuteStatus - Specified auto-execute status for the advisor is not allowed.
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 AutomaticTuningOptionNotFound - The requested automatic tuning option was not found.
405 NotSupported - This functionality is not supported.
409 Conflict - Request could not be processed because of conflict in the request.
409 DisabledBySystem - This option is disabled by system
409 DefaultAdvisorStateNotAllowedInCustomDbMode - DEFAULT advisor state is not allowed in CUSTOM mode
503 ServiceTemporarilyUnavailable - Feature temporarily unavailable.

Examples

Update database advisor

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?api-version=2021-11-01

{
  "properties": {
    "autoExecuteStatus": "Disabled"
  }
}


import com.azure.resourcemanager.sql.fluent.models.AdvisorInner;
import com.azure.resourcemanager.sql.models.AutoExecuteStatus;

/**
 * Samples for DatabaseAdvisors Update.
 */
public final class Main {
    /*
     * x-ms-original-file:
     * specification/sql/resource-manager/Microsoft.Sql/stable/2021-11-01/examples/DatabaseAdvisorUpdate.json
     */
    /**
     * Sample code: Update database advisor.
     * 
     * @param azure The entry point for accessing resource management APIs in Azure.
     */
    public static void updateDatabaseAdvisor(com.azure.resourcemanager.AzureResourceManager azure) {
        azure.sqlServers().manager().serviceClient().getDatabaseAdvisors().updateWithResponse("workloadinsight-demos",
            "misosisvr", "IndexAdvisor_test_3", "CreateIndex",
            new AdvisorInner().withAutoExecuteStatus(AutoExecuteStatus.DISABLED), 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",
  "name": "CreateIndex",
  "type": "Microsoft.Sql/servers/databases/advisors",
  "properties": {
    "advisorStatus": "GA",
    "autoExecuteStatus": "Disabled",
    "recommendationsStatus": "Ok",
    "lastChecked": "2017-06-20T00:24:39Z"
  }
}

Definitions

Name	Description
Advisor	Database, Server or Elastic Pool Advisor.
AdvisorStatus	Gets the status of availability of this advisor to customers. Possible values are 'GA', 'PublicPreview', 'LimitedPublicPreview' and 'PrivatePreview'.
AutoExecuteStatus	Gets the auto-execute status (whether to let the system execute the recommendations) of this advisor. Possible values are 'Enabled' and 'Disabled'
AutoExecuteStatusInheritedFrom	Gets the resource from which current value of auto-execute status is inherited. Auto-execute status can be set on (and inherited from) different levels in the resource hierarchy. Possible values are 'Subscription', 'Server', 'ElasticPool', 'Database' and 'Default' (when status is not explicitly set on any level).
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.

Advisor

Database, Server or Elastic Pool Advisor.

Name	Type	Description
id	string	Resource ID.
kind	string	Resource kind.
location	string	Resource location.
name	string	Resource name.
properties.advisorStatus	AdvisorStatus	Gets the status of availability of this advisor to customers. Possible values are 'GA', 'PublicPreview', 'LimitedPublicPreview' and 'PrivatePreview'.
properties.autoExecuteStatus	AutoExecuteStatus	Gets the auto-execute status (whether to let the system execute the recommendations) of this advisor. Possible values are 'Enabled' and 'Disabled'
properties.autoExecuteStatusInheritedFrom	AutoExecuteStatusInheritedFrom	Gets the resource from which current value of auto-execute status is inherited. Auto-execute status can be set on (and inherited from) different levels in the resource hierarchy. Possible values are 'Subscription', 'Server', 'ElasticPool', 'Database' and 'Default' (when status is not explicitly set on any level).
properties.lastChecked	string	Gets the time when the current resource was analyzed for recommendations by this advisor.
properties.recommendationsStatus	string	Gets that status of recommendations for this advisor and reason for not having any recommendations. Possible values include, but are not limited to, 'Ok' (Recommendations available),LowActivity (not enough workload to analyze), 'DbSeemsTuned' (Database is doing well), etc.
properties.recommendedActions	RecommendedAction[]	Gets the recommended actions for this advisor.
type	string	Resource type.

AdvisorStatus

Gets the status of availability of this advisor to customers. Possible values are 'GA', 'PublicPreview', 'LimitedPublicPreview' and 'PrivatePreview'.

Name	Type	Description
GA	string
LimitedPublicPreview	string
PrivatePreview	string
PublicPreview	string

AutoExecuteStatus

Gets the auto-execute status (whether to let the system execute the recommendations) of this advisor. Possible values are 'Enabled' and 'Disabled'

Name	Type	Description
Default	string
Disabled	string
Enabled	string

AutoExecuteStatusInheritedFrom

Gets the resource from which current value of auto-execute status is inherited. Auto-execute status can be set on (and inherited from) different levels in the resource hierarchy. Possible values are 'Subscription', 'Server', 'ElasticPool', 'Database' and 'Default' (when status is not explicitly set on any level).

Name	Type	Description
Database	string
Default	string
ElasticPool	string
Server	string
Subscription	string

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

Share via

Database Advisors - Update

URI Parameters

Request Body

Responses

Examples

Update database advisor

Sample request

Sample response

Definitions

Advisor

AdvisorStatus

AutoExecuteStatus

AutoExecuteStatusInheritedFrom

ImplementationMethod

IsRetryable

RecommendedAction

RecommendedActionCurrentState

RecommendedActionErrorInfo

RecommendedActionImpactRecord

RecommendedActionImplementationInfo

RecommendedActionInitiatedBy

RecommendedActionMetricInfo

RecommendedActionStateInfo

Additional resources