sys.dm_db_tuning_recommendations (Transact-SQL)
適用於: SQL Server 2017 (14.x) 和更新版本Azure SQL DatabaseAzure SQL 受控執行個體
傳回自動微調建議的詳細資訊。 如需詳細資訊,請參閱 自動調整
如需詳細資訊,請參閱 在 Azure SQL Database 和 Azure SQL 受控實例 中監視和效能微調。
在 Azure SQL Database 中,動態管理檢視無法公開會影響資料庫內含專案的資訊,或公開使用者可存取之其他資料庫的相關資訊。 為了避免公開此資訊,系統會篩選出包含不屬於連線租用戶之資料的每個資料列。
資料行名稱 | Data type | 說明 |
---|---|---|
name | nvarchar(4000) | 建議的唯一名稱。 |
type | nvarchar(4000) | 產生建議的自動調整選項名稱,例如 FORCE_LAST_GOOD_PLAN |
原因 | nvarchar(4000) | 提供這項建議的原因。 |
valid_since | datetime2 | 第一次產生此建議。 |
last_refresh | datetime2 | 上次產生此建議的時間。 |
state | nvarchar(4000) | 描述建議狀態的 JSON 檔。 有下欄欄位可供使用: - currentValue - 建議的目前狀態。- reason - 常數,描述建議處於目前狀態的原因。 |
is_executable_action | bit | 1 = 建議可以透過 Transact-SQL 腳本對資料庫執行。 0 = 無法對資料庫執行建議(例如:僅限資訊或已還原的建議) |
is_revertable_action | bit | 1 = 資料庫引擎可以自動監視和還原建議。 0 = 無法自動監視和還原建議。 大部分 可執行 的動作都可以 還原 。 |
execute_action_start_time | datetime2 | 套用建議的日期。 |
execute_action_duration | time | 執行動作的持續時間。 |
execute_action_initiated_by | nvarchar(4000) | User = 建議中的使用者手動強制方案。System = 系統自動套用建議。 |
execute_action_initiated_time | datetime2 | 套用建議的日期。 |
revert_action_start_time | datetime2 | 已還原建議的日期。 |
revert_action_duration | time | 還原動作的持續時間。 |
revert_action_initiated_by | nvarchar(4000) | User = 使用者手動取消強制建議方案。System = 系統自動還原的建議。 |
revert_action_initiated_time | datetime2 | 已還原建議的日期。 |
得分 | int | 此建議對 0-100 尺規的估計值/效果 (越大越好) |
細節 | nvarchar(max) | JSON 檔,其中包含更多有關建議的詳細資料。 有下欄欄位可供使用:planForceDetails - queryId - query_id回歸查詢。- regressedPlanId - 回歸計畫的plan_id。- regressedPlanExecutionCount - 偵測到回歸之前,具有回歸計畫的查詢執行次數。- regressedPlanAbortedCount - 執行回歸計畫期間偵測到的錯誤數目。- regressedPlanCpuTimeAverage - 偵測到回歸查詢之前所耗用的平均 CPU 時間(以微秒為單位)。- regressedPlanCpuTimeStddev - 偵測到回歸查詢之前所耗用 CPU 時間的標準差。- recommendedPlanId - 應強制plan_id計畫。- recommendedPlanExecutionCount - 偵測到回歸之前應該強制計畫的查詢執行次數。- recommendedPlanAbortedCount - 在執行應該強制的計畫期間偵測到的錯誤數目。- recommendedPlanCpuTimeAverage - 使用計畫執行的查詢所耗用的平均 CPU 時間(以微秒為單位),該計畫應強制執行(在偵測到回歸之前計算)。- recommendedPlanCpuTimeStddev 在偵測到回歸之前,回歸查詢所耗用 CPU 時間的標準差。implementationDetails - method - 應該用來更正回歸的方法。 值一律 TSql 為 。- script - 應執行的 Transact-SQL 腳本,以強制建議的計畫。 |
備註
當資料庫引擎識別潛在的查詢效能回歸,且不會保存時,所傳 sys.dm_db_tuning_recommendations
回的資訊會更新。 只有在資料庫引擎重新開機之前,才會保留建議。 使用 sys.dm_os_sys_info 中的 sqlserver_start_time
資料行,來尋找最近一次資料庫引擎啟動時間。 如果資料庫管理員想要在伺服器回收之後保留,資料庫管理員應該定期製作微調建議的備份複本。
資料 currentValue
行中的 state
欄位可能有下列值:
狀態 | 描述 |
---|---|
Active |
建議為使用中且尚未套用。 使用者可以接受建議腳本,並手動執行。 |
Verifying |
Database Engine 會套用建議,而內部驗證程式會比較強制計畫的效能與回歸計畫。 |
Success |
已成功套用建議。 |
Reverted |
因為沒有顯著的效能提升,因此會還原建議。 |
Expired |
建議已過期且無法再套用。 |
資料行中的 state
JSON 檔包含說明目前狀態的建議原因。 原因欄位中的值可能是:
原因 | 描述 |
---|---|
SchemaChanged |
建議已過期,因為參考資料表的架構已變更。 如果在新的架構上偵測到新的查詢計劃回歸,將會建立新的建議。 |
StatisticsChanged |
由於參考資料表上的統計資料變更,建議已過期。 如果根據新的統計資料偵測到新的查詢計劃回歸,將會建立新的建議。 |
ForcingFailed |
建議的計畫無法在查詢上強制使用。 last_force_failure_reason 在sys.query_store_plan 檢視中 尋找 ,以找出失敗的原因。 |
AutomaticTuningOptionDisabled |
FORCE_LAST_GOOD_PLAN 使用者會在驗證程式期間停用 選項。 使用 ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) 語句啟用 FORCE_LAST_GOOD_PLAN 選項,或使用資料行中的 details 腳本手動強制計畫。 |
UnsupportedStatementType |
無法在查詢上強制規劃。 不支援的查詢範例包括資料指標和 INSERT BULK 語句。 |
LastGoodPlanForced |
已成功套用建議。 |
AutomaticTuningOptionNotEnabled |
資料庫引擎識別出潛在的效能回歸,但 FORCE_LAST_GOOD_PLAN 未啟用此選項 - 請參閱 ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) 。 手動套用建議或啟用 FORCE_LAST_GOOD_PLAN 選項。 |
VerificationAborted |
由於重新開機或查詢存放區清除,驗證程式已中止。 |
VerificationForcedQueryRecompile |
查詢會重新編譯,因為沒有顯著的效能改善。 |
PlanForcedByUser |
使用者使用 sp_query_store_force_plan (Transact-SQL) 程式手動強制計畫。 如果使用者明確決定強制某些計畫,資料庫引擎將不會套用建議。 |
PlanUnforcedByUser |
使用者使用 sp_query_store_unforce_plan (Transact-SQL) 程式手動取消強制執行計畫。 由於使用者明確還原建議的計畫,資料庫引擎會繼續使用目前的計畫,並在未來發生某些計畫回歸時產生新的建議。 |
UserForcedDifferentPlan |
使用者使用 sp_query_store_force_plan (Transact-SQL) 程式手動強制不同的方案。 如果使用者明確決定強制某些計畫,資料庫引擎將不會套用建議。 |
TempTableChanged |
計畫中使用的臨時表已變更。 |
資料 details
行中的統計資料不會顯示執行時間計畫統計資料(例如目前的 CPU 時間)。 建議詳細資料會在回歸偵測時進行,並描述 Database Engine 識別效能回歸的原因。 使用 regressedPlanId
和 recommendedPlanId
來查詢 查詢查詢存放區目錄檢視 ,以尋找確切的執行時間計畫統計資料。
使用微調建議資訊的範例
範例 1
下列範例程式碼會取得產生的 Transact-SQL 腳本,以強制為任何指定的查詢提供良好的計畫:
SELECT name,
reason,
score,
JSON_VALUE(details, '$.implementationDetails.script') AS script,
details.*
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON(details, '$.planForceDetails') WITH (
[query_id] INT '$.queryId',
regressed_plan_id INT '$.regressedPlanId',
last_good_plan_id INT '$.recommendedPlanId'
) AS details
WHERE JSON_VALUE(STATE, '$.currentValue') = 'Active';
範例 2
下列會取得產生的 Transact-SQL 腳本,該腳本會強制針對任何指定的查詢強制執行良好的計畫,以及估計收益的其他資訊:
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;
範例 3
下列會取得產生的 Transact-SQL 腳本,以強制針對任何指定的查詢強制執行良好的計畫,以及包含查詢文字和查詢存放區中儲存的查詢計劃的其他資訊:
WITH cte_db_tuning_recommendations
AS (
SELECT reason,
score,
query_id,
regressedPlanId,
recommendedPlanId,
current_state = JSON_VALUE(STATE, '$.currentValue'),
current_state_reason = JSON_VALUE(STATE, '$.reason'),
script = JSON_VALUE(details, '$.implementationDetails.script'),
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
)
)
SELECT qsq.query_id,
qsqt.query_sql_text,
dtr.*,
CAST(rp.query_plan AS XML) AS RegressedPlan,
CAST(sp.query_plan AS XML) AS SuggestedPlan
FROM cte_db_tuning_recommendations AS dtr
INNER JOIN sys.query_store_plan AS rp
ON rp.query_id = dtr.query_id
AND rp.plan_id = dtr.regressedPlanId
INNER JOIN sys.query_store_plan AS sp
ON sp.query_id = dtr.query_id
AND sp.plan_id = dtr.recommendedPlanId
INNER JOIN sys.query_store_query AS qsq
ON qsq.query_id = rp.query_id
INNER JOIN sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id;
如需可用來在建議檢視中查詢值的 JSON 函式詳細資訊,請參閱 Database Engine 中的 JSON 支援 。
權限
VIEW SERVER STATE
需要 SQL Server 中的許可權。
VIEW DATABASE STATE
需要 Azure SQL Database 中資料庫的許可權。
SQL Server 2022 和更新版本的權限
需要伺服器的 VIEW SERVER PERFORMANCE STATE
權限。
下一步
意見反應
https://aka.ms/ContentUserFeedback。
即將登場:在 2024 年,我們將逐步淘汰 GitHub 問題作為內容的意見反應機制,並將它取代為新的意見反應系統。 如需詳細資訊,請參閱:提交並檢視相關的意見反應