自動微調

  • 文章

適用于：yesSQL Server 2017 (14.x) 及更新版本的 Yes Azure SQL Database

「自動調整」是一種資料庫功能，可深入探索潛在的查詢效能問題、建議解決方法，並且自動修正找到的問題。

SQL Server 2017 中引進的自動調整 (14.x) ，每當偵測到潛在的效能問題，並可讓您套用更正動作，或讓 Database Engine 自動修正效能問題時通知您。 自動調整 SQL Server 可讓您識別並修正 查詢執行計畫選擇回歸所造成的效能問題。 Azure SQL Database 中的自動調整也會建立必要的索引，並卸載未使用的索引。 如需查詢執行計畫的詳細資訊，請參閱 執行計畫。

SQL Server Database Engine 會監視在資料庫上執行的查詢，並自動改善工作負載的效能。 Database Engine 具有內建的智慧機制，可藉由動態調整資料庫與工作負載，來自動調整和改善查詢的效能。 有兩個可用的自動調整功能：

  • 自動計畫修正 會識別有問題的查詢執行計畫，例如 參數敏感度或參數探查 問題，並藉由在回歸發生之前強制最後一個已知的良好計畫來修正查詢執行計畫相關的效能問題。 適用于：從 SQL Server 2017 (14.x) ) 和 Azure SQL Database 開始的 SQL Server (

  • 自動索引管理 會識別應該加入資料庫中的索引，以及應該移除的索引。 適用于：Azure SQL Database

為何需要自動調整？

傳統資料庫管理中的三個主要工作是監視工作負載、識別重要的 Transact-SQL 查詢，以及識別應該新增以改善效能的索引，或很少使用且可移除以改善效能的索引。 SQL Server Database Engine 提供您需要監視之查詢和索引的詳細見解。 不過，持續監視資料庫是一項困難且繁瑣的工作，特別是在處理許多資料庫時。 管理大量的資料庫可能無法有效率地執行。 您可以考慮使用自動微調功能，將一些監視和微調動作委派給 Database Engine，而不是手動監視和調整資料庫。

自動調整的運作方式為何？

自動調整是持續監視和分析程式，會持續瞭解工作負載的特性，並識別潛在的問題和改善。

Automatic tuning process

此程式可讓資料庫透過尋找哪些索引和計畫來動態調整工作負載，進而改善工作負載的效能，以及哪些索引會影響您的工作負載。 根據這些結果，自動調整會套用可改善工作負載效能的微調動作。 此外，自動調整會在實作任何變更之後持續監視資料庫的效能，以確保其能改善工作負載的效能。 未改善效能的任何動作都會自動還原。 此驗證程式是一項重要功能，可確保自動調整所做的任何變更不會降低工作負載的整體效能。

自動計劃修正

自動計畫更正是自動調整功能，可識別 執行計畫選擇回歸 ，並強制最後一個已知的良好計畫自動修正問題。 如需查詢執行計畫和查詢最佳化工具的詳細資訊，請參閱 查詢處理架構指南。

重要

自動計畫更正取決於資料庫中啟用的查詢存放區，以進行工作負載追蹤。

什麼是執行計畫選擇回歸？

SQL Server Database Engine 可能會使用不同的執行計畫來執行 Transact-SQL 查詢。 查詢計劃取決於統計資料、索引和其他因素。 應該用來執行 Transact-SQL 查詢的最佳計畫可能會隨著時間而變更，視這些因素中的變更而定。 在某些情況下，新計畫可能比前一個計畫更好，而新的計畫可能會導致效能回歸，例如 參數敏感度或參數探查 相關問題。

Query execution plan choice regression

每當發現計畫選擇回歸發生時，您應該找到先前良好的計畫，並強制使用它，而不是目前的計畫。 這可以使用 程式來完成 sp_query_store_force_plan 。 SQL Server 2017 中的資料庫引擎 (14.x) 提供回歸方案和建議更正動作的相關資訊。 此外，Database Engine 可讓您完全自動化此程式，並讓 Database Engine 修正與計畫變更相關的任何問題。

重要

在擷取基準之後，自動計畫更正應該用於資料庫相容性層級升級的範圍，以自動降低工作負載升級風險。 如需此使用案例的詳細資訊，請參閱 升級至較新的 SQL Server 期間保持效能穩定性。

自動計劃選擇更正

每當偵測到計畫選擇回歸時，Database Engine 就可以自動切換至最後一個已知的良好計畫。

Query execution plan choice correction

Database Engine 會自動偵測任何潛在的計畫選擇回歸，包括應該使用的計畫，而不是錯誤的計畫。 自動計畫更正強制產生的執行計畫將會與最後一個已知良好的計畫相同或類似。 由於產生的計畫可能與最後一個已知良好的計畫不相同，因此強制計畫的效能可能會有所不同。 在罕見的情況下，效能差異可能是顯著且負面的;在此情況下，自動計畫更正會自動停止嘗試強制取代計畫。

當 Database Engine 在回歸發生之前套用最後一個已知的良好計畫時，它會自動監視強制計畫的效能。 如果強制計畫不優於回歸計畫，則會取消強制執行新的計畫，而且 Database Engine 會編譯新的計畫。 如果 Database Engine 確認強制計畫比回歸計畫更好，則會保留強制計畫。 它會保留，直到重新編譯發生 (，例如在下一個統計資料更新或架構變更) 為止。 如需計畫強制和可強制之計畫類型的詳細資訊，請參閱 計畫強制限制。

注意

如果在驗證計畫強制動作之前重新開機 SQL Server 實例，則會自動取消強制執行該計畫。 否則，SQL Server 重新開機時會保存計畫強制。

啟用自動計畫選擇更正

您可以為每個資料庫啟用自動調整，並指定每次偵測到某些計畫變更回歸時，應該強制執行最後一個良好的計畫。 自動調整已使用下列命令啟用：

ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

啟用此選項之後，Database Engine 會自動強制任何估計 CPU 增加高於 10 秒的建議，或新方案中的錯誤數目高於建議方案中的錯誤數目，並確認強制計畫比目前的計畫更好。

其他選項 - 手動計畫選擇更正

如果沒有自動調整，使用者必須定期監視系統，並尋找已回歸的查詢。 如果有任何計畫已回歸，則使用者應該找到先前良好的計畫，並使用程式強制取代目前的 sp_query_store_force_plan 計畫。 最佳做法是強制最後一個已知的良好計畫，因為較舊的計畫可能會因為統計資料或索引變更而無效。 強制最後一個已知良好計畫的使用者應該監視使用強制計畫執行的查詢效能，並確認強制計畫如預期般運作。 視監視和分析的結果而定，應該強制計畫，或使用者應該尋找另一種方式來優化查詢，例如重寫查詢。 手動強制計畫不應永遠強制，因為 Database Engine 應該能夠套用最佳計畫。 使用者或 DBA 最終應該使用 sp_query_store_unforce_plan 程式取消強制執行計畫，並讓 Database Engine 尋找最佳的計畫。

提示

或者，使用 查詢搭配強制計畫 查詢存放區檢視來尋找和取消強制執行計畫。

SQL Server 提供監視效能和修正查詢存放區中問題所需的所有必要檢視和程式。

在 SQL Server 2016 (13.x) 中，您可以使用查詢存放區系統檢視尋找計畫選擇回歸。 從 SQL Server 2017 (14.x) 開始，Database Engine 會偵測並顯示潛在的計畫選擇回歸，以及應該在 sys.dm_db_tuning_recommendations (Transact-SQL) DMV 中套用的建議動作。 DMV 會顯示問題的相關資訊、問題的重要性，以及所識別查詢、回歸計畫的識別碼、做為比較基準的計畫識別碼，以及可執行以修正問題的 Transact-SQL 語句。

類型 description Datetime score 詳細資料 ...
FORCE_LAST_GOOD_PLAN CPU 時間從 4 毫秒變更為 14 毫秒 3/17/2017 83 queryId recommendedPlanId regressedPlanId T-SQL
FORCE_LAST_GOOD_PLAN CPU 時間從 37 毫秒變更為 84 毫秒 3/16/2017 26 queryId recommendedPlanId regressedPlanId T-SQL

此檢視中的某些資料行會在下列清單中描述：

  • 建議動作 FORCE_LAST_GOOD_PLAN 的類型。
  • 描述，其中包含 Database Engine 認為此計畫變更可能是潛在的效能回歸的資訊。
  • 偵測到潛在回歸的日期時間。
  • 此建議的分數。
  • 有關偵測到計畫識別碼、回歸計畫的識別碼、應強制修正問題的計畫識別碼、可能套用以修正問題的 Transact-SQL 腳本等問題的詳細資料。詳細資料會以 JSON 格式儲存。

使用下列查詢來取得腳本，以修正問題，以及估計收益的其他資訊：

SELECT reason, score,
      script = JSON_VALUE(details, '$.implementationDetails.script'),
      planForceDetails.*,
      estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
                  * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
      error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            regressedPlanId int '$.regressedPlanId',
            recommendedPlanId int '$.recommendedPlanId',
            regressedPlanErrorCount int,
            recommendedPlanErrorCount int,
            regressedPlanExecutionCount int,
            regressedPlanCpuTimeAverage float,
            recommendedPlanExecutionCount int,
            recommendedPlanCpuTimeAverage float
          ) AS planForceDetails;

以下為結果集。

reason score 指令碼 query_id 目前plan_id 建議plan_id estimated_gain error_prone
CPU 時間從 3 毫秒變更為 46 毫秒 36 EXEC sp_query_store_force_plan 12， 17; 12 28 17 11.59 0

資料行 estimated_gain 代表建議計畫用於查詢執行而非目前計畫時所儲存的估計秒數。 如果收益大於 10 秒，建議的計畫應該強制而非目前的計畫。 例如，如果逾時或中止的執行 (比在建議的計畫中多) ，則資料 error_prone 行會設定為值 YES 。 容易出錯的計畫是建議計畫強制而非目前計畫的另一個原因。

雖然 Database Engine 提供識別計畫選擇回歸所需的所有資訊，但持續監視和修正效能問題可能會成為繁瑣的程式。 自動調整可讓此程式更容易。

注意

sys.dm_db_tuning_recommendations在重新開機資料庫引擎之後，不會保存 DMV 中的資料。 sqlserver_start_time使用sys.dm_os_sys_info中的資料行來尋找最後一個資料庫引擎啟動時間。

自動索引管理

在 Azure SQL 資料庫中，因為 Azure SQL Database 會了解您的工作負載，並確保永遠以最佳方式為資料編製索引，所以索引管理很簡單。 您的工作負載要達到最佳效能，有適當的索引設計非常重要，而且自動索引管理可以協助您將索引最佳化。 自動索引管理可以修正資料庫中索引編製不正確所造成的效能問題，或維護和改善現有資料庫結構描述的索引。 Azure SQL Database 中的自動調整會執行下列動作：

  • 識別可改善從資料表讀取資料的 Transact-SQL 查詢效能的索引。
  • 識別在可移除的較長時間內未使用的備援索引或索引。 移除不必要的索引可改善更新資料表中資料的查詢效能。

為什麼需要索引管理？

索引會加速從資料表讀取資料的一些查詢，不過它們可能會使更新資料的查詢變慢。 您需要仔細分析建立索引的時機，以及要將哪些資料行包含在索引中。 某些索引在一段時間後可能不再需要。 因此，您必須定期識別並卸載不會帶來任何優點的這些索引。 如果您忽略未使用的索引，更新資料的查詢效能將會降低，而不會對讀取資料的查詢有任何好處。 未使用的索引也會影響整體系統效能，因為其他的更新需要不必要的記錄。

找出一組最佳的索引，能改善查詢從資料表讀取資料時的效能，並對需要持續進行複雜分析的更新之影響降到最低。

Azure SQL Database 會使用內建智慧和進階規則來分析查詢、識別最適合您目前工作負載的索引，以及識別可能需要移除的索引。 Azure SQL Database 可確保您有一組最基本的索引，可將讀取資料的查詢優化，並將對其他查詢的影響降到最低。

自動索引管理

除了偵測之外，Azure SQL Database 也可以自動套用已識別的建議。 如果您發現內建規則可改善資料庫的效能，您可以讓 Azure SQL Database 自動管理您的索引。

若要在 Azure SQL Database 中啟用自動調整，並允許自動調整功能完全管理您的工作負載，請參閱 使用 Azure 入口網站在 Azure SQL Database 中啟用自動調整。

當 Azure SQL Database 套用 CREATE INDEX 或 DROP INDEX 建議時，它會自動監視受索引影響的查詢效能。 只有在改善受影響查詢的效能時，才會保留新的索引。 如果因為缺少索引而執行速度較慢的某些查詢，則會自動重新建立已卸載的索引。

自動索引管理考量

在 Azure SQL Database 中建立必要索引所需的動作可能會耗用資源，並暫時影響工作負載效能。 為了將索引建立對工作負載效能的影響降到最低，Azure SQL Database 會尋找任何索引管理作業的適當時間範圍。 如果資料庫需要資源來執行工作負載，而且當資料庫有足夠的未使用資源可用於維護工作時，就會重新開機調整動作。 自動索引管理的其中一項重要功能是驗證動作成效。 當 Azure SQL Database 建立或卸載索引時，監視程式會分析工作負載的效能，以確認動作已改善整體效能。 如果未大幅改善，則會立即還原動作。 如此一來，Azure SQL Database 可確保自動調整動作不會對工作負載的效能造成負面影響。 基礎結構描述的維護作業清楚了解自動調整所建立的索引。 自動建立索引的存在並不會封鎖卸除或重新命名資料行這類的結構描述變更。 卸載相關資料表或資料行時，Azure SQL Database 自動建立的索引會立即卸載。

替代方式 - 手動索引管理

如果沒有自動索引管理，使用者或 DBA 必須手動查詢 sys.dm_db_missing_index_details (Transact-SQL) 檢視，或使用 Management Studio 中的 [效能儀表板] 報表來尋找可能改善效能的索引、使用此檢視中提供的詳細資料建立索引，以及手動監視查詢的效能。 若要尋找應該卸載的索引，使用者應該監視索引的操作使用量統計資料，以尋找很少使用的索引。

Azure SQL Database 可簡化此程式。 Azure SQL Database 會分析您的工作負載、識別可使用新索引更快執行的查詢，並識別未使用或重複的索引。 如需找出應變更的索引詳細資訊，可在 Azure 入口網站中的尋找索引建議中找到。

另請參閱

ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL)
sys.database_automatic_tuning_options (Transact-SQL)
sys.dm_db_tuning_recommendations (Transact-SQL)
sys.dm_db_missing_index_details (Transact-SQL)
sp_query_store_force_plan (Transact-SQL)
sp_query_store_unforce_plan (Transact-SQL)
sys.database_query_store_options (Transact-SQL)
sys.dm_os_sys_info (Transact-SQL)
JSON 函式
執行計畫
效能的監視與微調
效能監視及微調工具
使用查詢存放區監視效能
查詢調整小幫手