sys.dm_db_tuning_recommendations (Transact-SQL)

S’applique à : SQL Server 2017 (14.x) et versions ultérieures Azure SQL Database Azure SQL Managed Instance

Retourne des informations détaillées sur les recommandations de réglage automatique. Pour plus d’informations, consultez Réglage automatique

Notes

Les informations retournées par sys.dm_db_tuning_recommendations sont mises à jour lorsque le moteur de base de données identifie la régression potentielle des performances des requêtes et n’est pas conservé. Les recommandations sont conservées uniquement tant que le moteur de base de données n’est pas redémarré. Utilisez la colonne sqlserver_start_time dans sys.dm_os_sys_info pour rechercher la dernière heure de démarrage du moteur de base de données. Les administrateurs de base de données doivent régulièrement effectuer des copies de sauvegarde de la recommandation de paramétrage s’ils souhaitent le conserver après le recyclage du serveur.

Le currentValue champ de la state colonne peut avoir les valeurs suivantes :

Le document JSON dans la state colonne contient la raison qui décrit pourquoi est la recommandation dans l’état actuel. Les valeurs du champ motif peuvent être les suivantes :

Les statistiques de la details colonne n’affichent pas les statistiques du plan d’exécution (par exemple, l’heure actuelle du processeur). Les détails de la recommandation sont pris au moment de la détection de régression et décrivent pourquoi Moteur de base de données régression des performances identifiée. Utilisez et recommendedPlanId interrogez regressedPlanId Magasin des requêtes vues de catalogue pour rechercher des statistiques exactes du plan d’exécution.

Exemples d’utilisation des informations sur les recommandations de réglage

Exemple 1

L’exemple de code suivant obtient le script Transact-SQL généré qui force un bon plan pour une requête donnée :

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

Exemple 2

Le code suivant obtient le script Transact-SQL généré qui force un bon plan pour une requête donnée et des informations supplémentaires sur le gain estimé :

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;

Exemple 3

Le code suivant obtient le script Transact-SQL généré qui force un bon plan pour une requête donnée et des informations supplémentaires qui incluent le texte de la requête et les plans de requête stockés dans Magasin des requêtes :

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;

Pour plus d’informations sur les fonctions JSON qui peuvent être utilisées pour interroger des valeurs dans la vue recommandation, consultez prise en charge json dans Moteur de base de données.

autorisations

Nécessite VIEW SERVER STATE une autorisation dans SQL Server.

Nécessite l’autorisation VIEW DATABASE STATE pour la base de données dans Azure SQL Database.

Autorisations pour SQL Server 2022 (et versions plus récentes)

Nécessite l'autorisation VIEW SERVER PERFORMANCE STATE sur le serveur.

Étapes suivantes

• Réglage automatique
• sys.database_automatic_tuning_options (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• Prise en charge JSON
• sys.dm_os_sys_info (Transact-SQL)