Azure SQL Database 和 Azure SQL 受控執行個體中的自動調整Automatic tuning in Azure SQL Database and Azure SQL Managed Instance

適用於: yesAzure SQL Database yesAzure SQL 受控執行個體 APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance

Azure SQL Database 和 Azure SQL 受控執行個體自動調整會透過以 AI 和機器學習為基礎的持續效能調整,提供尖峰效能和穩定的工作負載。Azure SQL Database and Azure SQL Managed Instance automatic tuning provides peak performance and stable workloads through continuous performance tuning based on AI and machine learning.

自動調整是完全受控的智慧效能服務,它能使用內建的智慧機制來持續監視在資料庫上執行的查詢,並且自動改善查詢的效能。Automatic tuning is a fully managed intelligent performance service that uses built-in intelligence to continuously monitor queries executed on a database, and it automatically improves their performance. 此功能是透過讓資料庫針對變動的工作負載進行動態調適,並套用調整建議來達成。This is achieved through dynamically adapting database to the changing workloads and applying tuning recommendations. 自動調整能透過 AI 從 Azure 上的所有資料庫進行水平學習,並能動態地改善其調整動作。Automatic tuning learns horizontally from all databases on Azure through AI and it dynamically improves its tuning actions. 隨著自動調整,資料庫執行的時間越長,就越好。The longer a database runs with automatic tuning on, the better it performs.

Azure SQL Database 和 Azure SQL 受控執行個體自動調整可能是您可以啟用以提供穩定和尖峰執行資料庫工作負載的其中一項最重要的功能。Azure SQL Database and Azure SQL Managed Instance automatic tuning might be one of the most important features that you can enable to provide stable and peak performing database workloads.

自動調整可為您做什麼What can automatic tuning do for you

  • 自動調整資料庫效能Automated performance tuning of databases
  • 自動驗證效能提升Automated verification of performance gains
  • 自動復原與自我修正Automated rollback and self-correction
  • 調整歷程記錄Tuning history
  • 手動部署的微調動作 Transact-sql (T-sql)腳本Tuning action Transact-SQL (T-SQL) scripts for manual deployments
  • 主動式工作負載效能監視Proactive workload performance monitoring
  • 以數十萬個資料庫向外延展的功能Scale out capability on hundreds of thousands of databases
  • 為 DevOps 資源及擁有權總成本帶來正面影響Positive impact to DevOps resources and the total cost of ownership

安全、可靠且經過實證Safe, Reliable, and Proven

在 Azure SQL Database 中套用至資料庫的微調作業,對於您最密集之工作負載的效能是完全安全的。Tuning operations applied to databases in Azure SQL Database are fully safe for the performance of your most intense workloads. 系統已經精心設計成不會干擾使用者工作負載。The system has been designed with care not to interfere with the user workloads. 自動調整建議只會在低使用率的時段套用。Automated tuning recommendations are applied only at the times of a low utilization. 系統也可以暫時地停用自動調整作業,以保護工作負載效能。The system can also temporarily disable automatic tuning operations to protect the workload performance. 在這種情況下,[由系統停用] 訊息會顯示在 Azure 入口網站中。In such case, "Disabled by the system" message will be shown in Azure portal. 自動調整會將工作負載考慮為具有最高資源優先順序。Automatic tuning regards workloads with the highest resource priority.

自動調整是成熟的機制,且已在數百萬個於 Azure 上執行的資料庫上達到完美。Automatic tuning mechanisms are mature and have been perfected on several million databases running on Azure. 已套用的自動調整作業都會進行自動驗證,以確保對工作負載效能有正面的改進。Automated tuning operations applied are verified automatically to ensure there is a positive improvement to the workload performance. 系統會動態地偵測迴歸的效能建議,並迅速地做出還原。Regressed performance recommendations are dynamically detected and promptly reverted. 透過記錄的微調記錄,針對 Azure SQL Database 和 Azure SQL 受控執行個體中的每個資料庫,提供微調改良的清楚追蹤。Through the tuning history recorded, there exists a clear trace of tuning improvements made to each database in Azure SQL Database and Azure SQL Managed Instance.

自動調整的運作方式為何

Azure SQL Database 自動調整會與資料庫引擎中的 SQL Server 自動調整功能共用其核心邏輯。Azure SQL Database automatic tuning is sharing its core logic with the SQL Server automatic tuning feature in the database engine. 如需內建智慧機制的其他技術資訊,請參閱 SQL Server 自動調整 (英文)。For additional technical information on the built-in intelligence mechanism, see SQL Server automatic tuning.

如需自動調整運作方式的概觀與典型的使用案例,請觀賞內嵌影片:For an overview of how automatic tuning works and for typical usage scenarios, see the embedded video:

啟用自動微調Enable automatic tuning

自動調整選項Automatic tuning options

Azure SQL Database 和 Azure SQL 受控執行個體中可用的自動調整選項如下:The automatic tuning options available in Azure SQL Database and Azure SQL Managed Instance are:

自動調整選項Automatic tuning option 單一資料庫和集區資料庫支援Single database and pooled database support 實例資料庫支援Instance database support
建立索引-識別可改善工作負載效能、建立索引,並自動驗證查詢效能已改善的索引。CREATE INDEX - Identifies indexes that may improve performance of your workload, creates indexes, and automatically verifies that performance of queries has improved. Yes No
DROP INDEX -每日識別重複和重複的索引(唯一索引除外),以及長時間未使用的索引(>90 天)。DROP INDEX - Identifies redundant and duplicate indexes daily, except for unique indexes, and indexes that were not used for a long time (>90 days). 請注意,此選項與使用分割區切換和索引提示的應用程式不相容。Please note that this option is not compatible with applications using partition switching and index hints. 高階和商務關鍵服務層級不支援卸載未使用的索引。Dropping unused indexes is not supported for Premium and Business Critical service tiers. Yes No
強制執行最後一個良好的計畫(自動計畫更正)-使用比上一個良好計畫慢的執行計畫來識別 Azure SQL 查詢,並使用最後一個已知的良好計畫,而不是回歸計畫來查詢。FORCE LAST GOOD PLAN (automatic plan correction) - Identifies Azure SQL queries using an execution plan that is slower than the previous good plan, and queries using the last known good plan instead of the regressed plan. YesYes Yes

SQL Database 的自動調整Automatic tuning for SQL Database

Azure SQL Database 的自動調整會使用建立索引DROP INDEX,並強制執行最後一個良好的計畫資料庫建議程式,以將您的資料庫效能優化。Automatic tuning for Azure SQL Database uses the CREATE INDEX, DROP INDEX, and FORCE LAST GOOD PLAN database advisor recommendations to optimize your database performance. 如需詳細資訊,請參閱 Azure 入口網站、 PowerShellREST API的 Database advisor 建議For more information, see Database advisor recommendations in the Azure portal, in PowerShell, and in the REST API.

您可以使用 Azure 入口網站手動套用微調建議,也可以讓自動調整為您自行套用微調建議。You can either manually apply tuning recommendations using the Azure portal or you can let automatic tuning autonomously apply tuning recommendations for you. 讓系統為您自動套用調整建議的好處,就是系統會自動驗證工作負載效能是否有正面的改善,或者如果未偵測到明顯的效能改善,系統會自動還原調整建議。The benefits of letting the system autonomously apply tuning recommendations for you is that it automatically validates there exists a positive gain to the workload performance, and if there is no significant performance improvement detected, it will automatically revert the tuning recommendation. 請注意,針對受到沒有經常執行之調整建議所影響的查詢,其驗證階段根據設計可能需要最多 72 小時才能完成。Please note that in case of queries affected by tuning recommendations that are not executed frequently, the validation phase can take up to 72 hrs by design.

如果您要透過 T-sql 套用微調建議,則無法使用自動效能驗證和反轉機制。In case you are applying tuning recommendations through T-SQL, the automatic performance validation, and reversal mechanisms are not available. 以這種方式套用的建議將維持作用中狀態,並顯示于24-48 小時的微調建議清單中。Recommendations applied in such way will remain active and shown in the list of tuning recommendations for 24-48 hrs. 系統自動將其收回之前。before the system automatically withdraws them. 如果您想要更快移除建議,可以將它從 Azure 入口網站中捨棄。If you would like to remove a recommendation sooner, you can discard it from Azure portal.

自動調整選項可以獨立啟用或停用每個資料庫,也可以在伺服器層級設定,並套用到從伺服器繼承設定的每個資料庫。Automatic tuning options can be independently enabled or disabled per database, or they can be configured at the server-level and applied on every database that inherits settings from the server. 伺服器可以繼承自動調整設定的 Azure 預設值。Servers can inherit Azure defaults for automatic tuning settings. Azure 預設值此時會設為已啟用 FORCE_LAST_GOOD_PLAN 和 CREATE_INDEX,且已停用 DROP_INDEX。Azure defaults at this time are set to FORCE_LAST_GOOD_PLAN is enabled, CREATE_INDEX is enabled, and DROP_INDEX is disabled.

重要

從3月起2020,自動調整的 Azure 預設值變更將會生效,如下所示:As of March, 2020 changes to Azure defaults for automatic tuning will take effect as follows:

  • 新的 Azure 預設值將會 FORCE_LAST_GOOD_PLAN = 已啟用,CREATE_INDEX = 已停用,且 DROP_INDEX = 已停用。New Azure defaults will be FORCE_LAST_GOOD_PLAN = enabled, CREATE_INDEX = disabled, and DROP_INDEX = disabled.
  • 未設定自動調整喜好設定的現有伺服器會自動設定為繼承新的 Azure 預設值。Existing servers with no automatic tuning preferences configured will be automatically configured to INHERIT the new Azure defaults. 這適用于目前有未定義狀態自動調整之伺服器設定的所有客戶。This applies to all customers currently having server settings for automatic tuning in an undefined state.
  • 新建立的伺服器會自動設定為繼承新的 Azure 預設值(不同于先前在建立新伺服器時,自動調整設定處於未定義狀態時)。New servers created will automatically be configured to INHERIT the new Azure defaults (unlike earlier when automatic tuning configuration was in an undefined state upon new server creation).

在伺服器上設定自動調整選項,並繼承屬於父系伺服器之資料庫的設定,是設定自動調整的建議方法,因為它可簡化大量資料庫的自動調整選項管理。Configuring automatic tuning options on a server and inheriting settings for databases belonging to the parent server is a recommended method for configuring automatic tuning as it simplifies management of automatic tuning options for a large number of databases.

若要瞭解如何建立自動調整建議的電子郵件通知,請參閱自動調整的電子郵件通知To learn about building email notifications for automatic tuning recommendations, see Email notifications for automatic tuning.

Azure SQL 受控執行個體的自動調整Automatic tuning for Azure SQL Managed Instance

SQL 受控執行個體的自動調整僅支援強制執行最後一個良好的計畫Automatic tuning for SQL Managed Instance only supports FORCE LAST GOOD PLAN. 如需透過 T-sql 設定自動調整選項的詳細資訊,請參閱自動調整會引進自動計畫更正自動計畫更正For more information about configuring automatic tuning options through T-SQL, see Automatic tuning introduces automatic plan correction and Automatic plan correction.

後續步驟Next steps