共用方式為


sys.dm_db_tuning_recommendations (Transact-SQL)

適用於:SQL Server 2017 (14.x) 和更新版本的 Azure SQL 資料庫 Azure SQL 受控執行個體

傳回自動微調建議的詳細資訊。 如需詳細資訊,請參閱 自動調整

如需詳細資訊,請參閱 Azure SQL 資料庫 和 Azure SQL 受控執行個體 中的監視和效能微調。

在 Azure SQL 資料庫 中,動態管理檢視無法公開會影響資料庫內含專案的資訊,或公開使用者可存取之其他資料庫的相關信息。 為了避免公開此資訊,系統會篩選出包含不屬於連線租用戶之資料的每個資料列。

數據行名稱 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 資料庫引擎 套用建議,而內部驗證程式會比較強制計劃的效能與回歸計劃。
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 時間)。 建議詳細數據會在回歸偵測時進行,並描述為何 資料庫引擎 識別效能回歸。 使用 regressedPlanIdrecommendedPlanId 來查詢 查詢存放區 目錄檢視,以尋找確切的運行時間計劃統計數據。

使用微調建議資訊的範例

範例 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 函式詳細資訊,請參閱 資料庫引擎 中的 JSON 支援

權限

VIEW SERVER STATE需要 SQL Server 中的許可權。

VIEW DATABASE STATE需要 Azure SQL 資料庫 中資料庫的許可權。

SQL Server 2022 和更新版本的權限

需要伺服器的 VIEW SERVER PERFORMANCE STATE 權限。

下一步