Database Automatic Tuning - Update

服務:

SQL Database

API 版本:

2023-08-01

更新目標資料庫的自動調整屬性。

PATCH https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}/automaticTuning/current?api-version=2023-08-01

URI 參數

名稱	位於	必要	類型	Description
databaseName	path	True	string	資料庫的名稱。
resourceGroupName	path	True	string	包含資源的資源群組名稱。 您可以從 Azure Resource Manager API 或入口網站取得此值。
serverName	path	True	string	伺服器的名稱。
subscriptionId	path	True	string	識別 Azure 訂用帳戶的訂用帳戶標識碼。
api-version	query	True	string	要用於要求的 API 版本。

要求本文

名稱	類型	Description
properties.desiredState	AutomaticTuningMode	自動調整所需的狀態。
properties.options	<string,  AutomaticTuningOptions>	自動調整選項定義。

回應

名稱	類型	Description
200 OK	DatabaseAutomaticTuning	已成功更新指定的資料庫自動調整設定。
Other Status Codes	ErrorResponse	錯誤回應： *** 400 FeatureDisabledOnSelectedEdition - 用戶嘗試使用目前資料庫版本已停用的功能。 400 InvalidIdentifier - 識別碼包含 NULL 或無效的 Unicode 字元。 400 SystemDatabasesNotSupported - 用戶嘗試取得系統資料庫的自動調整資訊。 400 DataWarehouseNotSupported - SQL 數據倉儲不支援自動調整。 400 InvalidAutomaticTuningUpsertRequest - 更新自動調整要求本文不存在或沒有屬性物件。 400 InvalidAdvisorAutoExecuteStatus - 不允許為建議程式指定自動執行狀態。 404 SubscriptionDoesNotHaveServer - 找不到要求的伺服器 404 DatabaseDoesNotExist - 找不到要求的資料庫 404 SubscriptionDoesNotHaveElasticPool - 找不到要求的彈性集區 404 OperationIdNotFound - 標識符為 的作業不存在。 404 AutomaticTuningOptionNotFound - 找不到要求的自動調整選項。 405 NotSupported - 不支援此功能。 409 衝突 - 要求因要求衝突而無法處理。 409 CannotCancelOperation - 管理作業處於無法取消的狀態。 409 OperationCancelled - 使用者已取消作業。 409 OperationInterrupted - 無法完成資源上的作業，因為相同資源上的另一個作業中斷。 409 DefaultAdvisorStateNotAllowedInCustomDbMode - CUSTOM 模式中不允許 DEFAULT Advisor 狀態 409 DisabledBySystem - 系統已停用此選項 429 SubscriptionTooManyCreateUpdateRequests - 超出可用資源可處理之最大要求的要求。 429 SubscriptionTooManyRequests - 超出可用資源可處理之最大要求的要求。 500 OperationTimedOut - 作業逾時並自動回復。 請重試作業。 503 ServiceTemporarilyUnavailable - 功能暫時無法使用。 503 TooManyRequests - 超出可用資源可處理之最大要求的要求。

範例

Updates database automatic tuning settings with all properties

Updates database automatic tuning settings with minimal properties

Updates database automatic tuning settings with all properties

範例要求

HTTP

PATCH https://management.azure.com/subscriptions/c3aa9078-0000-0000-0000-e36f151182d7/resourceGroups/default-sql-onebox/providers/Microsoft.Sql/servers/testsvr11/databases/db1/automaticTuning/current?api-version=2023-08-01

{
  "properties": {
    "desiredState": "Auto",
    "options": {
      "createIndex": {
        "desiredState": "Off"
      },
      "dropIndex": {
        "desiredState": "On"
      },
      "forceLastGoodPlan": {
        "desiredState": "Default"
      }
    }
  }
}

dotnet

using Azure;
using Azure.ResourceManager;
using System;
using System.Threading.Tasks;
using Azure.Core;
using Azure.Identity;
using Azure.ResourceManager.Sql.Models;
using Azure.ResourceManager.Sql;

// Generated from example definition: specification/sql/resource-manager/Microsoft.Sql/stable/2023-08-01/examples/DatabaseAutomaticTuningUpdateMax.json
// this example is just showing the usage of "DatabaseAutomaticTuning_Update" operation, for the dependent resources, they will have to be created separately.

// get your azure access token, for more details of how Azure SDK get your access token, please refer to https://learn.microsoft.com/en-us/dotnet/azure/sdk/authentication?tabs=command-line
TokenCredential cred = new DefaultAzureCredential();
// authenticate your client
ArmClient client = new ArmClient(cred);

// this example assumes you already have this SqlDatabaseAutomaticTuningResource created on azure
// for more information of creating SqlDatabaseAutomaticTuningResource, please refer to the document of SqlDatabaseAutomaticTuningResource
string subscriptionId = "c3aa9078-0000-0000-0000-e36f151182d7";
string resourceGroupName = "default-sql-onebox";
string serverName = "testsvr11";
string databaseName = "db1";
ResourceIdentifier sqlDatabaseAutomaticTuningResourceId = SqlDatabaseAutomaticTuningResource.CreateResourceIdentifier(subscriptionId, resourceGroupName, serverName, databaseName);
SqlDatabaseAutomaticTuningResource sqlDatabaseAutomaticTuning = client.GetSqlDatabaseAutomaticTuningResource(sqlDatabaseAutomaticTuningResourceId);

// invoke the operation
SqlDatabaseAutomaticTuningData data = new SqlDatabaseAutomaticTuningData
{
    DesiredState = AutomaticTuningMode.Auto,
    Options =
    {
    ["createIndex"] = new AutomaticTuningOptions
    {
    DesiredState = AutomaticTuningOptionModeDesired.Off,
    },
    ["dropIndex"] = new AutomaticTuningOptions
    {
    DesiredState = AutomaticTuningOptionModeDesired.On,
    },
    ["forceLastGoodPlan"] = new AutomaticTuningOptions
    {
    DesiredState = AutomaticTuningOptionModeDesired.Default,
    }
    },
};
SqlDatabaseAutomaticTuningResource result = await sqlDatabaseAutomaticTuning.UpdateAsync(data);

// the variable result is a resource, you could call other operations on this instance as well
// but just for demo, we get its data from this resource instance
SqlDatabaseAutomaticTuningData resourceData = result.Data;
// for demo we just print out the id
Console.WriteLine($"Succeeded on id: {resourceData.Id}");

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

範例回覆

狀態碼:

200

{
  "id": "/subscriptions/c3aa9078-0000-0000-0000-e36f151182d7/resourceGroups/default-sql-onebox/providers/Microsoft.Sql/servers/testsvr11/databases/db1/automaticTuning/current",
  "name": "current",
  "type": "Microsoft.Sql/servers/databases/automaticTuning",
  "properties": {
    "desiredState": "Auto",
    "actualState": "Auto",
    "options": {
      "forceLastGoodPlan": {
        "desiredState": "Default",
        "actualState": "On",
        "reasonCode": 2,
        "reasonDesc": "AutoConfigured"
      },
      "createIndex": {
        "desiredState": "Off",
        "actualState": "Off"
      },
      "dropIndex": {
        "desiredState": "On",
        "actualState": "On"
      },
      "maintainIndex": {
        "desiredState": "Default",
        "actualState": "Off",
        "reasonCode": 2,
        "reasonDesc": "AutoConfigured"
      }
    }
  }
}

Updates database automatic tuning settings with minimal properties

範例要求

HTTP

PATCH https://management.azure.com/subscriptions/c3aa9078-0000-0000-0000-e36f151182d7/resourceGroups/default-sql-onebox/providers/Microsoft.Sql/servers/testsvr11/databases/db1/automaticTuning/current?api-version=2023-08-01

{
  "properties": {
    "desiredState": "Auto"
  }
}

dotnet

using Azure;
using Azure.ResourceManager;
using System;
using System.Threading.Tasks;
using Azure.Core;
using Azure.Identity;
using Azure.ResourceManager.Sql.Models;
using Azure.ResourceManager.Sql;

// Generated from example definition: specification/sql/resource-manager/Microsoft.Sql/stable/2023-08-01/examples/DatabaseAutomaticTuningUpdateMin.json
// this example is just showing the usage of "DatabaseAutomaticTuning_Update" operation, for the dependent resources, they will have to be created separately.

// get your azure access token, for more details of how Azure SDK get your access token, please refer to https://learn.microsoft.com/en-us/dotnet/azure/sdk/authentication?tabs=command-line
TokenCredential cred = new DefaultAzureCredential();
// authenticate your client
ArmClient client = new ArmClient(cred);

// this example assumes you already have this SqlDatabaseAutomaticTuningResource created on azure
// for more information of creating SqlDatabaseAutomaticTuningResource, please refer to the document of SqlDatabaseAutomaticTuningResource
string subscriptionId = "c3aa9078-0000-0000-0000-e36f151182d7";
string resourceGroupName = "default-sql-onebox";
string serverName = "testsvr11";
string databaseName = "db1";
ResourceIdentifier sqlDatabaseAutomaticTuningResourceId = SqlDatabaseAutomaticTuningResource.CreateResourceIdentifier(subscriptionId, resourceGroupName, serverName, databaseName);
SqlDatabaseAutomaticTuningResource sqlDatabaseAutomaticTuning = client.GetSqlDatabaseAutomaticTuningResource(sqlDatabaseAutomaticTuningResourceId);

// invoke the operation
SqlDatabaseAutomaticTuningData data = new SqlDatabaseAutomaticTuningData
{
    DesiredState = AutomaticTuningMode.Auto,
};
SqlDatabaseAutomaticTuningResource result = await sqlDatabaseAutomaticTuning.UpdateAsync(data);

// the variable result is a resource, you could call other operations on this instance as well
// but just for demo, we get its data from this resource instance
SqlDatabaseAutomaticTuningData resourceData = result.Data;
// for demo we just print out the id
Console.WriteLine($"Succeeded on id: {resourceData.Id}");

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

範例回覆

狀態碼:

200

{
  "id": "/subscriptions/c3aa9078-0000-0000-0000-e36f151182d7/resourceGroups/default-sql-onebox/providers/Microsoft.Sql/servers/testsvr11/databases/db1/automaticTuning/current",
  "name": "current",
  "type": "Microsoft.Sql/servers/databases/automaticTuning",
  "properties": {
    "desiredState": "Auto",
    "actualState": "Auto",
    "options": {
      "forceLastGoodPlan": {
        "desiredState": "Default",
        "actualState": "On",
        "reasonCode": 2,
        "reasonDesc": "AutoConfigured"
      },
      "createIndex": {
        "desiredState": "Default",
        "actualState": "On",
        "reasonCode": 2,
        "reasonDesc": "AutoConfigured"
      },
      "dropIndex": {
        "desiredState": "Default",
        "actualState": "Off",
        "reasonCode": 2,
        "reasonDesc": "AutoConfigured"
      },
      "maintainIndex": {
        "desiredState": "Default",
        "actualState": "Off",
        "reasonCode": 2,
        "reasonDesc": "AutoConfigured"
      }
    }
  }
}

定義

名稱	Description
AutomaticTuningDisabledReason	如有需要和實際狀態，原因描述會有所不同。
AutomaticTuningMode	自動調整所需的狀態。
AutomaticTuningOptionModeActual	自動調整選項實際狀態。
AutomaticTuningOptionModeDesired	自動調整選項所需的狀態。
AutomaticTuningOptions	個別建議程序自動調整屬性。
DatabaseAutomaticTuning	資料庫層級的自動調整。
ErrorAdditionalInfo	資源管理錯誤其他資訊。
ErrorDetail	錯誤詳細數據。
ErrorResponse	錯誤回應

AutomaticTuningDisabledReason

列舉型別

如有需要和實際狀態，原因描述會有所不同。

值	Description
Default
Disabled
AutoConfigured
InheritedFromServer
QueryStoreOff
QueryStoreReadOnly
NotSupported

AutomaticTuningMode

列舉型別

自動調整所需的狀態。

值	Description
Inherit
Custom
Auto
Unspecified

AutomaticTuningOptionModeActual

列舉型別

自動調整選項實際狀態。

值	Description
Off
On

AutomaticTuningOptionModeDesired

列舉型別

自動調整選項所需的狀態。

值	Description
Off
On
Default

AutomaticTuningOptions

Object

個別建議程序自動調整屬性。

名稱	類型	Description
actualState	AutomaticTuningOptionModeActual	自動調整選項實際狀態。
desiredState	AutomaticTuningOptionModeDesired	自動調整選項所需的狀態。
reasonCode	integer (int32)	如有需要且實際狀態不同，原因代碼。
reasonDesc	AutomaticTuningDisabledReason	如有需要和實際狀態，原因描述會有所不同。

DatabaseAutomaticTuning

Object

資料庫層級的自動調整。

名稱	類型	Description
id	string	資源標識碼。
name	string	資源名稱。
properties.actualState	AutomaticTuningMode	自動調整實際狀態。
properties.desiredState	AutomaticTuningMode	自動調整所需的狀態。
properties.options	<string,  AutomaticTuningOptions>	自動調整選項定義。
type	string	資源類型。

ErrorAdditionalInfo

Object

資源管理錯誤其他資訊。

名稱	類型	Description
info	object	其他資訊。
type	string	其他信息類型。

ErrorDetail

Object

錯誤詳細數據。

名稱	類型	Description
additionalInfo	ErrorAdditionalInfo[]	錯誤其他資訊。
code	string	錯誤碼。
details	ErrorDetail[]	錯誤詳細數據。
message	string	錯誤訊息。
target	string	錯誤目標。

ErrorResponse

Object

錯誤回應

名稱	類型	Description
error	ErrorDetail	error 物件。