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 權限。

下一步

• 自動微調
• sys.database_automatic_tuning_options (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• JSON 支援
• sys.dm_os_sys_info (Transact-SQL)