Database Advisor Azure SQL Database 的效能建議Database Advisor performance recommendations for Azure SQL Database

適用於: Azure SQL Database

Azure SQL Database 會學習及適應您的應用程式。Azure SQL Database learns and adapts with your application. Azure SQL Database 有許多資料庫顧問,可提供可讓您將效能最大化的自訂建議。Azure SQL Database has a number of database advisors that provide customized recommendations that enable you to maximize performance. 這些資料庫顧問會持續評估並分析使用量歷程記錄,並根據協助改善效能的工作負載模式提供建議。These database advisors continuously assess and analyze the usage history and provide recommendations based on workload patterns that help improve performance.

效能總覽Performance overview

效能總覽可提供資料庫效能的摘要,並可協助您進行效能調整和疑難排解。Performance overview provides a summary of your database performance, and helps you with performance tuning and troubleshooting.

Azure SQL Database 的效能總覽

  • [建議] 圖格會提供適用於資料庫的微調建議分解 (如果有多個建議,則會顯示前三個)。The Recommendations tile provides a breakdown of tuning recommendations for your database (top three recommendations are shown if there are more). 按一下此圖格會帶您前往 效能建議選項Clicking this tile takes you to Performance recommendation options .
  • [微調活動] 圖格會提供資料庫進行中和已完成微調動作的摘要,讓您快速檢視調整活動的歷程記錄。The Tuning activity tile provides a summary of the ongoing and completed tuning actions for your database, giving you a quick view into the history of tuning activity. 按一下此圖格會帶您前往您的資料庫的完整微調記錄檢視。Clicking this tile takes you to the full tuning history view for your database.
  • 自動調整 磚會顯示資料庫的 自動調整 設定, (自動套用至資料庫) 的微調選項。The Auto-tuning tile shows the auto-tuning configuration for your database (tuning options that are automatically applied to your database). 按一下此圖格會開啟 [自動化組態] 對話方塊。Clicking this tile opens the automation configuration dialog.
  • [資料庫查詢] 圖格會顯示資料庫的查詢效能摘要 (整體 DTU 使用量和排名最前面的資源取用查詢)。The Database queries tile shows the summary of the query performance for your database (overall DTU usage and top resource consuming queries). 按一下此圖格,即會帶您前往 [ 。Clicking this tile takes you to Query Performance Insight .

效能建議選項Performance recommendation options

Azure SQL Database 提供的效能建議選項如下:Performance recommendation options available in Azure SQL Database are:

效能建議Performance recommendation 單一資料庫和集區資料庫支援Single database and pooled database support 實例資料庫支援Instance database support
建立索引建議 -建議建立可改善工作負載效能的索引。Create index recommendations - Recommends creation of indexes that may improve performance of your workload. Yes No
Drop index 建議 -建議每日移除重複和重複的索引,但不包括唯一索引,以及長時間未使用的索引 ( # B0 90 天) 。Drop index recommendations - Recommends removal of 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. Premium 和業務關鍵服務層級不支援卸載未使用的索引。Dropping unused indexes is not supported for Premium and Business Critical service tiers. Yes No
參數化查詢建議 (預覽) -當您有一或多個經常重新編譯但最後有相同查詢執行計畫的查詢時,建議使用強制參數化。Parameterize queries recommendations (preview) - Recommends forced parameterization in cases when you have one or more queries that are constantly being recompiled but end up with the same query execution plan. Yes No
修正架構問題建議 (預覽) -當 Azure SQL Database 注意到您的資料庫上發生架構相關的 SQL 錯誤時,就會出現架構修正的建議。Fix schema issues recommendations (preview) - Recommendations for schema correction appear when Azure SQL Database notices an anomaly in the number of schema-related SQL errors that are happening on your database. Microsoft 即將淘汰「修正結構描述問題」的建議。Microsoft is currently deprecating "Fix schema issue" recommendations. Yes No

Azure SQL Database 的效能建議

若要套用效能建議,請參閱套用 建議To apply performance recommendations, see applying recommendations. 若要查看建議的狀態,請參閱 監視作業To view the status of recommendations, see Monitoring operations.

您也可以找到過去所套用微調動作的完整歷程記錄。You can also find complete history of tuning actions that were applied in the past.

建立索引建議Create index recommendations

Azure SQL Database 會持續監視正在執行的查詢,並找出可改善效能的索引。Azure SQL Database continuously monitors the queries that are running and identifies the indexes that could improve performance. 確信遺漏特定索引之後,就會建立新的 [建立索引] 建議。After there's enough confidence that a certain index is missing, a new Create index recommendation is created.

評估一段時間之後,Azure SQL Database 會確定索引所能提升的效能。Azure SQL Database builds confidence by estimating the performance gain the index would bring through time. 根據評估的顯現效能,將建議分類為高、中或低。Depending on the estimated performance gain, recommendations are categorized as high, medium, or low.

使用建議建立的索引一律會標記為自動建立的索引。Indexes that are created by using recommendations are always flagged as auto-created indexes. 您可以藉由查看 sys. 索引視圖來查看自動建立的索引。You can see which indexes are auto-created by looking at the sys.indexes view. 自動建立的索引不會封鎖 ALTER/RENAME 命令。Auto-created indexes don't block ALTER/RENAME commands.

如果您嘗試卸除有自動建立之索引的資料行。If you try to drop the column that has an auto-created index over it, the command passes. 自動建立的索引會隨該命令卸除。The auto-created index is dropped with the command as well. 一般索引會對已編製索引的資料行封鎖 ALTER/RENAME 命令。Regular indexes block the ALTER/RENAME command on columns that are indexed.

套用索引建立建議之後,Azure SQL Database 會比較基準效能和查詢效能。After the create index recommendation is applied, Azure SQL Database compares the performance of the queries with the baseline performance. 如果新索引改善了效能,建議就會標示為成功並可取得影響報告。If the new index improved performance, the recommendation is flagged as successful and the impact report is available. 如果索引未改善效能,則會自動還原。If the index didn't improve performance, it's automatically reverted. Azure SQL Database 使用此程式來確保建議能改善資料庫效能。Azure SQL Database uses this process to ensure that recommendations improve database performance.

任何 建立索引 建議都有輪詢原則,如果資料庫或集區的資源使用量很高時不允許套用建議。Any create index recommendation has a back-off policy that doesn't allow applying the recommendation if the resource usage of a database or pool is high. 輪詢原則會將 CPU、資料 IO、記錄 IO 和可用儲存體列入考慮。The back-off policy takes into account CPU, Data IO, Log IO, and available storage.

如果過去30分鐘內的 CPU、資料 IO 或記錄 IO 高於80%,則建立索引建議會延後。If CPU, data IO, or log IO is higher than 80% in the previous 30 minutes, the create index recommendation is postponed. 如果在建立索引後,可用的儲存體低於 10%,則建議會進入錯誤狀態。If the available storage will be below 10% after the index is created, the recommendation goes into an error state. 如果幾天後,自動調整仍認為該索引有所幫助,則程序會重新開始。If, after a couple of days, automatic tuning still believes that the index would be beneficial, the process starts again.

此程序會重複進行,直到有足夠的儲存體可用來建立索引,或直到索引不再是有所幫助的狀態為止。This process repeats until there's enough available storage to create an index, or until the index isn't seen as beneficial anymore.

卸除索引建議Drop index recommendations

除了偵測遺漏的索引,Azure SQL Database 會持續分析現有索引的效能。Besides detecting missing indexes, Azure SQL Database continuously analyzes the performance of existing indexes. 如果未使用索引,Azure SQL Database 會建議將它卸除。If an index is not used, Azure SQL Database recommends dropping it. 在兩種情況下會建議使用卸除索引:Dropping an index is recommended in two cases:

  • 索引是另一個索引的複本 (編製和內含索引的資料行、分割、結構描述及篩選器相同)。The index is a duplicate of another index (same indexed and included column, partition schema, and filters).
  • 索引長時間都未使用 (93 天)。The index hasn't been used for a prolonged period (93 days).

卸除索引建議在實作後也會經過驗證。Drop index recommendations also go through the verification after implementation. 如果效能提升,即可取得影響報告。If the performance improves, the impact report is available. 如果效能降低,則會還原建議。If performance degrades, the recommendation is reverted.

(預覽參數化查詢建議) Parameterize queries recommendations (preview)

當您有一或多個查詢不斷被重新編譯,但結果都是相同的查詢執行計劃時,即會出現 參數化查詢 建議。Parameterize queries recommendations appear when you have one or more queries that are constantly being recompiled but end up with the same query execution plan. 這種狀況會製造套用強制參數化的機會。This condition creates an opportunity to apply forced parameterization. 而強制參數化允許查詢計畫納入快取並在未來重複使用,以改善效能並減少資源使用量。Forced parameterization, in turn, allows query plans to be cached and reused in the future, which improves performance and reduces resource usage.

每個查詢一開始都需要進行編譯,以產生執行計畫。Every query initially needs to be compiled to generate an execution plan. 每個產生的計畫都會新增至計畫快取。Each generated plan is added to the plan cache. 相同查詢的後續執行可以從快取中重複使用這個計畫,而不需要進一步編譯。Subsequent executions of the same query can reuse this plan from the cache, which eliminates the need for additional compilation.

具有非參數化值的查詢可能會導致效能超出負荷,因為每當非參數化的值不同時,執行計畫就會重新編譯。Queries with non-parameterized values can lead to performance overhead because the execution plan is recompiled each time the non-parameterized values are different. 在許多情況下,具有不同參數值的相同查詢會產生相同的執行計畫。In many cases, the same queries with different parameter values generate the same execution plans. 不過,這些計劃仍會個別加入至計畫快取。These plans, however, are still separately added to the plan cache.

重新編譯執行計畫的程序會使用資料庫資源、增加查詢持續時間,以及造成計畫快取溢位。The process of recompiling execution plans uses database resources, increases the query duration time, and overflows the plan cache. 這些事件接著會導致計劃從快取中收回。These events, in turn, cause plans to be evicted from the cache. 您可以藉由在資料庫上設定強制參數化選項來改變此行為。This behavior can be altered by setting the forced parameterization option on the database.

為了協助您評估此建議的影響,我們提供了實際 CPU 使用量和預計 CPU 使用量 (如同已套用建議) 之間的比較。To help you estimate the impact of this recommendation, you are provided with a comparison between the actual CPU usage and the projected CPU usage (as if the recommendation were applied). 這項建議可協助您節省 CPU 資源。This recommendation can help you gain CPU savings. 也可以協助您減少計畫快取的查詢持續時間和負擔,這表示多個計畫可以留在快取中並加以重複使用。It can also help you decrease query duration and overhead for the plan cache, which means that more of the plans can stay in the cache and be reused. 您可以按一下 [套用] 命令,快速套用此建議。You can apply this recommendation quickly by selecting the Apply command.

套用此建議之後,即可在幾分鐘內於資料庫上啟用強制參數化。After you apply this recommendation, it enables forced parameterization within minutes on your database. 它會啟動可持續約 24 小時的監視程序。It starts the monitoring process, which lasts for approximately 24 hours. 經過這段期間之後,您即可看到驗證報告。After this period, you can see the validation report. 此報告顯示資料庫在套用建議前後 24 小時的 CPU 使用量。This report shows the CPU usage of your database 24 hours before and after the recommendation has been applied. Azure SQL Database Advisor 具有安全機制,會在偵測到效能回歸時,自動還原套用的建議。Azure SQL Database Advisor has a safety mechanism that automatically reverts the applied recommendation if performance regression has been detected.

修正結構描述問題建議 (預覽)Fix schema issues recommendations (preview)

重要

Microsoft 即將淘汰「修正結構描述問題」的建議。Microsoft is currently deprecating "Fix schema issue" recommendations. 建議您使用 Intelligent Insights 來監視資料庫效能問題,包括「修正結構描述問題」建議先前涵蓋的結構描述問題。We recommend that you use Intelligent Insights to monitor your database performance issues, including schema issues that the "Fix schema issue" recommendations previously covered.

當 Azure SQL Database 注意到您的資料庫上發生架構相關的 SQL 錯誤時,就會出現 修正架構問題 的建議。Fix schema issues recommendations appear when Azure SQL Database notices an anomaly in the number of schema-related SQL errors that are happening on your database. 您的資料庫在一小時內遇到多個結構描述相關的錯誤時 (無效的資料行名稱、無效的物件名稱等),通常會出現此建議。This recommendation typically appears when your database encounters multiple schema-related errors (invalid column name, invalid object name, and so on) within an hour.

「架構問題」是一種語法錯誤的類別。"Schema issues" are a class of syntax errors. 當 SQL 查詢定義與資料庫結構描述定義不符時,就會發生這類問題。They occur when the definition of the SQL query and the definition of the database schema aren't aligned. 例如,查詢預期的其中一個資料行可能在目標資料表中遺失,反之亦然。For example, one of the columns that's expected by the query might be missing in the target table or vice-versa.

當 Azure SQL Database 注意到您的資料庫上發生架構相關的 SQL 錯誤時,會出現「修正架構問題」建議。The "Fix schema issue" recommendation appears when Azure SQL Database notices an anomaly in the number of schema-related SQL errors that are happening on your database. 下表顯示與結構描述問題相關的錯誤:The following table shows the errors that are related to schema issues:

SQL 錯誤碼SQL error code 訊息Message
201201 程序或函數 '' 必須有參數 ' ',但未提供。Procedure or function ' ' expects parameter ' ', which was not supplied.
207207 無效的資料行名稱 '*'。Invalid column name '*'.
208208 無效的物件名稱 '*'。Invalid object name '*'.
213213 資料行名稱或提供的數值數量與資料表定義不相符。Column name or number of supplied values does not match table definition.
28122812 找不到預存程序 ' *'。Could not find stored procedure '*'.
81448144 程序或函數 * 指定了太多的引數。Procedure or function * has too many arguments specified.

自訂應用程式Custom applications

開發人員可能會考慮使用 Azure SQL Database 的效能建議來開發自訂應用程式。Developers might consider developing custom applications using performance recommendations for Azure SQL Database. 您可以透過 AzSqlDatabaseRecommendedAction API 存取在入口網站中針對資料庫所列的所有建議。All recommendations listed in the portal for a database can be accessed through Get-AzSqlDatabaseRecommendedAction API.

後續步驟Next steps