Server Advisors - Update

Updates a server advisor.

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

URI Parameters

Name In Required Type Description
advisorName
path True

string

The name of the Server Advisor.

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 server 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 DefaultAdvisorStateNotAllowedInCustomServerMode - DEFAULT advisor state not allowed in CUSTOM mode

  • 409 UpdateNotAllowedIfServerAutotuningUpdateOperationInProgress - Automatic tuning settings from previous request have not propagated to all user databases.

  • 503 ServiceTemporarilyUnavailable - Feature temporarily unavailable.

Examples

Update server advisor

Sample request

PATCH https://management.azure.com/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/CreateIndex?api-version=2021-11-01

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

Sample response

{
  "kind": "",
  "properties": {
    "advisorStatus": "GA",
    "autoExecuteStatus": "Disabled",
    "autoExecuteStatusInheritedFrom": "Server"
  },
  "location": "East Asia",
  "id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/CreateIndex",
  "name": "CreateIndex",
  "type": "Microsoft.Sql/servers/advisors"
}

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