sys.dm_db_tuning_recommendations (Transact-SQL)

文章
07/05/2024

適用於：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 時間）。建議詳細數據會在回歸偵測時進行，並描述為何資料庫引擎識別效能回歸。使用 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 函式詳細資訊，請參閱資料庫引擎中的 JSON 支援。

權限

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

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

SQL Server 2022 和更新版本的權限

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

分享方式：