適用於：
SQL Server 2017 （14.x） 及以後版本 Azure SQL Database AzureSQL Managed InstanceMicrosoft Fabric 中的 SQL 資料庫
自動調整是一項資料庫功能，可讓您深入瞭解潛在的查詢效能問題、建議解決方案，以及自動修正已識別的問題。
SQL Server 2017 （14.x） 中引進的自動調整會在偵測到潛在效能問題時通知您，並可讓您套用更正動作，或讓 Database Engine 自動修正效能問題。 自動調整 SQL Server 可識別並修正 查詢執行計劃選擇回歸所造成的效能問題。 Azure SQL 資料庫中的自動調校，以及 Microsoft Fabric 中的 SQL 資料庫，也能建立必要的索引並刪除未使用的索引。 如需查詢執行計劃的詳細資訊，請參閱 執行計劃。
SQL Server 資料庫引擎會監視在資料庫上執行的查詢，並自動改善工作負載的效能。 Database Engine 具有內建的智慧機制，可藉由根據工作負載動態調整資料庫，以自動調整和改善查詢的效能。 有兩個可用的自動調整功能：
自動計劃修正 可識別有問題的查詢執行計劃，例如 參數敏感度或參數探查 問題，並藉由在回歸發生之前強制最後一個已知的良好計劃來修正查詢執行計劃相關的效能問題。 適用於：SQL Server（自 SQL Server 2017 （14.x）起）、Azure SQL 資料庫，以及 Microsoft Fabric 中的 SQL 資料庫，以及 Azure SQL 管理實例
自動索引管理 會識別應該加入資料庫中的索引，以及應該移除的索引。 適用於：Azure SQL 資料庫及 Microsoft Fabric 中的 SQL 資料庫
Note
本文中，Azure SQL 資料庫的特性與行為也適用於 Microsoft Fabric 中的 SQL 資料庫。
為何要自動調整？
傳統資料庫管理中的三個主要工作是監視工作負載、識別重要的 Transact-SQL 查詢，以及識別應新增以改善效能的索引，或很少使用且可移除以改善效能的索引。 SQL Server Database Engine 提供您需要監視之查詢和索引的詳細深入解析。 不過，持續監視資料庫是一項艱巨而乏味的工作，尤其是在處理許多資料庫時。 管理大量資料庫可能無法有效率地執行。 您可以考慮使用自動微調功能，將某些監視和微調動作委派給 Database Engine，而不是手動監視和調整資料庫。
自動調校是如何運作的？
自動調整是持續監視和分析程式，會持續瞭解工作負載的特性，並識別潛在的問題和改進。
此程式可讓資料庫透過尋找哪些索引和計劃來動態調整工作負載，進而改善工作負載的效能，以及哪些索引會影響您的工作負載。 根據這些調查結果，自動調整會套用可改善工作負載效能的調整動作。 此外，自動調整會在實作任何變更之後持續監視資料庫的效能，以確保其能改善工作負載的效能。 未能改善效能的任何動作都會自動還原。 此驗證程式是確保自動調整所做的任何變更不會降低工作負載的整體效能的重要功能。
自動計劃修正
自動計劃修正是一項自動調整功能，可識別 執行計劃選擇回歸 ，並強制最後一個已知的良好計劃來自動修正問題。 如需查詢執行計劃和查詢優化器的詳細資訊，請參閱 查詢處理架構指南。
Important
自動計劃修正取決於資料庫中啟用的查詢存放區，以進行工作負載追蹤。
什麼是執行計劃選擇回歸？
SQL Server Database Engine 可能會使用不同的執行計劃來執行 Transact-SQL 查詢。 查詢計劃取決於統計數據、索引和其他因素。 應該用來執行 Transact-SQL 查詢的最佳計劃可能會隨著時間而變更，視這些因素的變更而定。 在某些情況下，新計劃可能不比上一個計劃好，而新的計劃可能會導致效能退化，例如 參數敏感度或參數探查 相關問題。
每當您注意到計劃選擇回歸發生時，您應該找到先前的良好計劃，並強制使用它，而不是目前的計劃。 這可以使用 sp_query_store_force_plan 程序來完成。 SQL Server 2017 中的資料庫引擎 （14.x） 提供回歸計劃的相關信息和建議的更正動作。 此外，Database Engine 可讓您完全自動化此程式，並讓 Database Engine 修正與計劃變更相關的任何問題。
Important
應在資料庫相容性層級升級的範圍內使用自動計劃修正，在擷取基準之後，以自動方式降低工作負載升級風險。 如需此使用案例的詳細資訊，請參閱 在升級至較新的SQL Server 期間保持效能穩定性。
自動計畫選擇修正
每當偵測到計劃選擇回歸時，Database Engine 可以自動切換至最後一個已知的良好計劃。
Database Engine 會自動偵測任何潛在的計劃選擇回歸，包括應該使用的計劃，而不是錯誤的計劃。 由自動計劃更正強制產生的執行計劃，將會與最後一個已知的良好計劃相同或類似。 因為產生的計劃可能與最後一個已知良好的計劃不相同，因此強制計劃的效能可能會有所不同。 在罕見的情況下，效能差異可能是顯著且負面的;在此情況下，自動計劃修正將會自動停止嘗試強制取代計劃。
當 Database Engine 在回歸發生之前套用最後一個已知的良好計劃時，它會自動監視強制計劃的效能。 如果強制計劃不優於回歸計劃，新計劃將會取消強制，且 Database Engine 會編譯新的計劃。 如果 Database Engine 確認強制計劃優於回歸計劃，則會保留強制計劃。 它會保留到重新編譯發生之前（例如，在下一個統計數據更新或架構變更時）。 如需計劃強制和可強制之計劃類型的詳細資訊，請參閱 計劃強制限制。
Note
如果在驗證計劃強制動作之前重新啟動 SQL Server 實例，該計劃會自動取消強制。 否則，在 SQL Server 重新啟動時，計畫強制會被持續保存。
啟用自動計劃選擇更正
您可以為每個資料庫啟用自動調整，並指定每次偵測到某些計劃變更回歸時，都應該強制執行最後一個良好的計劃。 使用下列命令啟用自動調整：
ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );
啟用此選項之後，Database Engine 會自動強制任何預估 CPU 增益高於 10 秒的建議，或新方案中的錯誤數目高於建議方案中的錯誤數目，並確認強制計劃比目前的計劃好。
若要 在 Azure SQL Database 和 Azure SQL 受控實例中啟用自動調整，請參閱 使用 Azure 入口網站在 Azure SQL Database 中啟用自動調整。
替代方案 - 手動修正計劃選擇
如果沒有自動調整，用戶必須定期監視系統，並尋找已回歸的查詢。 如果有任何計劃回歸，用戶應該找到良好的先前計劃，透過使用sp_query_store_force_plan 程序來強制使用它，取代目前的計劃。 最佳做法是強制最後一個已知的良好計劃，因為較舊的計劃可能會因為統計數據或索引變更而無效。 強制最後一個已知良好計劃的使用者應該監視使用強制計劃執行的查詢效能，並確認強制計劃如預期般運作。 根據監視和分析的結果，應該強制計劃，或用戶應該找到另一種方式來優化查詢，例如重寫查詢。 手動強制的計劃不應該永遠維持，因為資料庫引擎應該能夠套用最佳計劃。 使用者或 DBA 最終應該使用 sp_query_store_unforce_plan 程式取消強制執行計劃，並讓 Database Engine 找到最佳的計劃。
Tip
或者，使用 強制計劃的查詢 查詢存放庫檢視來定位並取消強制計劃。
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 | 日期時間 | 分數 | details | ... |
|---|---|---|---|---|---|
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的類型。 - 包含資訊的描述，說明為什麼資料庫引擎認為此計劃變更可能導致效能退化。
- 潛在回歸現象被偵測到的日期時間。
- 此建議的分數。
- 所偵測計劃標識碼、回歸計劃的標識碼、應強制修正問題的計劃標識碼、可能套用以修正問題的腳本 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 | 分數 | 指令 | query_id | 現行plan_id | 推薦方案ID | 預計增益 | 易出錯 |
|---|---|---|---|---|---|---|---|
| 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 提供識別計劃選擇回歸問題所需的所有資訊，但持續監視和修正效能問題可能會變成繁瑣的過程。 自動調整使這個過程更容易。
Note
在資料庫引擎重新啟動之後，DMV 中的數據 sys.dm_db_tuning_recommendations 不會保存。 使用 sqlserver_start_time 中的 資料行，來尋找最近一次資料庫引擎啟動時間。
自動索引管理
在 Azure SQL Database 中，索引管理很容易，因為 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 函式
- 執行計劃
- 效能的監視與微調
- 效能監視及微調工具
- 使用查詢存放區監視效能
- 查詢調整小幫手