Database Recommended Actions - Get
Gets a database recommended action.
GET 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 |
---|---|---|---|---|
advisor
|
path | True |
string |
The name of the Database Advisor. |
database
|
path | True |
string |
The name of the database. |
recommended
|
path | True |
string |
The name of Database Recommended Action. |
resource
|
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. |
server
|
path | True |
string |
The name of the server. |
subscription
|
path | True |
string |
The subscription ID that identifies an Azure subscription. |
api-version
|
query | True |
string |
The API version to use for the request. |
Responses
Name | Type | Description |
---|---|---|
200 OK |
Successfully retrieved details of specified database recommended action. |
|
Other Status Codes |
*** Error Responses: ***
|
Examples
Get database recommended action
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/IR_[CRM]_[DataPoints]_4821CD2F9510D98184BB?api-version=2021-11-01
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]"
}
}
}
Definitions
Name | Description |
---|---|
Implementation |
Gets the method in which this recommended action can be manually implemented. e.g., TSql, AzurePowerShell. |
Is |
Gets whether the error could be ignored and recommended action could be retried. Possible values are: Yes/No |
Recommended |
Database, Server or Elastic Pool Recommended Action. |
Recommended |
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. |
Recommended |
Contains error information for an Azure SQL Database, Server or Elastic Pool Recommended Action. |
Recommended |
Contains information of estimated or observed impact on various metrics for an Azure SQL Database, Server or Elastic Pool Recommended Action. |
Recommended |
Contains information for manual implementation for an Azure SQL Database, Server or Elastic Pool Recommended Action. |
Recommended |
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. |
Recommended |
Contains time series of various impacted metrics for an Azure SQL Database, Server or Elastic Pool Recommended Action. |
Recommended |
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 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 |
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 |
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 |