Azure SQL Database 中的自動調整Automatic tuning in Azure SQL Database

Azure SQL Database 自動調整能透過以 AI 和機器學習為基礎的持續效能調整,來提供尖峰的效能與穩定的工作負載。Azure SQL Database 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. Azure SQL Database 在開啟自動調整的情況下執行得愈久,自動調整的效果便愈好。The longer an Azure SQL Database runs with automatic tuning on, the better it performs.

Azure SQL Database 自動調整是您可以啟用以提供穩定且執行良好資料庫工作負載的其中一個最重要功能。Azure SQL Database 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?

  • Azure SQL 資料庫的自動化效能調整Automated performance tuning of Azure SQL databases
  • 自動驗證效能提升Automated verification of performance gains
  • 自動復原與自我修正Automated rollback and self-correction
  • 調整歷程記錄Tuning history
  • 適用於手動部署的調整動作 T-SQL 指令碼Tuning action 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 資料庫的調整作業,對於您最密集之工作負載的效能是完全安全的。Tuning operations applied to Azure SQL databases 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 所做出的調整改進。Through the tuning history recorded, there exists a clear trace of tuning improvements made to each Azure SQL Database.

自動調整的運作方式為何

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

使用自動調整Use Automatic tuning

自動調整必須在您的訂用帳戶上啟用。Automatic tuning needs to be enabled on your subscription. 若要使用 Azure 入口網站來啟用自動調整,請參閱啟用自動調整To enable automatic tuning using Azure portal, see Enable automatic tuning.

自動調整可透過自動套用調整建議 (包括自動驗證效能提升) 來自主地運作。Automatic tuning can operate autonomously through automatically applying tuning recommendations, including automated verification of performance gains.

如需更充分的控制,可以關閉自動套用調整建議的功能,並透過 Azure 入口網站手動套用調整建議。For more control, automatic application of tuning recommendations can be turned off, and tuning recommendations can be manually applied through Azure portal. 您也可以僅使用此解決方案來檢視自動調整建議,並透過偏好的指令碼和工具來手動套用那些調整建議。It is also possible to use the solution to view automated tuning recommendations only and manually apply them through scripts and tools of your choice.

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

自動調整選項Automatic tuning options

Azure SQL Database 中可用的自動調整選項有:Automatic tuning options available in Azure SQL Database are:

自動調整選項Automatic tuning option 單一資料庫和集區資料庫支援Single database and pooled database support 執行個體的資料庫支援Instance database support
CREATE INDEX -找出可能改善您的工作負載的效能、 建立索引,並自動確認已改善查詢效能的索引。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 at this time the option is not compatible with applications using partition switching and index hints. Yes No
FORCE LAST GOOD PLAN (自動計劃更正中)-使用執行計畫低於前一個良好計畫,並使用已知的良好計畫而不迴歸的計畫查詢識別的 SQL 查詢。FORCE LAST GOOD PLAN (automatic plan correction) - Identifies SQL queries using execution plan that is slower than the previous good plan, and queries using the last known good plan instead of the regressed plan. Yes Yes

自動調整可識別 CREATE INDEXDROP INDEXFORCE LAST GOOD PLAN 建議,可以最佳化您的資料庫效能,並在 Azure 入口網站中顯示它們,還可以透過 T-SQLREST API 來公開它們。Automatic tuning identifies CREATE INDEX, DROP INDEX, and FORCE LAST GOOD PLAN recommendations that can optimize your database performance and shows them in Azure portal, and exposes them through T-SQL and REST API. 若要深入了解 FORCE LAST GOOD PLAN,並設定自動調整選項,透過 T-SQL,請參閱自動調整引進了自動計劃更正To learn more about FORCE LAST GOOD PLAN and configuring automatic tuning options through T-SQL, see Automatic tuning introduces automatic plan correction.

您可以使用入口網站來手動套用調整建議,或是讓自動調整為您自動套用調整建議。You can either manually apply tuning recommendations using the 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.

如果您手動套用調整建議,則無法使用自動效能驗證和反轉機制。In case you are manually applying tuning recommendations, the automatic performance validation, and reversal mechanisms are not available. 此外,手動套用建議事項會保持作用中和的建議清單中顯示 24-48 小時。In addition, manually applied recommendations will remain active and shown in the list of recommendations for 24-48 hrs. 之前,系統會自動提領它們。before the system automatically withdraws them. 如果您想要移除的建議更快,您可以手動方式捨棄它。If you would like to remove a recommendation sooner, you can manually discard it.

自動微調選項可以針對每個資料庫個別地啟用或停用,或可以在 SQL Database 伺服器上設定,並在從伺服器繼承設定的每個資料庫上套用。Automatic tuning options can be independently enabled or disabled per database, or they can be configured on SQL Database servers and applied on every database that inherits settings from the server. SQL Database 伺服器可以繼承 Azure 的自動調整設定預設值。SQL Database 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.

在伺服器上設定自動調整選項,並繼承屬於父代伺服器的資料庫設定,是設定自動調整的建議方法,因為這可簡化大量資料庫的自動調整選項管理。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.

後續步驟Next steps