sys.dm_db_tuning_recommendations (Transact-SQL)

Gäller för: SQL Server 2017 (14.x) och senare versioner Av Azure SQL DatabaseAzure SQL Managed Instance

Returnerar detaljerad information om rekommendationer för automatisk justering. Mer information finns i Automatisk justering

Anmärkningar

Information som returneras av sys.dm_db_tuning_recommendations uppdateras när databasmotorn identifierar potentiell regression av frågeprestanda och inte sparas. Rekommendationer behålls endast tills databasmotorn startas om. Använd kolumnen sqlserver_start_time i sys.dm_os_sys_info för att hitta den senaste starttiden för databasmotorn. Databasadministratörer bör regelbundet göra säkerhetskopior av justeringsrekommenderingen om de vill behålla den efter serveråtervinning.

Fältet currentValue i state kolumnen kan ha följande värden:

JSON-dokumentet i state kolumnen innehåller orsaken till varför är rekommendationen i det aktuella tillståndet. Värden i orsaksfältet kan vara:

Statistik i details kolumnen visar inte körningsplanstatistik (till exempel aktuell CPU-tid). Rekommendationsinformationen tas vid tidpunkten för regressionsidentifieringen och beskriver varför Databasmotorn identifierade prestandaregression. Använd regressedPlanId och recommendedPlanId för att fråga frågearkivkatalogvyer för att hitta exakt körningsplanstatistik.

Exempel på hur du använder information om justeringsrekommendationer

Exempel 1

Följande exempelkod hämtar det genererade Transact-SQL skriptet som tvingar fram en bra plan för en viss fråga:

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';

Exempel 2

Följande hämtar det genererade Transact-SQL skriptet som tvingar fram en bra plan för en viss fråga och ytterligare information om den uppskattade vinsten:

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;

Exempel 3

Följande hämtar det genererade Transact-SQL skriptet som tvingar fram en bra plan för en viss fråga och ytterligare information som innehåller frågetexten och frågeplaner som lagras i Query Store:

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;

Mer information om JSON-funktioner som kan användas för att fråga efter värden i rekommendationsvyn finns i JSON-stöd i databasmotorn.

Permissions

Kräver VIEW SERVER STATE behörighet i SQL Server.

Kräver behörighet för VIEW DATABASE STATE databasen i Azure SQL Database.

Behörigheter för SQL Server 2022 och senare

Kräver VIEW SERVER PERFORMANCE STATE behörighet på servern.

Nästa steg

• Automatisk justering
• sys.database_automatic_tuning_options (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• JSON-support
• sys.dm_os_sys_info (Transact-SQL)