尋找和套用效能建議Find and apply performance recommendations

適用於: Azure SQL Database

您可以使用 Azure 入口網站來尋找效能建議,讓您可以在 Azure SQL Database 中優化資料庫的效能,或更正工作負載中所識別的某些問題。You can use the Azure portal to find performance recommendations that can optimize performance of your database in Azure SQL Database or to correct some issue identified in your workload. Azure 入口網站中的 [ 效能建議 ] 頁面可讓您根據潛在的影響來尋找最佳建議。The Performance recommendation page in the Azure portal enables you to find the top recommendations based on their potential impact.

檢視建議Viewing recommendations

若要查看並套用效能建議,您需要在 Azure 中 (AZURE RBAC) 許可權的正確 azure 角色型存取控制 To view and apply performance recommendations, you need the correct Azure role-based access control (Azure RBAC) permissions in Azure. 需要 讀取者SQL DB 參與者 權限,才能檢視建議,以及需要 擁有者SQL DB 參與者 權限,才能執行任何動作;建立或卸除索引並取消建立索引。Reader, SQL DB Contributor permissions are required to view recommendations, and Owner, SQL DB Contributor permissions are required to execute any actions; create or drop indexes and cancel index creation.

使用下列步驟,在 Azure 入口網站上尋找效能建議:Use the following steps to find performance recommendations on the Azure portal:

  1. 登入 Azure 入口網站Sign in to the Azure portal.
  2. 移至 [所有服務 > SQL 資料庫],然後選取您的資料庫。Go to All services > SQL databases, and select your database.
  3. 瀏覽至 [效能建議] 來檢視適用於所選資料庫的可用建議。Navigate to Performance recommendation to view available recommendations for the selected database.

資料表中顯示的效能建議類似於下圖:Performance recommendations are shown in the table similar to the one shown on the following figure:

螢幕擷取畫面顯示資料表中具有動作和建議描述的效能建議。

依照可能帶來的效能影響排序,建議分成下列類別:Recommendations are sorted by their potential impact on performance into the following categories:

影響Impact 描述Description
High 高影響建議提供最明顯的效能影響。High impact recommendations should provide the most significant performance impact.
Medium 中度影響建議會改善效能,但不顯著。Medium impact recommendations should improve performance, but not substantially.
Low 低影響建議比沒有建議時提供更好的效能,但改善可能不顯著。Low impact recommendations should provide better performance than without, but improvements might not be significant.

注意

Azure SQL Database 必須至少監視活動一整天,才能找出一些建議。Azure SQL Database needs to monitor activities at least for a day in order to identify some recommendations. 相較於隨機蹦出的零星活動,一致的查詢模式更有利於 Azure SQL Database 最佳化。The Azure SQL Database can more easily optimize for consistent query patterns than it can for random spotty bursts of activity. 如果 [效能建議] 頁面中目前沒有可用的建議,該頁面會提供訊息說明原因。If recommendations are not currently available, the Performance recommendation page provides a message explaining why.

您也可以檢視歷程記錄作業的狀態。You can also view the status of the historical operations. 選取建議或狀態以查看詳細資訊。Select a recommendation or status to see more information.

以下是 Azure 入口網站中的「建立索引」建議的範例。Here is an example of the "Create index" recommendation in the Azure portal.

建立索引

套用建議Applying recommendations

Azure SQL Database 可讓您使用下列 3 個選項的其中任一選項來控制建議的啟用方式:Azure SQL Database gives you full control over how recommendations are enabled using any of the following three options:

  • 一次套用一個個別的建議。Apply individual recommendations one at a time.
  • 啟用自動調整功能,以自動套用建議。Enable the Automatic tuning to automatically apply recommendations.
  • 若要手動實作建議,請針對您的資料庫執行建議的 T-SQL 指令碼。To implement a recommendation manually, run the recommended T-SQL script against your database.

選取任何建議來檢視其詳細資料,然後按一下 [檢視指令碼] 來檢閱將如何建立建議的確切詳細資料。Select any recommendation to view its details and then click View script to review the exact details of how the recommendation is created.

套用建議時,資料庫仍保持連線 -- 使用效能建議或自動調整功能不會使資料庫離線。The database remains online while the recommendation is applied -- using performance recommendation or automatic tuning never takes a database offline.

套用個別的建議Apply an individual recommendation

您可以一次檢閱並接受一個建議。You can review and accept recommendations one at a time.

  1. 選取 [建議] 頁面上的某個建議。On the Recommendations page, select a recommendation.

  2. 在 [詳細資料] 頁面上,按一下 [套用 ] 按鈕。On the Details page, click the Apply button.

    套用建議

針對資料庫套用選取的建議。Selected recommendations are applied on the database.

從清單中移除建議Removing recommendations from the list

如果建議的清單中包含您想從清單中移除的項目,您可以捨棄該建議:If your list of recommendations contains items that you want to remove from the list, you can discard the recommendation:

  1. 在 [建議] 清單中選取建議,以開啟詳細資料。Select a recommendation in the list of Recommendations to open the details.
  2. 在 [詳細資料] 頁面上按一下 [捨棄]。Click Discard on the Details page.

如有需要,您可以將捨棄的項目加回到 建議 清單:If desired, you can add discarded items back to the Recommendations list:

  1. 在 [建議] 頁面上按一下 [檢視已捨棄]。On the Recommendations page, click View discarded.
  2. 從清單中選取捨棄的項目以檢視其詳細資料。Select a discarded item from the list to view its details.
  3. (選擇性) 按一下 [復原捨棄],將索引加回到 建議 的主要清單。Optionally, click Undo Discard to add the index back to the main list of Recommendations.

注意

請注意,如果啟用 SQL Database 自動微調,且您以手動方式捨棄清單中的建議,就永遠不會自動套用這類建議。Please note that if SQL Database Automatic tuning is enabled, and if you have manually discarded a recommendation from the list, such recommendation will never be applied automatically. 捨棄建議是一個便利的方式,可在要求不得套用特定建議時,讓使用者可以啟用自動調整。Discarding a recommendation is a handy way for users to have Automatic tuning enabled in cases when requiring that a specific recommendation shouldn’t be applied. 您可以選取 [復原捨棄] 選項,將捨棄的建議新增回 [建議] 清單,從而還原這個行為。You can revert this behavior by adding discarded recommendations back to the Recommendations list by selecting the Undo Discard option.

啟用自動微調Enable automatic tuning

您可以將資料庫設定為自動執行建議。You can set your database to implement recommendations automatically. 當建議可供使用時會自動套用建議。As recommendations become available, they are automatically applied. 因為所有建議都由服務管理,所以若對效能產生負面影響,就會還原該建議。As with all recommendations managed by the service, if the performance impact is negative, the recommendation is reverted.

  1. 在 [建議] 頁面上按一下 [自動化]:On the Recommendations page, click Automate:

    建議程式設定

  2. 選取要自動執行的動作:Select actions to automate:

    螢幕擷取畫面,顯示要在哪裡選取要自動執行的動作。

注意

請注意,DROP_INDEX 選項目前與使用分割區切換和索引提示的應用程式並不相容。Please note that DROP_INDEX option is currently not compatible with applications using partition switching and index hints.

選取所需的組態後,按一下 [套用]。Once you have selected your desired configuration, click Apply.

透過 T-sql 手動套用建議Manually apply recommendations through T-SQL

選取任何建議,然後按一下 [檢視指令碼] 。Select any recommendation and then click View script. 對資料庫執行這個指令碼,以手動套用建議。Run this script against your database to manually apply the recommendation.

,因此建議您在建立這些索引之後監視索引,以確認它們能夠提高效能,且於必要時調整或刪除它們。Indexes that are manually executed are not monitored and validated for performance impact by the service so it is suggested that you monitor these indexes after creation to verify they provide performance gains and adjust or delete them if necessary. 如需有關建立索引的詳細資訊,請參閱 CREATE INDEX (Transact-SQL)For details about creating indexes, see CREATE INDEX (Transact-SQL). 此外,手動套用的建議將維持使用中狀態,並顯示于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.

取消建議Canceling recommendations

可以取消處於 擱置中驗證中成功 狀態的建議。Recommendations that are in a Pending, Validating, or Success status can be canceled. 狀態為 執行中 的建議無法取消。Recommendations with a status of Executing cannot be canceled.

  1. 在 [調整歷程記錄] 區域中選取建議,以開啟 [建議詳細資料] 頁面。Select a recommendation in the Tuning History area to open the recommendations details page.
  2. 按一下 [取消] 以中止套用建議的程序。Click Cancel to abort the process of applying the recommendation.

監視作業Monitoring operations

套用建議時可能不會立即執行。Applying a recommendation might not happen instantaneously. 入口網站會提供有關建議狀態的詳細資料。The portal provides details regarding the status of recommendation. 索引有下列可能的狀態:The following are possible states that an index can be in:

狀態Status 描述Description
PendingPending 已收到套用建議命令,且已排程執行。Apply recommendation command has been received and is scheduled for execution.
執行中Executing 正在套用建議。The recommendation is being applied.
ValidatingValidating 成功套用建議,而服務正在衡量益處。Recommendation was successfully applied and the service is measuring the benefits.
SuccessSuccess 已成功套用建議,並證實有益處。Recommendation was successfully applied and benefits have been measured.
錯誤Error 套用建議程序期間發生錯誤。An error occurred during the process of applying the recommendation. 這可能是暫時性問題,也可能是資料表的結構描述變更,造成指令碼不再有效。This can be a transient issue, or possibly a schema change to the table and the script is no longer valid.
還原Reverting 已套用建立但被認為無助於效能,正在自動還原。The recommendation was applied, but has been deemed non-performant and is being automatically reverted.
已還原Reverted 已還原建議。The recommendation was reverted.

按一下清單中正在處理的建議以查看其詳細資訊:Click an in-process recommendation from the list to see more information:

顯示同進程建議清單的螢幕擷取畫面。

還原建議Reverting a recommendation

如果您使用效能建議來套用建議 (表示您未手動執行 T-SQL 指令碼),如果建議程式發現會對效能造成負面影響,它將會自動還原變更。If you used the performance recommendations to apply the recommendation (meaning you did not manually run the T-SQL script), it automatically reverts the change if it finds the performance impact to be negative. 如果您因為任何原因想要還原建議,您可以執行以下步驟:If for any reason you simply want to revert a recommendation, you can do the following:

  1. 在 [調整歷程記錄] 區域中選取已成功套用的建議。Select a successfully applied recommendation in the Tuning history area.
  2. 在 [建議詳細資料] 頁面上按一下 [還原]。Click Revert on the recommendation details page.

建議的索引

監視索引建議的效能影響Monitoring performance impact of index recommendations

成功實作建議之後 (目前僅提供索引作業和參數化查詢建議),您可以按一下 [建議詳細資料] 頁面上的 查詢深入解析 來開啟 查詢效能深入解析,並查看排名最前面查詢對效能的影響。After recommendations are successfully implemented (currently, index operations and parameterize queries recommendations only), you can click Query Insights on the recommendation details page to open Query Performance Insights and see the performance impact of your top queries.

監視效能影響

總結Summary

Azure SQL Database 提供改善資料庫效能的建議。Azure SQL Database provides recommendations for improving database performance. 藉由提供 T-SQL 指令碼,您會獲得最佳化資料庫的協助,並最終改善查詢效能。By providing T-SQL scripts, you get assistance in optimizing your database and ultimately improving query performance.

下一步Next steps

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

其他資源Additional resources