sys.dm_db_tuning_recommendations (Transact-SQL)
S’applique à : SQL Server 2017 (14.x) et versions ultérieures Azure SQL DatabaseAzure 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.
Étapes suivantes
Commentaires
https://aka.ms/ContentUserFeedback.
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultezEnvoyer et afficher des commentaires pour