sys.dm_db_tuning_recommendations (Transact-SQL)

Article
07/05/2024

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

Pour plus d’informations, consultez Supervision et réglage des performances dans Azure SQL Database et Azure SQL Managed Instance.

Dans Azure SQL Database, les vues de gestion dynamique ne peuvent pas exposer d’informations qui affecteraient l’autonomie de la base de données ou exposeraient des 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
nom	nvarchar(4000)	Nom unique de recommandation.
type	nvarchar(4000)	Nom de l’option de réglage automatique qui a produit la recommandation, par exemple, `FORCE_LAST_GOOD_PLAN`
raison	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 pourquoi 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 : informations uniquement ou recommandation rétablie)
is_revertable_action	bit	1 = La recommandation peut être automatiquement surveillée et rétablie par le moteur de base de données. 0 = La recommandation ne peut pas être automatiquement surveillée et rétablie. La plupart des actions exécutables seront rétablies.
execute_action_start_time	datetime2	Date à laquelle la recommandation est appliquée.
execute_action_duration	time	Durée de l’action d’exécution.
execute_action_initiated_by	nvarchar(4000)	`User` = Plan forcé manuellement par l’utilisateur 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 restauration.
revert_action_initiated_by	nvarchar(4000)	`User` = Plan recommandé non appliqué manuellement par l’utilisateur. `System` = Recommandation rétablie automatiquement 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 de 0 à 100 (plus la plus grande est la meilleure)
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 qui devrait être forcé. - `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 doit être forcé. - `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 qui doit être utilisée 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 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 :

Statut	Description
`Active`	La recommandation est active et n’est pas encore appliquée. L’utilisateur peut prendre un script de recommandation et l’exécuter manuellement.
`Verifying`	La recommandation est appliquée par 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 correctement appliquée.
`Reverted`	La recommandation est rétablie, car il n’y a pas de gains significatifs en matière de performances.
`Expired`	La recommandation a expiré et ne peut plus être appliquée.

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 :

Motif	Description
`SchemaChanged`	La recommandation a expiré car le schéma d’une table référencée est modifié. Une nouvelle recommandation est 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 statistique d’une table référencée. Une nouvelle recommandation est 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 l’affichage `last_force_failure_reason` 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 le plan manuellement à l’aide du script dans la `details` colonne.
`UnsupportedStatementType`	Le plan ne peut pas être forcé sur la requête. Des exemples de requêtes non prises en charge sont des curseurs et `INSERT BULK` des instructions.
`LastGoodPlanForced`	La recommandation est correctement appliquée.
`AutomaticTuningOptionNotEnabled`	Moteur de base de données a identifié une régression potentielle des performances, mais l’option `FORCE_LAST_GOOD_PLAN` n’est pas activée : consultez ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL). Appliquez manuellement une recommandation ou activez 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 décidé explicitement de forcer un plan.
`PlanUnforcedByUser`	L’utilisateur n’a pas appliqué 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 à utiliser le plan actuel et génère une nouvelle recommandation si une régression de plan se produit ultérieurement.
`UserForcedDifferentPlan`	L’utilisateur a forcé manuellement un autre 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 décidé explicitement 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 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.

Partager via