sys.dm_db_tuning_recommendations (Transact-SQL)

Van toepassing op: SQL Server 2017 (14.x) en latere versies Van Azure SQL DatabaseAzure SQL Managed Instance

Retourneert gedetailleerde informatie over aanbevelingen voor automatisch afstemmen. Zie Automatisch afstemmen voor meer informatie

Opmerkingen

Informatie die wordt geretourneerd door sys.dm_db_tuning_recommendations , wordt bijgewerkt wanneer de database-engine potentiële regressie van queryprestaties identificeert en niet persistent is. Aanbevelingen worden alleen bewaard totdat de database-engine opnieuw wordt opgestart. Gebruik de kolom sqlserver_start_time in sys.dm_os_sys_info om de laatste opstarttijd van de database-engine te vinden. Databasebeheerders moeten regelmatig back-ups maken van de aanbeveling voor afstemming als ze deze willen bewaren na het recyclen van de server.

Het currentValue veld in de state kolom heeft mogelijk de volgende waarden:

JSON-document in state kolom bevat de reden waarom wordt beschreven waarom de aanbeveling de huidige status heeft. Waarden in het redenveld kunnen het volgende zijn:

Statistieken in de details kolom bevatten geen statistieken van runtimeplannen (bijvoorbeeld de huidige CPU-tijd). De details van de aanbeveling worden genomen op het moment van regressiedetectie en beschrijven waarom database-engine prestatieregressie heeft geïdentificeerd. Gebruik regressedPlanId en recommendedPlanId om query's uit te voeren op query store-catalogusweergaven om exacte statistieken van runtime-plannen te vinden.

Voorbeelden van het gebruik van informatie over het afstemmen van aanbevelingen

Voorbeeld 1

De volgende voorbeeldcode haalt het gegenereerde Transact-SQL script op dat een goed plan voor een bepaalde query dwingt:

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

Voorbeeld 2

Hieronder wordt het gegenereerde Transact-SQL script opgehaald dat een goed plan voor een bepaalde query afdingt en aanvullende informatie over de geschatte winst:

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;

Voorbeeld 3

Hieronder wordt het gegenereerde Transact-SQL script opgehaald waarmee een goed plan wordt afgevangen voor een bepaalde query en aanvullende informatie die de querytekst en de queryplannen bevat die zijn opgeslagen in 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;

Zie JSON-ondersteuning in Database Engine voor meer informatie over JSON-functies die kunnen worden gebruikt voor het opvragen van waarden in de aanbevelingsweergave.

Permissions

Hiervoor is een machtiging in SQL Server vereist VIEW SERVER STATE .

Vereist de VIEW DATABASE STATE machtiging voor de database in Azure SQL Database.

Machtigingen voor SQL Server 2022 en hoger

Hiervoor is machtiging vereist VIEW SERVER PERFORMANCE STATE op de server.

Volgende stappen

• Automatisch afstemmen
• sys.database_automatic_tuning_options (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• JSON-ondersteuning
• sys.dm_os_sys_info (Transact-SQL)