自動微調
適用於：
SQL Server 2017 (14.x) 和更新版本 Azure SQL DatabaseAzure SQL 受控執行個體
「自動調整」是一種資料庫功能，可深入探索潛在的查詢效能問題、建議解決方法，並且自動修正找到的問題。
SQL Server 2017 (14.x) 中的自動調整只要偵測到潛在的效能問題時就會通知您，並可讓您套用矯正措施，或可讓資料庫引擎自動修正效能問題。 自動調整 SQL Server 可識別並修正查詢執行計畫選擇迴歸所造成的效能問題。 Azure SQL Database 中的自動調整也會建立必要的索引，並卸除未使用的索引。 如需查詢執行計畫的詳細資訊，請參閱執行計畫。
SQL Server 資料庫引擎會監視在資料庫上執行的查詢，並可自動提升工作負載效能。 資料庫引擎具備的內建智慧機制可自動調整，並可透過動態調整工作負載的資料庫來提升查詢效能。 有兩個可用的自動調整功能：
自動計畫修正會識別有問題的查詢執行計畫，例如參數敏感度或參數探查問題，並藉由在迴歸發生之前強制執行最後一個已知的良好計畫來修正查詢執行計畫相關的效能問題。 適用於：SQL Server (從 SQL Server 2017 (14.x) 開始) 與 Azure SQL Database 和 Azure SQL 受控執行個體
自動索引管理會識別應該在資料庫中新增的索引，以及應該移除的索引。 適用於：Azure SQL Database
為何需要自動調整？
傳統資料庫管理中的三個主要工作是監視工作負載、識別重要的 Transact-SQL 查詢，以及識別應該新增以改善效能的索引，或很少使用且可移除以改善效能的索引。 SQL Server 資料庫引擎會提供您需要監視之查詢和索引的詳細解析。 不過，持續監視資料庫是冗長乏味的艱辛工作，尤其是在處理許多資料庫時。 管理大量的資料庫可能無法有效率地執行。 您可以考慮使用自動調整功能將一些監視和微調動作委派給資料庫引擎，而不是監視和手動調整您的資料庫。
自動調整的運作方式為何？
自動調整會持續不斷地監視和分析程序，藉以了解您的工作負載特性，並找出潛在的問題和改進方式。
此程序可讓資料庫藉由找出可能改善工作負載效能的索引和計畫，以及找出會影響工作負載的索引，動態調整您的工作負載。 根據這些調查結果，自動調整會套用可改善工作負載效能的調整動作。 此外，自動調整會在實作任何變更之後持續監視資料庫的效能，以確保其能改善工作負載的效能。 未能改善效能的任何動作都會自動還原。 此驗證程序是一項重要功能，可確保自動調整所做的任何變更不會降低工作負載的整體效能。
自動計劃修正
自動計畫修正是自動調整功能，可識別執行計畫選擇迴歸，並強制執行最後一個已知的良好計畫以自動修正問題。 如需查詢執行計畫與查詢最佳化工具的詳細資訊，請參閱查詢處理架構指南。
重要
自動計畫修正取決於資料庫中所啟用以進行工作負載追蹤的查詢存放區。
什麼是執行計畫選擇迴歸？
SQL Server 資料庫引擎可能會使用不同的執行計畫來執行 Transact-SQL 查詢。 查詢計畫取決於統計資料、索引和其他因素。 應該用來執行 Transact-SQL 查詢的最佳計畫可能會隨著時間而變更，視這些因素中的變更而定。 在某些情況下，新計畫可能比前一個計畫更好，而新的計畫可能會導致效能迴歸，例如參數敏感度或參數探查相關問題。
每當發現計畫選擇回歸發生時，您應該找到先前良好的計畫，並強制使用該計畫，而非目前的計畫。 使用 sp_query_store_force_plan 程序來完成這個動作。 SQL Server 2017 (14.x) 中的資料庫引擎提供迴歸計畫和建議修正動作的相關資訊。 此外，資料庫引擎可讓您完全自動化此程序，並讓資料庫引擎修正與計畫變更相關的任何問題。
重要
在擷取基準之後，自動計畫修正應該用於資料庫相容性層級升級的範圍，以自動降低工作負載升級風險。 如需本使用案例的詳細資訊，請參閱在升級到新版 SQL Server 期間保持效能穩定。
自動計劃選擇更正
只要偵測到計畫選擇迴歸，資料庫引擎就可以自動切換到最後一個已知的良好計畫。
資料庫引擎會自動偵測任何可能的計畫選擇迴歸，包括應該使用的計畫，而不是錯誤的計畫。 自動計畫修正強制使用產生的執行計畫將會與最後一個已知良好的計畫相同或類似。 由於產生的計畫可能與最後一個已知良好的計畫不相同，因此強制計畫的效能可能會有所不同。 在罕見的情況下，效能差異可能是顯著且負面的；在此情況下，自動計畫修正會自動停止嘗試強制取代計畫。
當資料庫引擎在迴歸發生前套用最後一個已知的計畫時，會自動監視強制計畫的效能。 如果強制計畫不比迴歸的計畫好，則不會強制執行新的計畫，且資料庫引擎會編譯新的計畫。 如果資料庫引擎確認強制計畫比迴歸計畫更好，則會保留強制計畫。 強制計畫會保留至重新編譯發生 (例如在下一個統計資料更新或結構描述變更) 為止。 如需計畫強制執行和可強制執行計畫類型的詳細資訊，請參閱計畫強制執行限制。
注意
如果在驗證計畫強制執行動作之前重新啟動 SQL Server 執行個體，則會自動取消強制執行該計畫。 否則，SQL Server 重新啟動時會保存計畫強制執行。
啟用自動計畫選擇修正
您可以依每個資料庫啟用自動調整，並指定只要偵測到某些計畫變更迴歸，就應該強制執行最後一個良好的計畫。 自動調整已使用下列命令啟用：
ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );
一旦您啟用此選項，資料庫引擎就會自動強制執行估計的 CPU 增量高於 10 秒，或新計畫錯誤數目高於建議計畫錯誤數目的任何建議，並確認強制執行的計畫比目前的計畫更好。
若要在 Azure SQL 資料庫 和 Azure SQL 受控執行個體 中啟用自動調整，請參閱＜使用 Azure 入口網站 在 Azure SQL 資料庫 中啟用自動調整＞ (機器翻譯)。
其他選項 - 手動計畫選擇更正
沒有自動調整，使用者必須定期監視系統，並尋找已迴歸的查詢。 如果有任何計畫已回歸，則使用者應該找到先前良好的計畫，並使用 sp_query_store_force_plan 程序來強制取代目前的計畫。 最佳做法是強制執行最後一個已知的良好計畫，因為較舊的計畫可能會因為統計資料或索引變更而無效。 強制執行最後一個已知良好計畫的使用者應該監視使用強制計畫執行的查詢效能，並確認強制計畫如預期般運作。 視監視和分析的結果而定，使用者應該強制執行計畫，或應該尋找另一種方式來最佳化查詢，例如重寫查詢。 手動強制計畫不應永遠強制執行，因為資料庫引擎應該能夠套用最佳計畫。 使用者或 DBA 最終應該使用 sp_query_store_unforce_plan 程序取消強制執行計畫，並讓資料庫引擎尋找最佳的計畫。
提示
或者，使用查詢搭配強制計畫查詢存放區檢視來尋找並取消強制執行計畫。
SQL Server 提供監視效能和修正查詢存放區中問題所需的所有必要檢視和程序。
在 SQL Server 2016 (13.x) 中，您可以使用查詢存放區系統檢視來尋找計畫選擇迴歸。 從 SQL Server 2017 (14.x) 開始，資料庫引擎會偵測並顯示潛在的計畫選擇迴歸，以及應該在 sys.dm_db_tuning_recommendations (Transact-SQL) DMV 中套用的建議動作。 DMV 會顯示問題的相關資訊、問題的重要性，以及詳細資料，例如識別的查詢、迴歸計畫的識別碼、用作比較基準的計畫識別碼，以及可執行以修正問題的 Transact-SQL 陳述式。
| 類型 | description | Datetime | score | 詳細資料 | ... |
|---|---|---|---|---|---|
FORCE_LAST_GOOD_PLAN |
CPU 時間從 4 毫秒變更為 14 毫秒 | 2017/3/17 | 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。 - 包含資料庫引擎為什麼認為此計畫變更可能是潛在效能迴歸相關資訊的描述。
- 偵測到潛在迴歸的日期時間。
- 此建議的分數。
- 有關偵測到計畫的識別碼、迴歸計畫的識別碼、應強制修正問題的計畫識別碼、可以套用以修正問題的 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 | current plan_id | recommended 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。 容易出錯的計畫是應強制執行建議計畫而非目前計畫的另一個原因。
雖然資料庫引擎提供識別計畫選擇迴歸所需的所有資訊，但持續監視和修正效能問題可能會成為繁瑣的程序。 自動調整可讓此程序更容易。
注意
在重新啟動資料庫引擎之後，系統不會保存 sys.dm_db_tuning_recommendations DMV 中的資料。 使用 sys.dm_os_sys_info 中的 sqlserver_start_time 資料行，來尋找最近一次資料庫引擎啟動時間。
自動索引管理
在 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 套用 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 入口網站中的尋找索引建議中找到。
下一步
- 在 Azure SQL Database 和 Azure SQL 受控執行個體中進行自動微調
- 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)
- sys.query_store_plan_forcing_locations (Transact-SQL)
- sp_query_store_unforce_plan (Transact-SQL)
- sys.database_query_store_options (Transact-SQL)
- sys.dm_os_sys_info (Transact-SQL)
- JSON 函式
- 執行計畫
- 效能的監視與微調
- 效能監視及微調工具
- 使用查詢存放區監視效能
- 查詢調整小幫手
意見反應
即將登場：在 2024 年，我們將逐步淘汰 GitHub 問題作為內容的意見反應機制，並將它取代為新的意見反應系統。 如需詳細資訊，請參閱：https://aka.ms/ContentUserFeedback。
提交並檢視相關的意見反應