SQL Database 的效能建議Performance recommendations for SQL Database

Azure SQL Database 會學習及適應您的應用程式。Azure SQL Database learns and adapts with your application. 它會提供自訂的建議,讓您將 SQL 資料庫的效能最大化。It provides customized recommendations that enable you to maximize the performance of your SQL databases. SQL Database 會持續評估及分析 SQL 資料庫的使用歷程記錄。SQL Database continuously assesses and analyzes the usage history of your SQL databases. 根據資料庫的獨特工作負載模式提供建議,並協助改善效能。The recommendations that are provided are based on database-unique workload patterns and help improve performance.

提示

自動調整是自動調整一些最常見資料庫效能問題的建議方法。Automatic tuning is the recommended method to automatically tune some of the most common database performance issues. 查詢效能深入解析是基本 Azure SQL Database 效能監視需求的建議方法。Query Performance Insights is the recommended method for basic Azure SQL Database performance monitoring needs. Azure SQL 分析 是運用自動化效能疑難排解的內建智慧,執行資料庫效能大規模進階監視的建議方法。Azure SQL Analytics is the recommended method for advanced monitoring of database performance at scale, with built-in intelligence for automated performance troubleshooting.

建立索引建議Create index recommendations

SQL Database 會持續監視正在執行的查詢,並找出可改善效能的索引。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.indexes 檢視,可以得知哪些是自動建立的索引。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. SQL Database 會使用此程序來確保建議可改善資料庫效能。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

除了偵測遺漏的索引,SQL Database 還會持續分析現有索引的效能。Besides detecting missing indexes, 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

當您有一或多個查詢不斷被重新編譯,但結果都是相同的查詢執行計劃時,即會出現參數化查詢建議。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.

針對 SQL Server 發出的每個查詢一開始需要重新編譯,以產生執行計畫。Every query that's issued against SQL Server 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. 在資料庫上設定強制參數化選項,可以改變此 SQL Server 行為。This SQL Server 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. SQL Database 建議程式有一項安全機制,會在偵測到效能衰退時,自動還原所套用的建議。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.

當 SQL Database 服務注意到 SQL 資料庫上發生結構描述數目異常相關的 SQL 錯誤時,即會出現修正結構描述問題建議。Fix schema issues recommendations appear when the SQL Database service notices an anomaly in the number of schema-related SQL errors that are happening on your SQL 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.

「結構描述問題」是 SQL Server 中的語法錯誤類別。“Schema issues” are a class of syntax errors in SQL Server. 當 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 資料庫上發生結構描述數目異常相關的 SQL 錯誤時,即會出現「修正結構描述問題」建議。The “Fix schema issue” recommendation appears when the Azure SQL Database service notices an anomaly in the number of schema-related SQL errors that are happening on your SQL database. 下表顯示與結構描述問題相關的錯誤:The following table shows the errors that are related to schema issues:

SQL 錯誤碼SQL error code MessageMessage
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. 入口網站中的資料庫可以透過存取列出的所有建議Get AzSqlDatabaseRecommendedAction API。All recommendations listed in the portal for a database can be accessed through Get-AzSqlDatabaseRecommendedAction API.

後續步驟Next steps

監視建議,並繼續套用建議以改善效能。Monitor your recommendations and continue to apply them to refine performance. 資料庫工作負載會動態地持續變更。Database workloads are dynamic and change continuously. SQL Database 建議程式會繼續監視並提供可能改善資料庫效能的建議。SQL Database Advisor continues to monitor and provide recommendations that can potentially improve your database's performance.