SqlManagementClient
The Azure SQL Database management API provides a RESTful set of web APIs that interact with Azure SQL Database services to manage your databases
COMMUNITYBEARER0 INSTALLS
OpenAPI Specificationv3.0
{
"swagger": "2.0",
"schemes": [
"https"
],
"host": "management.azure.com",
"info": {
"description": "The Azure SQL Database management API provides a RESTful set of web APIs that interact with Azure SQL Database services to manage your databases. The API enables users to create, retrieve, update, and delete databases, servers, and other entities.",
"title": "SqlManagementClient",
"version": "2015-05-01-preview",
"x-apisguru-categories": [
"cloud"
],
"x-logo": {
"url": "https://api.apis.guru/v2/cache/logo/https_assets.onestore.ms_cdnfiles_onestorerolling-1606-01000_shell_v3_images_logo_microsoft.png"
},
"x-origin": [
{
"format": "swagger",
"url": "https://raw.githubusercontent.com/Azure/azure-rest-api-specs/master/specification/sql/resource-manager/Microsoft.Sql/preview/2015-05-01-preview/advisors.json",
"version": "2.0"
}
],
"x-preferred": true,
"x-providerName": "azure.com",
"x-serviceName": "sql-advisors",
"x-tags": [
"Azure",
"Microsoft"
]
},
"consumes": [
"application/json"
],
"produces": [
"application/json"
],
"securityDefinitions": {
"azure_auth": {
"authorizationUrl": "https://login.microsoftonline.com/common/oauth2/authorize",
"description": "Azure Active Directory OAuth2 Flow",
"flow": "implicit",
"scopes": {
"user_impersonation": "impersonate your user account"
},
"type": "oauth2"
}
},
"parameters": {
"ApiVersionParameter": {
"description": "The API version to use for the request.",
"in": "query",
"name": "api-version",
"required": true,
"type": "string",
"x-ms-parameter-location": "client"
},
"DatabaseNameParameter": {
"description": "The name of the database.",
"in": "path",
"name": "databaseName",
"required": true,
"type": "string",
"x-ms-parameter-location": "method"
},
"ResourceGroupParameter": {
"description": "The name of the resource group that contains the resource. You can obtain this value from the Azure Resource Manager API or the portal.",
"in": "path",
"name": "resourceGroupName",
"required": true,
"type": "string",
"x-ms-parameter-location": "method"
},
"ServerNameParameter": {
"description": "The name of the server.",
"in": "path",
"name": "serverName",
"required": true,
"type": "string",
"x-ms-parameter-location": "method"
},
"SubscriptionIdParameter": {
"description": "The subscription ID that identifies an Azure subscription.",
"in": "path",
"name": "subscriptionId",
"required": true,
"type": "string",
"x-ms-parameter-location": "client"
}
},
"paths": {
"/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/advisors": {
"get": {
"description": "Gets a list of server advisors.",
"operationId": "ServerAdvisors_ListByServer",
"parameters": [
{
"$ref": "#/parameters/ResourceGroupParameter"
},
{
"$ref": "#/parameters/ServerNameParameter"
},
{
"$ref": "#/parameters/SubscriptionIdParameter"
},
{
"$ref": "#/parameters/ApiVersionParameter"
}
],
"responses": {
"200": {
"description": "Successfully retrieved the list of server advisors.",
"schema": {
"items": {
"$ref": "#/definitions/Advisor"
},
"type": "array"
}
},
"default": {
"description": "*** Error Responses: ***\n\n * 400 InvalidIdentifier - The identifier contains NULL or an invalid unicode character.\n\n * 404 SubscriptionDoesNotHaveServer - The requested server was not found\n\n * 404 DatabaseDoesNotExist - The requested database was not found\n\n * 404 SubscriptionDoesNotHaveElasticPool - The requested elastic pool was not found\n\n * 404 AdvisorNotFound - The requested advisor was not found.\n\n * 405 NotSupported - This functionality is not supported.\n\n * 409 Conflict - Request could not be processed because of conflict in the request.\n\n * 503 ServiceTemporarilyUnavailable - Feature temporarily unavailable."
}
},
"tags": [
"ServerAdvisors"
],
"x-ms-examples": {
"List of server advisors": {
"parameters": {
"api-version": "2015-05-01-preview",
"resourceGroupName": "workloadinsight-demos",
"serverName": "misosisvr",
"subscriptionId": "00000000-1111-2222-3333-444444444444"
},
"responses": {
"200": {
"body": [
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/CreateIndex",
"kind": "",
"location": "East Asia",
"name": "CreateIndex",
"properties": {
"advisorStatus": "GA",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Default"
},
"type": "Microsoft.Sql/servers/advisors"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/DropIndex",
"kind": "",
"location": "East Asia",
"name": "DropIndex",
"properties": {
"advisorStatus": "GA",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Default"
},
"type": "Microsoft.Sql/servers/advisors"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/DbParameterization",
"kind": "",
"location": "East Asia",
"name": "DbParameterization",
"properties": {
"advisorStatus": "GA",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Default"
},
"type": "Microsoft.Sql/servers/advisors"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/SchemaIssue",
"kind": "",
"location": "East Asia",
"name": "SchemaIssue",
"properties": {
"advisorStatus": "PublicPreview",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Default"
},
"type": "Microsoft.Sql/servers/advisors"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/ForceLastGoodPlan",
"kind": "",
"location": "East Asia",
"name": "ForceLastGoodPlan",
"properties": {
"advisorStatus": "PrivatePreview",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Default"
},
"type": "Microsoft.Sql/servers/advisors"
}
]
}
}
},
"List of server recommended actions for all advisors": {
"parameters": {
"$expand": "recommendedActions",
"api-version": "2015-05-01-preview",
"resourceGroupName": "workloadinsight-demos",
"serverName": "misosisvr",
"subscriptionId": "00000000-1111-2222-3333-444444444444"
},
"responses": {
"200": {
"body": [
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/CreateIndex",
"kind": "",
"location": "East Asia",
"name": "CreateIndex",
"properties": {
"advisorStatus": "GA",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Server",
"recommendedActions": [
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/CreateIndex/recommendedActions/IR_[CRM]_[DataPoints]_4821CD2F9510D98184BB",
"kind": "",
"location": "East Asia",
"name": "IR_[CRM]_[DataPoints]_4821CD2F9510D98184BB",
"properties": {
"details": {
"databaseName": "IndexAdvisor_test_3",
"includedColumns": "[Hour], [System], [LastChanged]",
"indexColumns": "[Name],[Money],[Power]",
"indexName": "nci_wi_DataPoints_B892614093BAC56295EF6018BD4CB51B",
"indexType": "NONCLUSTERED",
"schema": "[CRM]",
"table": "[DataPoints]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 1440,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 209.3125,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"implementationDetails": {
"method": "TSql",
"script": "CREATE NONCLUSTERED INDEX [nci_wi_DataPoints_B892614093BAC56295EF6018BD4CB51B] ON [CRM].[DataPoints] ([Name],[Money],[Power]) INCLUDE ([Hour], [System], [LastChanged]) WITH (ONLINE = ON)"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:04Z",
"observedImpact": [],
"recommendationReason": "",
"score": 1,
"state": {
"currentValue": "Active",
"lastModified": "2017-06-20T15:15:40Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:04Z"
},
"type": "Microsoft.Sql/servers/advisors/recommendedActions"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/CreateIndex/recommendedActions/IR_[dbo]_[Employees]_560E15A98D14CA09BDFB",
"kind": "",
"location": "East Asia",
"name": "IR_[dbo]_[Employees]_560E15A98D14CA09BDFB",
"properties": {
"details": {
"databaseName": "IndexAdvisor_test_3",
"includedColumns": "[Postal]",
"indexColumns": "[City], [State]",
"indexName": "nci_wi_Employees_8C18C2AF4267DC77793040782641CCDE",
"indexType": "NONCLUSTERED",
"schema": "[dbo]",
"table": "[Employees]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 17,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 128,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"implementationDetails": {
"method": "TSql",
"script": "CREATE NONCLUSTERED INDEX [nci_wi_Employees_8C18C2AF4267DC77793040782641CCDE] ON [dbo].[Employees] ([City], [State]) INCLUDE ([Postal]) WITH (ONLINE = ON)"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:05Z",
"observedImpact": [],
"recommendationReason": "",
"score": 3,
"state": {
"currentValue": "Active",
"lastModified": "2017-03-01T14:38:05Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:05Z"
},
"type": "Microsoft.Sql/servers/advisors/recommendedActions"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/CreateIndex/recommendedActions/IR_[dbo]_[DataPoints]_F5D2F347AA22DB46E4CC",
"kind": "",
"location": "East Asia",
"name": "IR_[dbo]_[DataPoints]_F5D2F347AA22DB46E4CC",
"properties": {
"details": {
"databaseName": "IndexAdvisor_test_3",
"includedColumns": "[Power],[Pineapple]",
"indexActionDuration": "00:01:00",
"indexActionStartTime": "2017-03-01T14:38:05.337",
"indexColumns": "[Name],[Money]",
"indexName": "nci_wi_DataPoints_609E4B7D6A3813990ED44B28B340C8FC",
"indexType": "NONCLUSTERED",
"schema": "[dbo]",
"table": "[DataPoints]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 5040,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 120,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"executeActionDuration": "PT1M",
"executeActionInitiatedBy": "User",
"executeActionInitiatedTime": "2017-03-01T14:38:05Z",
"executeActionStartTime": "2017-03-01T14:38:05Z",
"implementationDetails": {
"method": "TSql",
"script": "DROP INDEX [nci_wi_DataPoints_609E4B7D6A3813990ED44B28B340C8FC] ON [dbo].[DataPoints]"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:05Z",
"observedImpact": [
{
"changeValueAbsolute": -12.7,
"changeValueRelative": -0.9,
"dimensionName": "AffectedQueriesCpuUtilization",
"unit": "CpuCores"
},
{
"changeValueAbsolute": -12.7,
"changeValueRelative": -0.3175,
"dimensionName": "CpuUtilization",
"unit": "CpuCores"
},
{
"absoluteValue": 12,
"dimensionName": "QueriesWithImprovedPerformance",
"unit": "Count"
},
{
"absoluteValue": 1,
"dimensionName": "QueriesWithRegressedPerformance",
"unit": "Count"
},
{
"absoluteValue": 130.742187,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
},
{
"absoluteValue": 0,
"dimensionName": "VerificationProgress",
"unit": "Percent"
}
],
"recommendationReason": "",
"score": 3,
"state": {
"actionInitiatedBy": "User",
"currentValue": "Success",
"lastModified": "2017-03-01T14:38:05Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:05Z"
},
"type": "Microsoft.Sql/servers/advisors/recommendedActions"
}
]
},
"type": "Microsoft.Sql/servers/advisors"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/DropIndex",
"kind": "",
"location": "East Asia",
"name": "DropIndex",
"properties": {
"advisorStatus": "GA",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Default",
"recommendedActions": [
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/DropIndex/recommendedActions/IR_[CRM]_[DataPoints1]_29AEA82685D24704DE1A",
"kind": "",
"location": "East Asia",
"name": "IR_[CRM]_[DataPoints1]_29AEA82685D24704DE1A",
"properties": {
"details": {
"databaseName": "IndexAdvisor_test_3",
"includedColumns": "[Apple]",
"indexColumns": "[Cookies],[SessionId]",
"indexName": "MyIndex123",
"indexType": "NONCLUSTERED",
"originalIndexName": "IX_COM_SKU_SKUDepartmentID",
"schema": "[CRM]",
"table": "[DataPoints1]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 803,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 144.6875,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"executeActionInitiatedBy": "System",
"executeActionInitiatedTime": "2017-03-01T14:38:05Z",
"implementationDetails": {
"method": "TSql",
"script": "DROP INDEX [MyIndex123] ON [CRM].[DataPoints1]"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:05Z",
"observedImpact": [],
"recommendationReason": "Duplicate",
"score": 1,
"state": {
"actionInitiatedBy": "System",
"currentValue": "Pending",
"lastModified": "2017-03-01T14:38:05Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:05Z"
},
"type": "Microsoft.Sql/servers/advisors/recommendedActions"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/DropIndex/recommendedActions/IR_[CRM]_[DataPoints2]_E4B21F229379807E531A",
"kind": "",
"location": "East Asia",
"name": "IR_[CRM]_[DataPoints2]_E4B21F229379807E531A",
"properties": {
"details": {
"databaseName": "IndexAdvisor_test_3",
"includedColumns": "[Apple]",
"indexActionDuration": "00:01:00",
"indexActionStartTime": "2017-03-01T14:38:05.697",
"indexColumns": "[Cookies],[SessionId],[Protocol]",
"indexName": "MyIndex321",
"indexType": "NONCLUSTERED",
"originalIndexName": "IX_COM_SKU_SKUDepartmentID",
"schema": "[CRM]",
"table": "[DataPoints2]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 705,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 342,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"executeActionDuration": "PT1M",
"executeActionInitiatedBy": "User",
"executeActionInitiatedTime": "2017-03-01T14:38:05Z",
"executeActionStartTime": "2017-03-01T14:38:05Z",
"implementationDetails": {
"method": "TSql",
"script": "CREATE NONCLUSTERED INDEX [MyIndex321] ON [CRM].[DataPoints2] ([Cookies],[SessionId],[Protocol]) INCLUDE ([Apple]) WITH (ONLINE = ON)"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:05Z",
"observedImpact": [
{
"changeValueAbsolute": 0,
"changeValueRelative": 0,
"dimensionName": "AffectedQueriesCpuUtilization",
"unit": "CpuCores"
},
{
"changeValueAbsolute": 0,
"changeValueRelative": 0,
"dimensionName": "CpuUtilization",
"unit": "CpuCores"
},
{
"absoluteValue": 0,
"dimensionName": "QueriesWithImprovedPerformance",
"unit": "Count"
},
{
"absoluteValue": 0,
"dimensionName": "QueriesWithRegressedPerformance",
"unit": "Count"
},
{
"absoluteValue": -342,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
},
{
"absoluteValue": 0,
"dimensionName": "VerificationProgress",
"unit": "Percent"
}
],
"recommendationReason": "Duplicate",
"score": 1,
"state": {
"actionInitiatedBy": "User",
"currentValue": "Success",
"lastModified": "2017-03-01T14:38:05Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:05Z"
},
"type": "Microsoft.Sql/servers/advisors/recommendedActions"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/DropIndex/recommendedActions/IR_[dbo]_[FactFinance]_13ADA5F354E9E14A983B",
"kind": "",
"location": "East Asia",
"name": "IR_[dbo]_[FactFinance]_13ADA5F354E9E14A983B",
"properties": {
"details": {
"databaseName": "IndexAdvisor_test_3",
"includedColumns": "[OrganizationKey]",
"indexActionDuration": "00:01:00",
"indexActionStartTime": "2017-03-01T14:38:05.837",
"indexColumns": "[FinanceKey],[DateKey]",
"indexName": "IX_FF",
"indexType": "NONCLUSTERED",
"originalIndexName": "IX_COM_SKU_SKUDepartmentID",
"schema": "[dbo]",
"table": "[FactFinance]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 705,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 342,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"executeActionDuration": "PT1M",
"executeActionInitiatedBy": "System",
"executeActionInitiatedTime": "2017-03-01T14:38:05Z",
"executeActionStartTime": "2017-03-01T14:38:05Z",
"implementationDetails": {
"method": "TSql",
"script": "CREATE NONCLUSTERED INDEX [IX_FF] ON [dbo].[FactFinance] ([FinanceKey],[DateKey]) INCLUDE ([OrganizationKey]) WITH (ONLINE = ON)"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:05Z",
"observedImpact": [
{
"changeValueAbsolute": 0,
"changeValueRelative": 0,
"dimensionName": "AffectedQueriesCpuUtilization",
"unit": "CpuCores"
},
{
"changeValueAbsolute": 0,
"changeValueRelative": 0,
"dimensionName": "CpuUtilization",
"unit": "CpuCores"
},
{
"absoluteValue": 0,
"dimensionName": "QueriesWithImprovedPerformance",
"unit": "Count"
},
{
"absoluteValue": 0,
"dimensionName": "QueriesWithRegressedPerformance",
"unit": "Count"
},
{
"absoluteValue": -342,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
},
{
"absoluteValue": 0,
"dimensionName": "VerificationProgress",
"unit": "Percent"
}
],
"recommendationReason": "Duplicate",
"score": 1,
"state": {
"actionInitiatedBy": "System",
"currentValue": "Success",
"lastModified": "2017-02-28T14:38:05Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:05Z"
},
"type": "Microsoft.Sql/servers/advisors/recommendedActions"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/DropIndex/recommendedActions/IR_[CRM]_[DataPoints1]_13ADA5F354E9E14A983B",
"kind": "",
"location": "East Asia",
"name": "IR_[CRM]_[DataPoints1]_13ADA5F354E9E14A983B",
"properties": {
"details": {
"databaseName": "IndexAdvisor_test_3",
"includedColumns": "[Apple]",
"indexColumns": "[Protocol],[SessionId]",
"indexName": "IX_FF",
"indexType": "NONCLUSTERED",
"originalIndexName": "IX_COM_SKU_SKUDepartmentID",
"schema": "[CRM]",
"table": "[DataPoints1]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 645,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 342,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"executeActionInitiatedBy": "System",
"executeActionInitiatedTime": "2017-03-23T14:14:35Z",
"implementationDetails": {
"method": "TSql",
"script": "DROP INDEX [IX_FF] ON [CRM].[DataPoints1]"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:05Z",
"observedImpact": [],
"recommendationReason": "Unused",
"score": 1,
"state": {
"actionInitiatedBy": "System",
"currentValue": "Pending",
"lastModified": "2017-03-23T14:14:35Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:05Z"
},
"type": "Microsoft.Sql/servers/advisors/recommendedActions"
}
]
},
"type": "Microsoft.Sql/servers/advisors"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/DbParameterization",
"kind": "",
"location": "East Asia",
"name": "DbParameterization",
"properties": {
"advisorStatus": "GA",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Default",
"recommendedActions": [
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/DbParameterization/recommendedActions/ForceDbParameterization",
"kind": "",
"location": "East Asia",
"name": "ForceDbParameterization",
"properties": {
"details": {
"databaseName": "IndexAdvisor_test_3"
},
"errorDetails": {},
"estimatedImpact": [
{
"changeValueAbsolute": 22.5613696939135,
"dimensionName": "CpuSavings",
"unit": "Percent"
},
{
"changeValueAbsolute": 0.701823681806341,
"dimensionName": "QueryDurationDecrease",
"unit": "secs"
}
],
"executeActionInitiatedBy": "System",
"executeActionInitiatedTime": "2017-05-11T15:08:31Z",
"implementationDetails": {
"method": "TSql",
"script": "ALTER DATABASE [IndexAdvisor_test_3] SET PARAMETERIZATION FORCED"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:37:44Z",
"observedImpact": [],
"recommendationReason": "",
"score": 3,
"state": {
"actionInitiatedBy": "System",
"currentValue": "Pending",
"lastModified": "2017-05-11T15:08:31Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:37:44Z"
},
"type": "Microsoft.Sql/servers/advisors/recommendedActions"
}
]
},
"type": "Microsoft.Sql/servers/advisors"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/SchemaIssue",
"kind": "",
"location": "East Asia",
"name": "SchemaIssue",
"properties": {
"advisorStatus": "PublicPreview",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Default",
"recommendedActions": [
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/SchemaIssue/recommendedActions/SchemaProblem_1A258C5714A7410C9D23",
"kind": "",
"location": "East Asia",
"name": "SchemaProblem_1A258C5714A7410C9D23",
"properties": {
"details": {
"databaseName": "IndexAdvisor_test_3",
"sqlErrorCount": 342482,
"sqlErrorMessage": "Invalid object name 'dbo.Companies'.",
"sqlErrorNumber": 208
},
"errorDetails": {},
"estimatedImpact": [],
"implementationDetails": {},
"isArchivedAction": false,
"isExecutableAction": false,
"isRevertableAction": false,
"lastRefresh": "2017-06-07T22:19:53Z",
"observedImpact": [],
"recommendationReason": "SchemaProblem",
"score": 3,
"state": {
"currentValue": "Active",
"lastModified": "2017-03-01T14:37:48Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:37:48Z"
},
"type": "Microsoft.Sql/servers/advisors/recommendedActions"
}
]
},
"type": "Microsoft.Sql/servers/advisors"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/ForceLastGoodPlan",
"kind": "",
"location": "East Asia",
"name": "ForceLastGoodPlan",
"properties": {
"advisorStatus": "PrivatePreview",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Default",
"recommendedActions": []
},
"type": "Microsoft.Sql/servers/advisors"
}
]
}
}
}
}
}
},
"/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/advisors/{advisorName}": {
"get": {
"description": "Gets a server advisor.",
"operationId": "ServerAdvisors_Get",
"parameters": [
{
"$ref": "#/parameters/ResourceGroupParameter"
},
{
"$ref": "#/parameters/ServerNameParameter"
},
{
"description": "The name of the Server Advisor.",
"in": "path",
"name": "advisorName",
"required": true,
"type": "string"
},
{
"$ref": "#/parameters/SubscriptionIdParameter"
},
{
"$ref": "#/parameters/ApiVersionParameter"
}
],
"responses": {
"200": {
"description": "Successfully retrieved details of specified server advisor.",
"schema": {
"$ref": "#/definitions/Advisor"
}
},
"default": {
"description": "*** Error Responses: ***\n\n * 400 InvalidIdentifier - The identifier contains NULL or an invalid unicode character.\n\n * 404 SubscriptionDoesNotHaveServer - The requested server was not found\n\n * 404 DatabaseDoesNotExist - The requested database was not found\n\n * 404 SubscriptionDoesNotHaveElasticPool - The requested elastic pool was not found\n\n * 404 AdvisorNotFound - The requested advisor was not found.\n\n * 405 NotSupported - This functionality is not supported.\n\n * 409 Conflict - Request could not be processed because of conflict in the request.\n\n * 503 ServiceTemporarilyUnavailable - Feature temporarily unavailable."
}
},
"tags": [
"ServerAdvisors"
],
"x-ms-examples": {
"Get server advisor": {
"parameters": {
"advisorName": "CreateIndex",
"api-version": "2015-05-01-preview",
"resourceGroupName": "workloadinsight-demos",
"serverName": "misosisvr",
"subscriptionId": "00000000-1111-2222-3333-444444444444"
},
"responses": {
"200": {
"body": {
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/CreateIndex",
"kind": "",
"location": "East Asia",
"name": "CreateIndex",
"properties": {
"advisorStatus": "GA",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Default"
},
"type": "Microsoft.Sql/servers/advisors"
}
}
}
}
}
},
"patch": {
"description": "Updates a server advisor.",
"operationId": "ServerAdvisors_Update",
"parameters": [
{
"$ref": "#/parameters/ResourceGroupParameter"
},
{
"$ref": "#/parameters/ServerNameParameter"
},
{
"description": "The name of the Server Advisor.",
"in": "path",
"name": "advisorName",
"required": true,
"type": "string"
},
{
"description": "The requested advisor resource state.",
"in": "body",
"name": "parameters",
"required": true,
"schema": {
"$ref": "#/definitions/Advisor"
}
},
{
"$ref": "#/parameters/SubscriptionIdParameter"
},
{
"$ref": "#/parameters/ApiVersionParameter"
}
],
"responses": {
"200": {
"description": "Successfully updated the specified server advisor.",
"schema": {
"$ref": "#/definitions/Advisor"
}
},
"default": {
"description": "*** Error Responses: ***\n\n * 400 InvalidIdentifier - The identifier contains NULL or an invalid unicode character.\n\n * 400 InvalidAdvisorUpsertRequest - The update advisor request body does not exist or has no properties object.\n\n * 400 InvalidAdvisorAutoExecuteStatus - Specified auto-execute status for the advisor is not allowed.\n\n * 404 SubscriptionDoesNotHaveServer - The requested server was not found\n\n * 404 DatabaseDoesNotExist - The requested database was not found\n\n * 404 SubscriptionDoesNotHaveElasticPool - The requested elastic pool was not found\n\n * 404 AdvisorNotFound - The requested advisor was not found.\n\n * 405 NotSupported - This functionality is not supported.\n\n * 409 Conflict - Request could not be processed because of conflict in the request.\n\n * 503 ServiceTemporarilyUnavailable - Feature temporarily unavailable."
}
},
"tags": [
"ServerAdvisors"
],
"x-ms-examples": {
"Update server advisor": {
"parameters": {
"advisorName": "CreateIndex",
"api-version": "2015-05-01-preview",
"parameters": {
"properties": {
"autoExecuteStatus": "Disabled"
}
},
"resourceGroupName": "workloadinsight-demos",
"serverName": "misosisvr",
"subscriptionId": "00000000-1111-2222-3333-444444444444"
},
"responses": {
"200": {
"body": {
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/advisors/CreateIndex",
"kind": "",
"location": "East Asia",
"name": "CreateIndex",
"properties": {
"advisorStatus": "GA",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Server"
},
"type": "Microsoft.Sql/servers/advisors"
}
}
}
}
}
}
},
"/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}/advisors": {
"get": {
"description": "Gets a list of database advisors.",
"operationId": "DatabaseAdvisors_ListByDatabase",
"parameters": [
{
"$ref": "#/parameters/ResourceGroupParameter"
},
{
"$ref": "#/parameters/ServerNameParameter"
},
{
"$ref": "#/parameters/DatabaseNameParameter"
},
{
"$ref": "#/parameters/SubscriptionIdParameter"
},
{
"$ref": "#/parameters/ApiVersionParameter"
}
],
"responses": {
"200": {
"description": "Successfully retrieved the list of database advisors.",
"schema": {
"items": {
"$ref": "#/definitions/Advisor"
},
"type": "array"
}
},
"default": {
"description": "*** Error Responses: ***\n\n * 400 InvalidIdentifier - The identifier contains NULL or an invalid unicode character.\n\n * 404 SubscriptionDoesNotHaveServer - The requested server was not found\n\n * 404 DatabaseDoesNotExist - The requested database was not found\n\n * 404 SubscriptionDoesNotHaveElasticPool - The requested elastic pool was not found\n\n * 404 AdvisorNotFound - The requested advisor was not found.\n\n * 405 NotSupported - This functionality is not supported.\n\n * 409 Conflict - Request could not be processed because of conflict in the request.\n\n * 503 ServiceTemporarilyUnavailable - Feature temporarily unavailable."
}
},
"tags": [
"DatabaseAdvisors"
],
"x-ms-examples": {
"List of database advisors": {
"parameters": {
"api-version": "2015-05-01-preview",
"databaseName": "IndexAdvisor_test_3",
"resourceGroupName": "workloadinsight-demos",
"serverName": "misosisvr",
"subscriptionId": "00000000-1111-2222-3333-444444444444"
},
"responses": {
"200": {
"body": [
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/CreateIndex",
"kind": "",
"location": "East Asia",
"name": "CreateIndex",
"properties": {
"advisorStatus": "GA",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Database",
"lastChecked": "2017-06-20T00:24:39Z",
"recommendationsStatus": "Ok"
},
"type": "Microsoft.Sql/servers/databases/advisors"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/DropIndex",
"kind": "",
"location": "East Asia",
"name": "DropIndex",
"properties": {
"advisorStatus": "GA",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Database",
"lastChecked": "2017-06-19T20:42:43Z",
"recommendationsStatus": "DbSeemsTuned"
},
"type": "Microsoft.Sql/servers/databases/advisors"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/DbParameterization",
"kind": "",
"location": "East Asia",
"name": "DbParameterization",
"properties": {
"advisorStatus": "GA",
"autoExecuteStatus": "Enabled",
"autoExecuteStatusInheritedFrom": "Database",
"lastChecked": "2017-06-19T19:13:03Z",
"recommendationsStatus": "DbParameterizationIssue"
},
"type": "Microsoft.Sql/servers/databases/advisors"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/SchemaIssue",
"kind": "",
"location": "East Asia",
"name": "SchemaIssue",
"properties": {
"advisorStatus": "PublicPreview",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Default",
"lastChecked": "2017-06-20T14:00:32Z",
"recommendationsStatus": "SchemaIsNotConsistent"
},
"type": "Microsoft.Sql/servers/databases/advisors"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/ForceLastGoodPlan",
"kind": "",
"location": "East Asia",
"name": "ForceLastGoodPlan",
"properties": {
"advisorStatus": "PrivatePreview",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Default"
},
"type": "Microsoft.Sql/servers/databases/advisors"
}
]
}
}
},
"List of database recommended actions for all advisors": {
"parameters": {
"$expand": "recommendedActions",
"advisorName": "CreateIndex",
"api-version": "2015-05-01-preview",
"databaseName": "IndexAdvisor_test_3",
"resourceGroupName": "workloadinsight-demos",
"serverName": "misosisvr",
"subscriptionId": "00000000-1111-2222-3333-444444444444"
},
"responses": {
"200": {
"body": [
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/CreateIndex",
"kind": "",
"location": "East Asia",
"name": "CreateIndex",
"properties": {
"advisorStatus": "GA",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Database",
"lastChecked": "2017-06-20T16:39:16Z",
"recommendationsStatus": "Ok",
"recommendedActions": [
{
"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",
"kind": "",
"location": "East Asia",
"name": "IR_[CRM]_[DataPoints]_4821CD2F9510D98184BB",
"properties": {
"details": {
"includedColumns": "[Hour], [System], [LastChanged]",
"indexColumns": "[Name],[Money],[Power]",
"indexName": "nci_wi_DataPoints_B892614093BAC56295EF6018BD4CB51B",
"indexType": "NONCLUSTERED",
"schema": "[CRM]",
"table": "[DataPoints]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 1440,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 209.3125,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"implementationDetails": {
"method": "TSql",
"script": "CREATE NONCLUSTERED INDEX [nci_wi_DataPoints_B892614093BAC56295EF6018BD4CB51B] ON [CRM].[DataPoints] ([Name],[Money],[Power]) INCLUDE ([Hour], [System], [LastChanged]) WITH (ONLINE = ON)"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:04Z",
"observedImpact": [],
"recommendationReason": "",
"score": 1,
"state": {
"currentValue": "Active",
"lastModified": "2017-06-20T15:15:40Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:04Z"
},
"type": "Microsoft.Sql/servers/databases/advisors/recommendedActions"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/CreateIndex/recommendedActions/IR_[dbo]_[DataPoints]_F5D2F347AA22DB46E4CC",
"kind": "",
"location": "East Asia",
"name": "IR_[dbo]_[DataPoints]_F5D2F347AA22DB46E4CC",
"properties": {
"details": {
"includedColumns": "[Power],[Pineapple]",
"indexActionDuration": "00:01:00",
"indexActionStartTime": "2017-03-01T14:38:05.337",
"indexColumns": "[Name],[Money]",
"indexName": "nci_wi_DataPoints_609E4B7D6A3813990ED44B28B340C8FC",
"indexType": "NONCLUSTERED",
"schema": "[dbo]",
"table": "[DataPoints]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 5040,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 120,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"executeActionDuration": "PT1M",
"executeActionInitiatedBy": "User",
"executeActionInitiatedTime": "2017-03-01T14:38:05Z",
"executeActionStartTime": "2017-03-01T14:38:05Z",
"implementationDetails": {
"method": "TSql",
"script": "DROP INDEX [nci_wi_DataPoints_609E4B7D6A3813990ED44B28B340C8FC] ON [dbo].[DataPoints]"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:05Z",
"observedImpact": [
{
"changeValueAbsolute": -12.7,
"changeValueRelative": -0.9,
"dimensionName": "AffectedQueriesCpuUtilization",
"unit": "CpuCores"
},
{
"changeValueAbsolute": -12.7,
"changeValueRelative": -0.3175,
"dimensionName": "CpuUtilization",
"unit": "CpuCores"
},
{
"absoluteValue": 12,
"dimensionName": "QueriesWithImprovedPerformance",
"unit": "Count"
},
{
"absoluteValue": 1,
"dimensionName": "QueriesWithRegressedPerformance",
"unit": "Count"
},
{
"absoluteValue": 130.742187,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
},
{
"absoluteValue": 0,
"dimensionName": "VerificationProgress",
"unit": "Percent"
}
],
"recommendationReason": "",
"score": 3,
"state": {
"actionInitiatedBy": "User",
"currentValue": "Success",
"lastModified": "2017-03-01T14:38:05Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:05Z"
},
"type": "Microsoft.Sql/servers/databases/advisors/recommendedActions"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/CreateIndex/recommendedActions/IR_[dbo]_[Employees]_560E15A98D14CA09BDFB",
"kind": "",
"location": "East Asia",
"name": "IR_[dbo]_[Employees]_560E15A98D14CA09BDFB",
"properties": {
"details": {
"includedColumns": "[Postal]",
"indexColumns": "[City], [State]",
"indexName": "nci_wi_Employees_8C18C2AF4267DC77793040782641CCDE",
"indexType": "NONCLUSTERED",
"schema": "[dbo]",
"table": "[Employees]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 17,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 128,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"implementationDetails": {
"method": "TSql",
"script": "CREATE NONCLUSTERED INDEX [nci_wi_Employees_8C18C2AF4267DC77793040782641CCDE] ON [dbo].[Employees] ([City], [State]) INCLUDE ([Postal]) WITH (ONLINE = ON)"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:05Z",
"observedImpact": [],
"recommendationReason": "",
"score": 3,
"state": {
"currentValue": "Active",
"lastModified": "2017-03-01T14:38:05Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:05Z"
},
"type": "Microsoft.Sql/servers/databases/advisors/recommendedActions"
}
]
},
"type": "Microsoft.Sql/servers/databases/advisors"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/DropIndex",
"kind": "",
"location": "East Asia",
"name": "DropIndex",
"properties": {
"advisorStatus": "GA",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Database",
"lastChecked": "2017-06-19T20:42:43Z",
"recommendationsStatus": "DbSeemsTuned",
"recommendedActions": [
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/DropIndex/recommendedActions/IR_[CRM]_[DataPoints1]_13ADA5F354E9E14A983B",
"kind": "",
"location": "East Asia",
"name": "IR_[CRM]_[DataPoints1]_13ADA5F354E9E14A983B",
"properties": {
"details": {
"includedColumns": "[Apple]",
"indexColumns": "[Protocol],[SessionId]",
"indexName": "IX_FF",
"indexType": "NONCLUSTERED",
"originalIndexName": "IX_COM_SKU_SKUDepartmentID",
"schema": "[CRM]",
"table": "[DataPoints1]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 645,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 342,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"executeActionInitiatedBy": "System",
"executeActionInitiatedTime": "2017-03-23T14:14:35Z",
"implementationDetails": {
"method": "TSql",
"script": "DROP INDEX [IX_FF] ON [CRM].[DataPoints1]"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:05Z",
"observedImpact": [],
"recommendationReason": "Unused",
"score": 1,
"state": {
"actionInitiatedBy": "System",
"currentValue": "Pending",
"lastModified": "2017-03-23T14:14:35Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:05Z"
},
"type": "Microsoft.Sql/servers/databases/advisors/recommendedActions"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/DropIndex/recommendedActions/IR_[CRM]_[DataPoints1]_29AEA82685D24704DE1A",
"kind": "",
"location": "East Asia",
"name": "IR_[CRM]_[DataPoints1]_29AEA82685D24704DE1A",
"properties": {
"details": {
"includedColumns": "[Apple]",
"indexColumns": "[Cookies],[SessionId]",
"indexName": "MyIndex123",
"indexType": "NONCLUSTERED",
"originalIndexName": "IX_COM_SKU_SKUDepartmentID",
"schema": "[CRM]",
"table": "[DataPoints1]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 803,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 144.6875,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"executeActionInitiatedBy": "System",
"executeActionInitiatedTime": "2017-03-01T14:38:05Z",
"implementationDetails": {
"method": "TSql",
"script": "DROP INDEX [MyIndex123] ON [CRM].[DataPoints1]"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:05Z",
"observedImpact": [],
"recommendationReason": "Duplicate",
"score": 1,
"state": {
"actionInitiatedBy": "System",
"currentValue": "Pending",
"lastModified": "2017-03-01T14:38:05Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:05Z"
},
"type": "Microsoft.Sql/servers/databases/advisors/recommendedActions"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/DropIndex/recommendedActions/IR_[CRM]_[DataPoints2]_E4B21F229379807E531A",
"kind": "",
"location": "East Asia",
"name": "IR_[CRM]_[DataPoints2]_E4B21F229379807E531A",
"properties": {
"details": {
"includedColumns": "[Apple]",
"indexActionDuration": "00:01:00",
"indexActionStartTime": "2017-03-01T14:38:05.697",
"indexColumns": "[Cookies],[SessionId],[Protocol]",
"indexName": "MyIndex321",
"indexType": "NONCLUSTERED",
"originalIndexName": "IX_COM_SKU_SKUDepartmentID",
"schema": "[CRM]",
"table": "[DataPoints2]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 705,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 342,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"executeActionDuration": "PT1M",
"executeActionInitiatedBy": "User",
"executeActionInitiatedTime": "2017-03-01T14:38:05Z",
"executeActionStartTime": "2017-03-01T14:38:05Z",
"implementationDetails": {
"method": "TSql",
"script": "CREATE NONCLUSTERED INDEX [MyIndex321] ON [CRM].[DataPoints2] ([Cookies],[SessionId],[Protocol]) INCLUDE ([Apple]) WITH (ONLINE = ON)"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:05Z",
"observedImpact": [
{
"changeValueAbsolute": 0,
"changeValueRelative": 0,
"dimensionName": "AffectedQueriesCpuUtilization",
"unit": "CpuCores"
},
{
"changeValueAbsolute": 0,
"changeValueRelative": 0,
"dimensionName": "CpuUtilization",
"unit": "CpuCores"
},
{
"absoluteValue": 0,
"dimensionName": "QueriesWithImprovedPerformance",
"unit": "Count"
},
{
"absoluteValue": 0,
"dimensionName": "QueriesWithRegressedPerformance",
"unit": "Count"
},
{
"absoluteValue": -342,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
},
{
"absoluteValue": 0,
"dimensionName": "VerificationProgress",
"unit": "Percent"
}
],
"recommendationReason": "Duplicate",
"score": 1,
"state": {
"actionInitiatedBy": "User",
"currentValue": "Success",
"lastModified": "2017-03-01T14:38:05Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:05Z"
},
"type": "Microsoft.Sql/servers/databases/advisors/recommendedActions"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/DropIndex/recommendedActions/IR_[dbo]_[FactFinance]_13ADA5F354E9E14A983B",
"kind": "",
"location": "East Asia",
"name": "IR_[dbo]_[FactFinance]_13ADA5F354E9E14A983B",
"properties": {
"details": {
"includedColumns": "[OrganizationKey]",
"indexActionDuration": "00:01:00",
"indexActionStartTime": "2017-03-01T14:38:05.837",
"indexColumns": "[FinanceKey],[DateKey]",
"indexName": "IX_FF",
"indexType": "NONCLUSTERED",
"originalIndexName": "IX_COM_SKU_SKUDepartmentID",
"schema": "[dbo]",
"table": "[FactFinance]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 705,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 342,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"executeActionDuration": "PT1M",
"executeActionInitiatedBy": "System",
"executeActionInitiatedTime": "2017-03-01T14:38:05Z",
"executeActionStartTime": "2017-03-01T14:38:05Z",
"implementationDetails": {
"method": "TSql",
"script": "CREATE NONCLUSTERED INDEX [IX_FF] ON [dbo].[FactFinance] ([FinanceKey],[DateKey]) INCLUDE ([OrganizationKey]) WITH (ONLINE = ON)"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:05Z",
"observedImpact": [
{
"changeValueAbsolute": 0,
"changeValueRelative": 0,
"dimensionName": "AffectedQueriesCpuUtilization",
"unit": "CpuCores"
},
{
"changeValueAbsolute": 0,
"changeValueRelative": 0,
"dimensionName": "CpuUtilization",
"unit": "CpuCores"
},
{
"absoluteValue": 0,
"dimensionName": "QueriesWithImprovedPerformance",
"unit": "Count"
},
{
"absoluteValue": 0,
"dimensionName": "QueriesWithRegressedPerformance",
"unit": "Count"
},
{
"absoluteValue": -342,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
},
{
"absoluteValue": 0,
"dimensionName": "VerificationProgress",
"unit": "Percent"
}
],
"recommendationReason": "Duplicate",
"score": 1,
"state": {
"actionInitiatedBy": "System",
"currentValue": "Success",
"lastModified": "2017-02-28T14:38:05Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:05Z"
},
"type": "Microsoft.Sql/servers/databases/advisors/recommendedActions"
}
]
},
"type": "Microsoft.Sql/servers/databases/advisors"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/DbParameterization",
"kind": "",
"location": "East Asia",
"name": "DbParameterization",
"properties": {
"advisorStatus": "GA",
"autoExecuteStatus": "Enabled",
"autoExecuteStatusInheritedFrom": "Database",
"lastChecked": "2017-06-19T19:13:03Z",
"recommendationsStatus": "DbParameterizationIssue",
"recommendedActions": [
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/DbParameterization/recommendedActions/ForceDbParameterization",
"kind": "",
"location": "East Asia",
"name": "ForceDbParameterization",
"properties": {
"errorDetails": {},
"estimatedImpact": [
{
"changeValueAbsolute": 22.5613696939135,
"dimensionName": "CpuSavings",
"unit": "Percent"
},
{
"changeValueAbsolute": 0.701823681806341,
"dimensionName": "QueryDurationDecrease",
"unit": "secs"
}
],
"executeActionInitiatedBy": "System",
"executeActionInitiatedTime": "2017-05-11T15:08:31Z",
"implementationDetails": {
"method": "TSql",
"script": "ALTER DATABASE [IndexAdvisor_test_3] SET PARAMETERIZATION FORCED"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:37:44Z",
"observedImpact": [],
"recommendationReason": "",
"score": 3,
"state": {
"actionInitiatedBy": "System",
"currentValue": "Pending",
"lastModified": "2017-05-11T15:08:31Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:37:44Z"
},
"type": "Microsoft.Sql/servers/databases/advisors/recommendedActions"
}
]
},
"type": "Microsoft.Sql/servers/databases/advisors"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/SchemaIssue",
"kind": "",
"location": "East Asia",
"name": "SchemaIssue",
"properties": {
"advisorStatus": "PublicPreview",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Default",
"lastChecked": "2017-06-20T18:50:21Z",
"recommendationsStatus": "SchemaIsNotConsistent",
"recommendedActions": [
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/SchemaIssue/recommendedActions/SchemaProblem_1A258C5714A7410C9D23",
"kind": "",
"location": "East Asia",
"name": "SchemaProblem_1A258C5714A7410C9D23",
"properties": {
"details": {
"sqlErrorCount": 342482,
"sqlErrorMessage": "Invalid object name 'dbo.Companies'.",
"sqlErrorNumber": 208
},
"errorDetails": {},
"estimatedImpact": [],
"implementationDetails": {},
"isArchivedAction": false,
"isExecutableAction": false,
"isRevertableAction": false,
"lastRefresh": "2017-06-07T22:19:53Z",
"observedImpact": [],
"recommendationReason": "SchemaProblem",
"score": 3,
"state": {
"currentValue": "Active",
"lastModified": "2017-03-01T14:37:48Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:37:48Z"
},
"type": "Microsoft.Sql/servers/databases/advisors/recommendedActions"
}
]
},
"type": "Microsoft.Sql/servers/databases/advisors"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/ForceLastGoodPlan",
"kind": "",
"location": "East Asia",
"name": "ForceLastGoodPlan",
"properties": {
"advisorStatus": "PrivatePreview",
"autoExecuteStatus": "Disabled",
"autoExecuteStatusInheritedFrom": "Default",
"recommendedActions": []
},
"type": "Microsoft.Sql/servers/databases/advisors"
}
]
}
}
}
}
}
},
"/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}/advisors/{advisorName}": {
"get": {
"description": "Gets a database advisor.",
"operationId": "DatabaseAdvisors_Get",
"parameters": [
{
"$ref": "#/parameters/ResourceGroupParameter"
},
{
"$ref": "#/parameters/ServerNameParameter"
},
{
"$ref": "#/parameters/DatabaseNameParameter"
},
{
"description": "The name of the Database Advisor.",
"in": "path",
"name": "advisorName",
"required": true,
"type": "string"
},
{
"$ref": "#/parameters/SubscriptionIdParameter"
},
{
"$ref": "#/parameters/ApiVersionParameter"
}
],
"responses": {
"200": {
"description": "Successfully retrieved details of specified database advisor.",
"schema": {
"$ref": "#/definitions/Advisor"
}
},
"default": {
"description": "*** Error Responses: ***\n\n * 400 InvalidIdentifier - The identifier contains NULL or an invalid unicode character.\n\n * 404 SubscriptionDoesNotHaveServer - The requested server was not found\n\n * 404 DatabaseDoesNotExist - The requested database was not found\n\n * 404 SubscriptionDoesNotHaveElasticPool - The requested elastic pool was not found\n\n * 404 AdvisorNotFound - The requested advisor was not found.\n\n * 405 NotSupported - This functionality is not supported.\n\n * 409 Conflict - Request could not be processed because of conflict in the request.\n\n * 503 ServiceTemporarilyUnavailable - Feature temporarily unavailable."
}
},
"tags": [
"DatabaseAdvisors"
],
"x-ms-examples": {
"Get database advisor": {
"parameters": {
"advisorName": "CreateIndex",
"api-version": "2015-05-01-preview",
"databaseName": "IndexAdvisor_test_3",
"resourceGroupName": "workloadinsight-demos",
"serverName": "misosisvr",
"subscriptionId": "00000000-1111-2222-3333-444444444444"
},
"responses": {
"200": {
"body": {
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/CreateIndex",
"name": "CreateIndex",
"properties": {
"advisorStatus": "GA",
"autoExecuteStatus": "Disabled",
"lastChecked": "2017-06-20T00:24:39Z",
"recommendationsStatus": "Ok"
},
"type": "Microsoft.Sql/servers/databases/advisors"
}
}
}
}
}
},
"patch": {
"description": "Updates a database advisor.",
"operationId": "DatabaseAdvisors_Update",
"parameters": [
{
"$ref": "#/parameters/ResourceGroupParameter"
},
{
"$ref": "#/parameters/ServerNameParameter"
},
{
"$ref": "#/parameters/DatabaseNameParameter"
},
{
"description": "The name of the Database Advisor.",
"in": "path",
"name": "advisorName",
"required": true,
"type": "string"
},
{
"description": "The requested advisor resource state.",
"in": "body",
"name": "parameters",
"required": true,
"schema": {
"$ref": "#/definitions/Advisor"
}
},
{
"$ref": "#/parameters/SubscriptionIdParameter"
},
{
"$ref": "#/parameters/ApiVersionParameter"
}
],
"responses": {
"200": {
"description": "Successfully updated the specified database advisor.",
"schema": {
"$ref": "#/definitions/Advisor"
}
},
"default": {
"description": "*** Error Responses: ***\n\n * 400 InvalidIdentifier - The identifier contains NULL or an invalid unicode character.\n\n * 400 InvalidAdvisorUpsertRequest - The update advisor request body does not exist or has no properties object.\n\n * 400 InvalidAdvisorAutoExecuteStatus - Specified auto-execute status for the advisor is not allowed.\n\n * 404 SubscriptionDoesNotHaveServer - The requested server was not found\n\n * 404 DatabaseDoesNotExist - The requested database was not found\n\n * 404 SubscriptionDoesNotHaveElasticPool - The requested elastic pool was not found\n\n * 404 AdvisorNotFound - The requested advisor was not found.\n\n * 405 NotSupported - This functionality is not supported.\n\n * 409 Conflict - Request could not be processed because of conflict in the request.\n\n * 503 ServiceTemporarilyUnavailable - Feature temporarily unavailable."
}
},
"tags": [
"DatabaseAdvisors"
],
"x-ms-examples": {
"Update database advisor": {
"parameters": {
"advisorName": "CreateIndex",
"api-version": "2015-05-01-preview",
"databaseName": "IndexAdvisor_test_3",
"parameters": {
"properties": {
"autoExecuteStatus": "Disabled"
}
},
"resourceGroupName": "workloadinsight-demos",
"serverName": "misosisvr",
"subscriptionId": "00000000-1111-2222-3333-444444444444"
},
"responses": {
"200": {
"body": {
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/CreateIndex",
"name": "CreateIndex",
"properties": {
"advisorStatus": "GA",
"autoExecuteStatus": "Disabled",
"lastChecked": "2017-06-20T00:24:39Z",
"recommendationsStatus": "Ok"
},
"type": "Microsoft.Sql/servers/databases/advisors"
}
}
}
}
}
}
},
"/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}/advisors/{advisorName}/recommendedActions": {
"get": {
"description": "Gets list of Database Recommended Actions.",
"operationId": "DatabaseRecommendedActions_ListByDatabaseAdvisor",
"parameters": [
{
"$ref": "#/parameters/ResourceGroupParameter"
},
{
"$ref": "#/parameters/ServerNameParameter"
},
{
"$ref": "#/parameters/DatabaseNameParameter"
},
{
"description": "The name of the Database Advisor.",
"in": "path",
"name": "advisorName",
"required": true,
"type": "string"
},
{
"$ref": "#/parameters/SubscriptionIdParameter"
},
{
"$ref": "#/parameters/ApiVersionParameter"
}
],
"responses": {
"200": {
"description": "Successfully retrieved the list of database recommended actions.",
"schema": {
"items": {
"$ref": "#/definitions/RecommendedAction"
},
"type": "array"
}
},
"default": {
"description": "*** Error Responses: ***\n\n * 400 InvalidIdentifier - The identifier contains NULL or an invalid unicode character.\n\n * 404 SubscriptionDoesNotHaveServer - The requested server was not found\n\n * 404 DatabaseDoesNotExist - The requested database was not found\n\n * 404 SubscriptionDoesNotHaveElasticPool - The requested elastic pool was not found\n\n * 404 AdvisorNotFound - The requested advisor was not found.\n\n * 404 RecommendedActionNotFound - The requested recommended action was not found.\n\n * 404 AdvisorNotFound - The requested advisor was not found.\n\n * 404 RecommendedActionNotFound - The requested recommended action was not found.\n\n * 405 NotSupported - This functionality is not supported.\n\n * 409 Conflict - Request could not be processed because of conflict in the request.\n\n * 503 ServiceTemporarilyUnavailable - Feature temporarily unavailable."
}
},
"tags": [
"DatabaseRecommendedActions"
],
"x-ms-examples": {
"List of database recommended actions": {
"parameters": {
"advisorName": "CreateIndex",
"api-version": "2015-05-01-preview",
"databaseName": "IndexAdvisor_test_3",
"resourceGroupName": "workloadinsight-demos",
"serverName": "misosisvr",
"subscriptionId": "00000000-1111-2222-3333-444444444444"
},
"responses": {
"200": {
"body": [
{
"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",
"kind": "",
"location": "East Asia",
"name": "IR_[CRM]_[DataPoints]_4821CD2F9510D98184BB",
"properties": {
"details": {
"includedColumns": "[Hour], [System], [LastChanged]",
"indexColumns": "[Name],[Money],[Power]",
"indexName": "nci_wi_DataPoints_B892614093BAC56295EF6018BD4CB51B",
"indexType": "NONCLUSTERED",
"schema": "[CRM]",
"table": "[DataPoints]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 1440,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 209.3125,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"implementationDetails": {
"method": "TSql",
"script": "CREATE NONCLUSTERED INDEX [nci_wi_DataPoints_B892614093BAC56295EF6018BD4CB51B] ON [CRM].[DataPoints] ([Name],[Money],[Power]) INCLUDE ([Hour], [System], [LastChanged]) WITH (ONLINE = ON)"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:04Z",
"observedImpact": [],
"recommendationReason": "",
"score": 1,
"state": {
"currentValue": "Active",
"lastModified": "2017-03-01T14:38:05Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:04Z"
},
"type": "Microsoft.Sql/servers/databases/advisors/recommendedActions"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/CreateIndex/recommendedActions/IR_[dbo]_[DataPoints]_F5D2F347AA22DB46E4CC",
"kind": "",
"location": "East Asia",
"name": "IR_[dbo]_[DataPoints]_F5D2F347AA22DB46E4CC",
"properties": {
"details": {
"includedColumns": "[Power],[Pineapple]",
"indexActionDuration": "00:01:00",
"indexActionStartTime": "2017-03-01T14:38:05.337",
"indexColumns": "[Name],[Money]",
"indexName": "nci_wi_DataPoints_609E4B7D6A3813990ED44B28B340C8FC",
"indexType": "NONCLUSTERED",
"schema": "[dbo]",
"table": "[DataPoints]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 5040,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 120,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"executeActionDuration": "PT1M",
"executeActionInitiatedBy": "User",
"executeActionInitiatedTime": "2017-03-01T14:38:05Z",
"executeActionStartTime": "2017-03-01T14:38:05Z",
"implementationDetails": {
"method": "TSql",
"script": "DROP INDEX [nci_wi_DataPoints_609E4B7D6A3813990ED44B28B340C8FC] ON [dbo].[DataPoints]"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:05Z",
"observedImpact": [
{
"changeValueAbsolute": -12.7,
"changeValueRelative": -0.9,
"dimensionName": "AffectedQueriesCpuUtilization",
"unit": "CpuCores"
},
{
"changeValueAbsolute": -12.7,
"changeValueRelative": -0.3175,
"dimensionName": "CpuUtilization",
"unit": "CpuCores"
},
{
"absoluteValue": 12,
"dimensionName": "QueriesWithImprovedPerformance",
"unit": "Count"
},
{
"absoluteValue": 1,
"dimensionName": "QueriesWithRegressedPerformance",
"unit": "Count"
},
{
"absoluteValue": 130.742187,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
},
{
"absoluteValue": 0,
"dimensionName": "VerificationProgress",
"unit": "Percent"
}
],
"recommendationReason": "",
"score": 3,
"state": {
"actionInitiatedBy": "User",
"currentValue": "Success",
"lastModified": "2017-03-01T14:38:05Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:05Z"
},
"type": "Microsoft.Sql/servers/databases/advisors/recommendedActions"
},
{
"id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/workloadinsight-demos/providers/Microsoft.Sql/servers/misosisvr/databases/IndexAdvisor_test_3/advisors/CreateIndex/recommendedActions/IR_[dbo]_[Employees]_560E15A98D14CA09BDFB",
"kind": "",
"location": "East Asia",
"name": "IR_[dbo]_[Employees]_560E15A98D14CA09BDFB",
"properties": {
"details": {
"includedColumns": "[Postal]",
"indexColumns": "[City], [State]",
"indexName": "nci_wi_Employees_8C18C2AF4267DC77793040782641CCDE",
"indexType": "NONCLUSTERED",
"schema": "[dbo]",
"table": "[Employees]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 17,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 128,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"implementationDetails": {
"method": "TSql",
"script": "CREATE NONCLUSTERED INDEX [nci_wi_Employees_8C18C2AF4267DC77793040782641CCDE] ON [dbo].[Employees] ([City], [State]) INCLUDE ([Postal]) WITH (ONLINE = ON)"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:05Z",
"observedImpact": [],
"recommendationReason": "",
"score": 3,
"state": {
"currentValue": "Active",
"lastModified": "2017-03-01T14:38:05Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:05Z"
},
"type": "Microsoft.Sql/servers/databases/advisors/recommendedActions"
}
]
}
}
}
}
}
},
"/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}/advisors/{advisorName}/recommendedActions/{recommendedActionName}": {
"get": {
"description": "Gets a database recommended action.",
"operationId": "DatabaseRecommendedActions_Get",
"parameters": [
{
"$ref": "#/parameters/ResourceGroupParameter"
},
{
"$ref": "#/parameters/ServerNameParameter"
},
{
"$ref": "#/parameters/DatabaseNameParameter"
},
{
"description": "The name of the Database Advisor.",
"in": "path",
"name": "advisorName",
"required": true,
"type": "string"
},
{
"description": "The name of Database Recommended Action.",
"in": "path",
"name": "recommendedActionName",
"required": true,
"type": "string"
},
{
"$ref": "#/parameters/SubscriptionIdParameter"
},
{
"$ref": "#/parameters/ApiVersionParameter"
}
],
"responses": {
"200": {
"description": "Successfully retrieved details of specified database recommended action.",
"schema": {
"$ref": "#/definitions/RecommendedAction"
}
},
"default": {
"description": "*** Error Responses: ***\n\n * 400 InvalidIdentifier - The identifier contains NULL or an invalid unicode character.\n\n * 404 SubscriptionDoesNotHaveServer - The requested server was not found\n\n * 404 DatabaseDoesNotExist - The requested database was not found\n\n * 404 SubscriptionDoesNotHaveElasticPool - The requested elastic pool was not found\n\n * 404 AdvisorNotFound - The requested advisor was not found.\n\n * 404 RecommendedActionNotFound - The requested recommended action was not found.\n\n * 404 AdvisorNotFound - The requested advisor was not found.\n\n * 404 RecommendedActionNotFound - The requested recommended action was not found.\n\n * 405 NotSupported - This functionality is not supported.\n\n * 409 Conflict - Request could not be processed because of conflict in the request.\n\n * 503 ServiceTemporarilyUnavailable - Feature temporarily unavailable."
}
},
"tags": [
"DatabaseRecommendedActions"
],
"x-ms-examples": {
"Get database recommended action": {
"parameters": {
"advisorName": "CreateIndex",
"api-version": "2015-05-01-preview",
"databaseName": "IndexAdvisor_test_3",
"recommendedActionName": "IR_[CRM]_[DataPoints]_4821CD2F9510D98184BB",
"resourceGroupName": "workloadinsight-demos",
"serverName": "misosisvr",
"subscriptionId": "00000000-1111-2222-3333-444444444444"
},
"responses": {
"200": {
"body": {
"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",
"kind": "",
"location": "East Asia",
"name": "IR_[CRM]_[DataPoints]_4821CD2F9510D98184BB",
"properties": {
"details": {
"includedColumns": "[Hour], [System], [LastChanged]",
"indexColumns": "[Name],[Money],[Power]",
"indexName": "nci_wi_DataPoints_B892614093BAC56295EF6018BD4CB51B",
"indexType": "NONCLUSTERED",
"schema": "[CRM]",
"table": "[DataPoints]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 1440,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 209.3125,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"implementationDetails": {
"method": "TSql",
"script": "CREATE NONCLUSTERED INDEX [nci_wi_DataPoints_B892614093BAC56295EF6018BD4CB51B] ON [CRM].[DataPoints] ([Name],[Money],[Power]) INCLUDE ([Hour], [System], [LastChanged]) WITH (ONLINE = ON)"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:04Z",
"observedImpact": [],
"recommendationReason": "",
"score": 1,
"state": {
"currentValue": "Active",
"lastModified": "2017-03-01T14:38:05Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:04Z"
},
"type": "Microsoft.Sql/servers/databases/advisors/recommendedActions"
}
}
}
}
}
},
"patch": {
"description": "Updates a database recommended action.",
"operationId": "DatabaseRecommendedActions_Update",
"parameters": [
{
"$ref": "#/parameters/ResourceGroupParameter"
},
{
"$ref": "#/parameters/ServerNameParameter"
},
{
"$ref": "#/parameters/DatabaseNameParameter"
},
{
"description": "The name of the Database Advisor.",
"in": "path",
"name": "advisorName",
"required": true,
"type": "string"
},
{
"description": "The name of Database Recommended Action.",
"in": "path",
"name": "recommendedActionName",
"required": true,
"type": "string"
},
{
"description": "The requested recommended action resource state.",
"in": "body",
"name": "parameters",
"required": true,
"schema": {
"$ref": "#/definitions/RecommendedAction"
}
},
{
"$ref": "#/parameters/SubscriptionIdParameter"
},
{
"$ref": "#/parameters/ApiVersionParameter"
}
],
"responses": {
"200": {
"description": "Successfully updated the specified database recommended action.",
"schema": {
"$ref": "#/definitions/RecommendedAction"
}
},
"default": {
"description": "*** Error Responses: ***\n\n * 400 InvalidIdentifier - The identifier contains NULL or an invalid unicode character.\n\n * 400 InvalidRecommendedActionUpsertRequest - The update recommended action request body does not exist or has no properties or state object.\n\n * 400 InvalidRecommendedActionState - The specified state for recommended action is invalid\n\n * 404 SubscriptionDoesNotHaveServer - The requested server was not found\n\n * 404 DatabaseDoesNotExist - The requested database was not found\n\n * 404 SubscriptionDoesNotHaveElasticPool - The requested elastic pool was not found\n\n * 404 AdvisorNotFound - The requested advisor was not found.\n\n * 404 RecommendedActionNotFound - The requested recommended action was not found.\n\n * 404 AdvisorNotFound - The requested advisor was not found.\n\n * 404 RecommendedActionNotFound - The requested recommended action was not found.\n\n * 405 NotSupported - This functionality is not supported.\n\n * 409 Conflict - Request could not be processed because of conflict in the request.\n\n * 503 ServiceTemporarilyUnavailable - Feature temporarily unavailable."
}
},
"tags": [
"DatabaseRecommendedActions"
],
"x-ms-examples": {
"Update database recommended action": {
"parameters": {
"advisorName": "CreateIndex",
"api-version": "2015-05-01-preview",
"databaseName": "IndexAdvisor_test_3",
"parameters": {
"properties": {
"state": {
"currentValue": "Pending"
}
}
},
"recommendedActionName": "IR_[CRM]_[DataPoints]_4821CD2F9510D98184BB",
"resourceGroupName": "workloadinsight-demos",
"serverName": "misosisvr",
"subscriptionId": "00000000-1111-2222-3333-444444444444"
},
"responses": {
"200": {
"body": {
"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",
"kind": "",
"location": "East Asia",
"name": "IR_[CRM]_[DataPoints]_4821CD2F9510D98184BB",
"properties": {
"details": {
"includedColumns": "[Hour], [System], [LastChanged]",
"indexColumns": "[Name],[Money],[Power]",
"indexName": "nci_wi_DataPoints_B892614093BAC56295EF6018BD4CB51B",
"indexType": "NONCLUSTERED",
"schema": "[CRM]",
"table": "[DataPoints]"
},
"errorDetails": {},
"estimatedImpact": [
{
"absoluteValue": 1440,
"dimensionName": "ActionDuration",
"unit": "Seconds"
},
{
"absoluteValue": 209.3125,
"dimensionName": "SpaceChange",
"unit": "Megabytes"
}
],
"executeActionInitiatedBy": "User",
"executeActionInitiatedTime": "2017-06-20T15:11:15Z",
"implementationDetails": {
"method": "TSql",
"script": "CREATE NONCLUSTERED INDEX [nci_wi_DataPoints_B892614093BAC56295EF6018BD4CB51B] ON [CRM].[DataPoints] ([Name],[Money],[Power]) INCLUDE ([Hour], [System], [LastChanged]) WITH (ONLINE = ON)"
},
"isArchivedAction": false,
"isExecutableAction": true,
"isRevertableAction": true,
"lastRefresh": "2017-03-01T14:38:04Z",
"observedImpact": [],
"recommendationReason": "",
"score": 1,
"state": {
"actionInitiatedBy": "User",
"currentValue": "Pending",
"lastModified": "2017-06-20T15:11:15Z"
},
"timeSeries": [],
"validSince": "2017-03-01T14:38:04Z"
},
"type": "Microsoft.Sql/servers/databases/advisors/recommendedActions"
}
}
}
}
}
}
}
},
"definitions": {
"Advisor": {
"allOf": [
{
"allOf": [
{
"description": "ARM resource.",
"properties": {
"id": {
"description": "Resource ID.",
"readOnly": true,
"type": "string"
},
"name": {
"description": "Resource name.",
"readOnly": true,
"type": "string"
},
"type": {
"description": "Resource type.",
"readOnly": true,
"type": "string"
}
},
"type": "object",
"x-ms-azure-resource": true
}
],
"description": "ARM proxy resource.",
"properties": {},
"type": "object"
}
],
"description": "Database, Server or Elastic Pool Advisor.",
"properties": {
"kind": {
"description": "Resource kind.",
"readOnly": true,
"type": "string"
},
"location": {
"description": "Resource location.",
"readOnly": true,
"type": "string"
},
"properties": {
"$ref": "#/definitions/AdvisorProperties",
"description": "Resource properties.",
"x-ms-client-flatten": true
}
},
"type": "object"
},
"AdvisorProperties": {
"description": "Properties for a Database, Server or Elastic Pool Advisor.",
"properties": {
"advisorStatus": {
"description": "Gets the status of availability of this advisor to customers. Possible values are 'GA', 'PublicPreview', 'LimitedPublicPreview' and 'PrivatePreview'.",
"enum": [
"GA",
"PublicPreview",
"LimitedPublicPreview",
"PrivatePreview"
],
"readOnly": true,
"type": "string",
"x-ms-enum": {
"modelAsString": false,
"name": "AdvisorStatus"
}
},
"autoExecuteStatus": {
"description": "Gets the auto-execute status (whether to let the system execute the recommendations) of this advisor. Possible values are 'Enabled' and 'Disabled'",
"enum": [
"Enabled",
"Disabled",
"Default"
],
"type": "string",
"x-ms-enum": {
"modelAsString": false,
"name": "AutoExecuteStatus"
}
},
"autoExecuteStatusInheritedFrom": {
"description": "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).",
"enum": [
"Default",
"Subscription",
"Server",
"ElasticPool",
"Database"
],
"readOnly": true,
"type": "string",
"x-ms-enum": {
"modelAsString": false,
"name": "AutoExecuteStatusInheritedFrom"
}
},
"lastChecked": {
"description": "Gets the time when the current resource was analyzed for recommendations by this advisor.",
"format": "date-time",
"readOnly": true,
"type": "string"
},
"recommendationsStatus": {
"description": "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.",
"readOnly": true,
"type": "string"
},
"recommendedActions": {
"description": "Gets the recommended actions for this advisor.",
"items": {
"$ref": "#/definitions/RecommendedAction"
},
"readOnly": true,
"type": "array"
}
},
"required": [
"autoExecuteStatus"
],
"type": "object"
},
"RecommendedAction": {
"allOf": [
{
"allOf": [
{
"description": "ARM resource.",
"properties": {
"id": {
"description": "Resource ID.",
"readOnly": true,
"type": "string"
},
"name": {
"description": "Resource name.",
"readOnly": true,
"type": "string"
},
"type": {
"description": "Resource type.",
"readOnly": true,
"type": "string"
}
},
"type": "object",
"x-ms-azure-resource": true
}
],
"description": "ARM proxy resource.",
"properties": {},
"type": "object"
}
],
"description": "Database, Server or Elastic Pool Recommended Action.",
"properties": {
"kind": {
"description": "Resource kind.",
"readOnly": true,
"type": "string"
},
"location": {
"description": "Resource location.",
"readOnly": true,
"type": "string"
},
"properties": {
"$ref": "#/definitions/RecommendedActionProperties",
"description": "Resource properties.",
"x-ms-client-flatten": true
}
},
"type": "object"
},
"RecommendedActionErrorInfo": {
"description": "Contains error information for an Azure SQL Database, Server or Elastic Pool Recommended Action.",
"properties": {
"errorCode": {
"description": "Gets the reason why the recommended action was put to error state. e.g., DatabaseHasQdsOff, IndexAlreadyExists",
"readOnly": true,
"type": "string"
},
"isRetryable": {
"description": "Gets whether the error could be ignored and recommended action could be retried. Possible values are: Yes/No",
"enum": [
"Yes",
"No"
],
"readOnly": true,
"type": "string",
"x-ms-enum": {
"modelAsString": false,
"name": "IsRetryable"
}
}
},
"type": "object"
},
"RecommendedActionImpactRecord": {
"description": "Contains information of estimated or observed impact on various metrics for an Azure SQL Database, Server or Elastic Pool Recommended Action.",
"properties": {
"absoluteValue": {
"description": "Gets the absolute value of this dimension if applicable. e.g., Number of Queries affected",
"format": "double",
"readOnly": true,
"type": "number"
},
"changeValueAbsolute": {
"description": "Gets the absolute change in the value of this dimension. e.g., Absolute Disk space change in Megabytes",
"format": "double",
"readOnly": true,
"type": "number"
},
"changeValueRelative": {
"description": "Gets the relative change in the value of this dimension. e.g., Relative Disk space change in Percentage",
"format": "double",
"readOnly": true,
"type": "number"
},
"dimensionName": {
"description": "Gets the name of the impact dimension. e.g., CPUChange, DiskSpaceChange, NumberOfQueriesAffected.",
"readOnly": true,
"type": "string"
},
"unit": {
"description": "Gets the name of the impact dimension. e.g., CPUChange, DiskSpaceChange, NumberOfQueriesAffected.",
"readOnly": true,
"type": "string"
}
},
"type": "object"
},
"RecommendedActionImplementationInfo": {
"description": "Contains information for manual implementation for an Azure SQL Database, Server or Elastic Pool Recommended Action.",
"properties": {
"method": {
"description": "Gets the method in which this recommended action can be manually implemented. e.g., TSql, AzurePowerShell.",
"enum": [
"TSql",
"AzurePowerShell"
],
"readOnly": true,
"type": "string",
"x-ms-enum": {
"modelAsString": false,
"name": "ImplementationMethod"
}
},
"script": {
"description": "Gets the manual implementation script. e.g., T-SQL script that could be executed on the database.",
"readOnly": true,
"type": "string"
}
},
"type": "object"
},
"RecommendedActionMetricInfo": {
"description": "Contains time series of various impacted metrics for an Azure SQL Database, Server or Elastic Pool Recommended Action.",
"properties": {
"metricName": {
"description": "Gets the name of the metric. e.g., CPU, Number of Queries.",
"readOnly": true,
"type": "string"
},
"startTime": {
"description": "Gets the start time of time interval given by this MetricInfo.",
"format": "date-time",
"readOnly": true,
"type": "string"
},
"timeGrain": {
"description": "Gets the duration of time interval for the value given by this MetricInfo. e.g., PT1H (1 hour)",
"readOnly": true,
"type": "string"
},
"unit": {
"description": "Gets the unit in which metric is measured. e.g., DTU, Frequency",
"readOnly": true,
"type": "string"
},
"value": {
"description": "Gets the value of the metric in the time interval given by this MetricInfo.",
"format": "double",
"readOnly": true,
"type": "number"
}
},
"type": "object"
},
"RecommendedActionProperties": {
"description": "Properties for a Database, Server or Elastic Pool Recommended Action.",
"properties": {
"details": {
"additionalProperties": {
"type": "object"
},
"description": "Gets additional details specific to this recommended action.",
"readOnly": true,
"type": "object"
},
"errorDetails": {
"$ref": "#/definitions/RecommendedActionErrorInfo",
"description": "Gets the error details if and why this recommended action is put to error state.",
"readOnly": true
},
"estimatedImpact": {
"description": "Gets the estimated impact info for this recommended action e.g., Estimated CPU gain, Estimated Disk Space change",
"items": {
"$ref": "#/definitions/RecommendedActionImpactRecord"
},
"readOnly": true,
"type": "array"
},
"executeActionDuration": {
"description": "Gets the time taken for applying this recommended action on user resource. e.g., time taken for index creation",
"readOnly": true,
"type": "string"
},
"executeActionInitiatedBy": {
"description": "Gets if approval for applying this recommended action was given by user/system.",
"enum": [
"User",
"System"
],
"readOnly": true,
"type": "string",
"x-ms-enum": {
"modelAsString": false,
"name": "RecommendedActionInitiatedBy"
}
},
"executeActionInitiatedTime": {
"description": "Gets the time when this recommended action was approved for execution.",
"format": "date-time",
"readOnly": true,
"type": "string"
},
"executeActionStartTime": {
"description": "Gets the time when system started applying this recommended action on the user resource. e.g., index creation start time",
"format": "date-time",
"readOnly": true,
"type": "string"
},
"implementationDetails": {
"$ref": "#/definitions/RecommendedActionImplementationInfo",
"description": "Gets the implementation details of this recommended action for user to apply it manually.",
"readOnly": true
},
"isArchivedAction": {
"description": "Gets if this recommended action was suggested some time ago but user chose to ignore this and system added a new recommended action again.",
"readOnly": true,
"type": "boolean"
},
"isExecutableAction": {
"description": "Gets if this recommended action is actionable by user",
"readOnly": true,
"type": "boolean"
},
"isRevertableAction": {
"description": "Gets if changes applied by this recommended action can be reverted by user",
"readOnly": true,
"type": "boolean"
},
"lastRefresh": {
"description": "Gets time when this recommended action was last refreshed.",
"format": "date-time",
"readOnly": true,
"type": "string"
},
"linkedObjects": {
"description": "Gets the linked objects, if any.",
"items": {
"type": "string"
},
"readOnly": true,
"type": "array"
},
"observedImpact": {
"description": "Gets the observed/actual impact info for this recommended action e.g., Actual CPU gain, Actual Disk Space change",
"items": {
"$ref": "#/definitions/RecommendedActionImpactRecord"
},
"readOnly": true,
"type": "array"
},
"recommendationReason": {
"description": "Gets the reason for recommending this action. e.g., DuplicateIndex",
"readOnly": true,
"type": "string"
},
"revertActionDuration": {
"description": "Gets the time taken for reverting changes of this recommended action on user resource. e.g., time taken for dropping the created index.",
"readOnly": true,
"type": "string"
},
"revertActionInitiatedBy": {
"description": "Gets if approval for reverting this recommended action was given by user/system.",
"enum": [
"User",
"System"
],
"readOnly": true,
"type": "string",
"x-ms-enum": {
"modelAsString": false,
"name": "RecommendedActionInitiatedBy"
}
},
"revertActionInitiatedTime": {
"description": "Gets the time when this recommended action was approved for revert.",
"format": "date-time",
"readOnly": true,
"type": "string"
},
"revertActionStartTime": {
"description": "Gets the time when system started reverting changes of this recommended action on user resource. e.g., time when index drop is executed.",
"format": "date-time",
"readOnly": true,
"type": "string"
},
"score": {
"description": "Gets the impact of this recommended action. Possible values are 1 - Low impact, 2 - Medium Impact and 3 - High Impact",
"format": "int32",
"readOnly": true,
"type": "integer"
},
"state": {
"$ref": "#/definitions/RecommendedActionStateInfo",
"description": "Gets the info of the current state the recommended action is in."
},
"timeSeries": {
"description": "Gets the time series info of metrics for this recommended action e.g., CPU consumption time series",
"items": {
"$ref": "#/definitions/RecommendedActionMetricInfo"
},
"readOnly": true,
"type": "array"
},
"validSince": {
"description": "Gets the time since when this recommended action is valid.",
"format": "date-time",
"readOnly": true,
"type": "string"
}
},
"required": [
"state"
],
"type": "object"
},
"RecommendedActionStateInfo": {
"description": "Contains information of current state for an Azure SQL Database, Server or Elastic Pool Recommended Action.",
"properties": {
"actionInitiatedBy": {
"description": "Gets who initiated the execution of this recommended action. Possible Value are: User -> When user explicitly 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.",
"enum": [
"User",
"System"
],
"readOnly": true,
"type": "string",
"x-ms-enum": {
"modelAsString": false,
"name": "RecommendedActionInitiatedBy"
}
},
"currentValue": {
"description": "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. ",
"enum": [
"Active",
"Pending",
"Executing",
"Verifying",
"PendingRevert",
"RevertCancelled",
"Reverting",
"Reverted",
"Ignored",
"Expired",
"Monitoring",
"Resolved",
"Success",
"Error"
],
"type": "string",
"x-ms-enum": {
"modelAsString": true,
"name": "RecommendedActionCurrentState"
}
},
"lastModified": {
"description": "Gets the time when the state was last modified",
"format": "date-time",
"readOnly": true,
"type": "string"
}
},
"required": [
"currentValue"
],
"type": "object"
}
}
}