sys.dm_db_tuning_recommendations (Transact-SQL)

Article
06/06/2023

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 Paramétrage automatique

Pour plus d’informations, consultez Supervision et optimisation des performances dans Azure SQL base de données et Azure SQL Managed Instance.

Dans Azure SQL base de données, les vues de gestion dynamique ne peuvent pas exposer d’informations qui affecteraient le confinement de la base de données ou exposer d’informations sur d’autres bases de données auxquelles l’utilisateur a accès. Pour éviter d’exposer ces informations, chaque ligne qui contient des données qui n’appartiennent pas au locataire connecté est filtrée.

Nom de la colonne	Type de données	Description
name	nvarchar(4000)	Nom unique de la recommandation.
type	nvarchar(4000)	Nom de l’option de réglage automatique qui a produit la recommandation, par exemple, `FORCE_LAST_GOOD_PLAN`
reason	nvarchar(4000)	Raison pour laquelle cette recommandation a été fournie.
valid_since	datetime2	La première fois que cette recommandation a été générée.
last_refresh	datetime2	La dernière fois que cette recommandation a été générée.
state	nvarchar(4000)	Document JSON qui décrit l’état de la recommandation. Les champs suivants sont disponibles : - `currentValue` - état actuel de la recommandation. - `reason` - constante qui décrit la raison pour laquelle la recommandation est dans l’état actuel.
is_executable_action	bit	1 = La recommandation peut être exécutée sur la base de données via un script Transact-SQL. 0 = La recommandation ne peut pas être exécutée sur la base de données (par exemple , information uniquement ou recommandation rétablie)
is_revertable_action	bit	1 = La recommandation peut être automatiquement surveillée et annulée par le moteur de base de données. 0 = La recommandation ne peut pas être automatiquement surveillée et annulée. La plupart des actions exécutables peuvent être annulées.
execute_action_start_time	datetime2	Date d’application de la recommandation.
execute_action_duration	time	Durée de l’action d’exécution.
execute_action_initiated_by	nvarchar(4000)	`User` = Plan forcé de l’utilisateur manuellement dans la recommandation. `System` = Recommandation appliquée automatiquement par le système.
execute_action_initiated_time	datetime2	Date à laquelle la recommandation a été appliquée.
revert_action_start_time	datetime2	Date à laquelle la recommandation a été rétablie.
revert_action_duration	time	Durée de l’action de rétablissement.
revert_action_initiated_by	nvarchar(4000)	`User` = Plan recommandé non appliqué manuellement par l’utilisateur. `System` = Recommandation automatiquement rétablie par le système.
revert_action_initiated_time	datetime2	Date à laquelle la recommandation a été rétablie.
score	int	Valeur/effet estimé pour cette recommandation sur l’échelle 0-100 (plus le mieux c’est)
details	nvarchar(max)	Document JSON qui contient plus de détails sur la recommandation. Les champs suivants sont disponibles : `planForceDetails` - `queryId` - query_id de la requête régressée. - `regressedPlanId` - plan_id du plan régressé. - `regressedPlanExecutionCount` - Nombre d’exécutions de la requête avec un plan régressé avant la détection de la régression. - `regressedPlanAbortedCount` - Nombre d’erreurs détectées pendant l’exécution du plan régressé. - `regressedPlanCpuTimeAverage` - Temps processeur moyen (en micro secondes) consommé par la requête régressée avant la détection de la régression. - `regressedPlanCpuTimeStddev` - Écart type du temps processeur consommé par la requête régressée avant la détection de la régression. - `recommendedPlanId` - plan_id du plan à forcer. - `recommendedPlanExecutionCount`- Nombre d’exécutions de la requête avec le plan qui doit être forcé avant la détection de la régression. - `recommendedPlanAbortedCount` - Nombre d’erreurs détectées pendant l’exécution du plan qui doivent être forcées. - `recommendedPlanCpuTimeAverage` - Temps processeur moyen (en micro secondes) consommé par la requête exécutée avec le plan qui doit être forcé (calculé avant la détection de la régression). - `recommendedPlanCpuTimeStddev` Écart type du temps processeur consommé par la requête régressée avant la détection de la régression. `implementationDetails` - `method` - Méthode à utiliser pour corriger la régression. La valeur est toujours `TSql`. - `script` - Script Transact-SQL qui doit être exécuté pour forcer le plan recommandé.

Notes

Les informations retournées par sys.dm_db_tuning_recommendations sont mises à jour lorsque le moteur de base de données identifie une régression potentielle des performances des requêtes et ne sont pas conservées. Les recommandations sont conservées uniquement jusqu’à ce que le moteur de base de données soit 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 la conserver après le recyclage du serveur.

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

Statut	Description
`Active`	La recommandation est active et n’est pas encore appliquée. L’utilisateur peut prendre le script de recommandation et l’exécuter manuellement.
`Verifying`	La recommandation est appliquée par le moteur de base de données et le processus de vérification interne compare les performances du plan forcé avec le plan régressé.
`Success`	La recommandation est appliquée avec succès.
`Reverted`	La recommandation est rétablie, car il n’y a pas de gains de performances significatifs.
`Expired`	La recommandation a expiré et ne peut plus être appliquée.

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

Motif	Description
`SchemaChanged`	La recommandation a expiré, car le schéma d’une table référencée est modifié. Une nouvelle recommandation sera créée si une nouvelle régression de plan de requête est détectée sur le nouveau schéma.
`StatisticsChanged`	La recommandation a expiré en raison de la modification des statistiques sur une table référencée. Une nouvelle recommandation sera créée si une nouvelle régression de plan de requête est détectée en fonction de nouvelles statistiques.
`ForcingFailed`	Le plan recommandé ne peut pas être forcé sur une requête. Recherchez le `last_force_failure_reason` dans la vue sys.query_store_plan pour trouver la raison de l’échec.
`AutomaticTuningOptionDisabled`	`FORCE_LAST_GOOD_PLAN` l’option est désactivée par l’utilisateur pendant le processus de vérification. Activez l’option `FORCE_LAST_GOOD_PLAN` à l’aide de l’instruction ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) ou forcez manuellement le plan à l’aide du script dans la `details` colonne.
`UnsupportedStatementType`	Le plan ne peut pas être forcé sur la requête. Les curseurs et `INSERT BULK` les instructions sont des exemples de requêtes non prises en charge.
`LastGoodPlanForced`	La recommandation est appliquée avec succès.
`AutomaticTuningOptionNotEnabled`	Le moteur de base de données a identifié une régression des performances potentielle, mais l’option `FORCE_LAST_GOOD_PLAN` n’est pas activée. Consultez ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL). Appliquer manuellement la recommandation ou activer l’option `FORCE_LAST_GOOD_PLAN` .
`VerificationAborted`	Le processus de vérification est abandonné en raison du redémarrage ou du nettoyage Magasin des requêtes.
`VerificationForcedQueryRecompile`	La requête est recompilée, car il n’y a pas d’amélioration significative des performances.
`PlanForcedByUser`	L’utilisateur a forcé manuellement le plan à l’aide de la procédure sp_query_store_force_plan (Transact-SQL). Le moteur de base de données n’applique pas la recommandation si l’utilisateur a explicitement décidé de forcer un plan.
`PlanUnforcedByUser`	L’utilisateur a supprimé manuellement le plan à l’aide de la procédure sp_query_store_unforce_plan (Transact-SQL). Étant donné que l’utilisateur a explicitement rétabli le plan recommandé, le moteur de base de données continue d’utiliser le plan actuel et génère une nouvelle recommandation si une régression de plan se produit à l’avenir.
`UserForcedDifferentPlan`	L’utilisateur a forcé manuellement un plan différent à l’aide de la procédure sp_query_store_force_plan (Transact-SQL). Le moteur de base de données n’applique pas la recommandation si l’utilisateur a explicitement décidé de forcer un plan.
`TempTableChanged`	Une table temporaire utilisée dans le plan est modifiée.

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

Exemples d’utilisation des informations de recommandations de paramétrage

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

L’élément 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

L’élément 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 l’affichage des recommandations, consultez Prise en charge JSON dans le moteur de base de données.

Autorisations

Nécessite l’autorisation VIEW SERVER STATE 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.