Meilleures pratiques pour la supervision des charges de travail avec le Magasin des requêtes

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

Cet article décrit les bonnes pratiques concernant l’utilisation du Magasin des requêtes SQL Server avec votre charge de travail.

• Pour plus d’informations sur la configuration et l’administration avec le Magasin des requêtes, consultez Supervision du niveau de performance avec le Magasin des requêtes.
• Pour plus d’informations sur la découverte d’informations actionnables et le réglage des performances avec le Magasin des requêtes, consultez Réglage des performances avec le Magasin des requêtes.
• Pour plus d’informations sur l’utilisation du magasin de requêtes dans Azure SQL Database, consultez Utilisation du magasin de requêtes dans Azure SQL Database.
• Dans Azure Synapse Analytics, le Magasin des requêtes n’est pas activé par défaut pour les pools SQL dédiés. Toutefois, il peut être activé. D’autres options de configuration du Magasin des requêtes ne sont pas prises en charge. Pour plus d’informations, consultez Analyse et stockage des requêtes historiques dans Azure Synapse Analytics.

Utiliser la dernière version de SQL Server Management Studio

SQL Server Management Studio propose un ensemble d’interfaces utilisateur conçu pour configurer le Magasin des requêtes et consommer les données collectées relatives à votre charge de travail. Téléchargez la dernière version de SQL Server Management Studio.

Pour obtenir une description rapide de la manière d’utiliser le Magasin des requêtes dans des scénarios de résolution de problèmes, consultez Query Store Azure blogs.

Utiliser Query Performance Insight dans Base de données SQL Azure

Si vous exécutez le Magasin des requêtes dans Azure SQL Database, vous pouvez utiliser Query Performance Insight pour analyser la consommation de ressources dans le temps. Même si vous pouvez utiliser Management Studio et Azure Data Studio pour obtenir des détails sur la consommation en ressources de toutes vos requêtes (par exemple, processeur, mémoire et E/S), Query Performance Insight vous offre un moyen rapide et efficace de déterminer leur impact sur la consommation DTU globale de votre base de données. Pour plus d’informations, consultez Query Performance Insight pour base de données SQL Azure.

Utiliser le Magasin des requêtes avec des bases de données de pool élastique

Vous pouvez utiliser le Magasin des requêtes dans toutes les bases de données sans le moindre problème, même dans les pools élastiques Azure SQL Database très denses. Tous les problèmes précédents liés à l’utilisation excessive de ressources qui peuvent s’être produits lors de l’activation du Magasin des requêtes pour le grand nombre de bases de données des pools élastiques ont été résolus.

Démarrer la résolution des problèmes de performances des requêtes

La résolution des problèmes liés au Magasin des requêtes est un flux de travail simple, comme l’illustre le diagramme suivant :

Activez le Magasin des requêtes à l’aide de Management Studio, comme cela est décrit dans la section précédente ou exécutez l’instruction Transact-SQL suivante :

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;

Le Magasin des requêtes a besoin d’un certain temps avant de collecter le jeu de données qui représente avec précision votre charge de travail. En générale, un jour suffit, même pour les charges de travail très complexes. Néanmoins, vous pouvez commencer à explorer les données et à identifier les requêtes qui demandent votre attention immédiate après avoir activé la fonctionnalité. Accédez au sous-dossier Magasin des requêtes sous le nœud de base de données dans l’Explorateur d’objets de Management Studio pour ouvrir les vues de résolution de problèmes de scénarios spécifiques.

Les vues du Magasin des requêtes de Management Studio fonctionnent avec l’ensemble de métriques d’exécution, chacune exprimée comme étant l’une des fonctions statistiques suivantes :

Le graphique suivant montre comment trouver les vues du magasin de requêtes :

Le tableau suivant explique quand utiliser chaque vue du magasin de requêtes :

Quand vous identifiez une requête dont les performances ne sont pas optimales, votre action dépend de la nature du problème.

• Si la requête a été exécutée avec plusieurs plans et que le dernier est nettement plus mauvais que le précédent, vous pouvez utiliser le mécanisme permettant de forcer le plan. SQL Server tente de forcer le plan de l’optimiseur. Si le forçage de plan échoue, un XEvent est déclenché et l’optimiseur est tenu d’optimiser de façon normale.

• Vous pouvez en déduire qu’il manque un index à votre requête pour qu’elle s’exécute de façon optimale. Ces informations apparaissent dans le plan d’exécution de requête. Créez l’index manquant et vérifiez les performances de requête en utilisant le Magasin des requêtes.

Si vous exécutez votre charge de travail sur SQL Database, inscrivez-vous à SQL Database Index Advisor pour recevoir automatiquement des recommandations d’index.

• Dans certains cas, vous pouvez imposer une recompilation des statistiques si vous constatez une grande différence entre le nombre estimé et le nombre réel de lignes dans le plan d’exécution.
• Réécrivez les requêtes problématiques, par exemple, pour profiter du paramétrage des requêtes ou pour implémenter une logique plus optimale.

Vérifier que le Magasin des requêtes collecte les données des requêtes en continu

Le Magasin des requêtes peut modifier discrètement le mode d’opération. Surveillez régulièrement l’état du Magasin des requêtes pour vérifier qu’il fonctionne et pour prendre des mesures afin d’éviter des défaillances dont les causes étaient évitables. Exécutez la requête suivante pour déterminer le mode d’opération et afficher les paramètres les plus pertinents :

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

La différence entre actual_state_desc et desired_state_desc indique que le mode d’opération a changé automatiquement. Le changement le plus courant correspond au basculement discret du Magasin des requêtes en mode lecture seule. Dans certaines circonstances très rares, le Magasin des requêtes peut finir à l’état ERREUR en raison d’erreurs internes.

Quand l’état effectif est Lecture seule, consultez la colonne readonly_reason pour déterminer la cause racine. En règle générale, vous déterminez que le Magasin des requêtes est passé en mode lecture seule quand le quota de taille a été dépassé. Dans ce cas, readonly_reason est défini sur 65536. Pour d’autres raisons, consultez sys.database_query_store_options (Transact-SQL).

Pour faire basculer le magasin de requêtes en mode lecture-écriture et activer la collecte de données, envisagez les mesures suivantes :

• Augmentez la taille maximale de stockage en utilisant l’option MAX_STORAGE_SIZE_MB de la commande ALTER DATABASE.

• Nettoyez les données du magasin de requêtes à l’aide de l’instruction suivante :

  ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR;
  

Vous pouvez appliquer une ou deux de ces mesures en exécutant l’instruction suivante qui remet explicitement le mode d’opération en lecture-écriture :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

À titre préventif, prenez les mesures suivantes :

• Vous pouvez prévenir les changements discrets du mode d’opération en appliquant les bonnes pratiques. Vérifiez que la taille du Magasin des requêtes est toujours inférieure à la valeur maximale autorisée pour réduire considérablement les chances de passer en mode lecture seule. Activez la stratégie basée sur la taille comme indiqué dans la section Configurer le Magasin des requêtes pour que le Magasin des requêtes nettoie automatiquement les données quand sa taille approche de la limite.
• Pour faire en sorte que les données les plus récentes soient conservées, configurez une stratégie basée sur la durée pour supprimer régulièrement les informations obsolètes.
• Enfin, envisagez de définir l’option Mode de capture du Magasin des requêtes sur Auto, car cela permet d’éliminer les requêtes moins pertinentes pour votre charge de travail.

État ERREUR

Pour récupérer le Magasin des requêtes, essayez de définir explicitement le mode lecture-écriture et revérifiez l’état réel.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Si le problème persiste, cela indique que l’endommagement des données du Magasin des requêtes est rendu persistant sur le disque.

À partir de SQL Server 2017 (14.x), le Magasin des requêtes peut être récupéré via l’exécution de la procédure stockée sys.sp_query_store_consistency_check dans la base de données affectée. Il faut désactiver le Magasin des requêtes avant de tenter l’opération de récupération. Voici un exemple de requête à utiliser ou modifier pour effectuer la vérification de cohérence et la récupération de QDS :

IF EXISTS (SELECT * FROM sys.database_query_store_options WHERE actual_state=3) 
BEGIN
  BEGIN TRY
    ALTER DATABASE [QDS] SET QUERY_STORE = OFF
    Exec [QDS].dbo.sp_query_store_consistency_check
    ALTER DATABASE [QDS] SET QUERY_STORE = ON
    ALTER DATABASE [QDS] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
  END TRY
 
  BEGIN CATCH 
    SELECT  
      ERROR_NUMBER() AS ErrorNumber  
      ,ERROR_SEVERITY() AS ErrorSeverity  
      ,ERROR_STATE() AS ErrorState  
      ,ERROR_PROCEDURE() AS ErrorProcedure  
      ,ERROR_LINE() AS ErrorLine  
      ,ERROR_MESSAGE() AS ErrorMessage; 
  END CATCH;   
END

Pour SQL Server 2016 (13.x), vous devez effacer les données du Magasin des requêtes comme cela est indiqué.

Si la récupération a échoué, vous pouvez essayer d’effacer le contenu du Magasin des requêtes avant de définir le mode lecture-écriture.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE CLEAR;
GO

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Éviter l’utilisation de requêtes non paramétrées

Il n’est pas recommandé d’utiliser des requêtes non paramétrées quand cela n’est pas nécessaire. Le cas d’une analyse ad hoc en est un exemple. Les plans mis en cache ne peuvent pas être réutilisés, ce qui force l’optimiseur de requête à compiler les requêtes pour chaque texte de requête unique. Pour plus d’informations, consultez Principes d’utilisation du paramétrage forcé.

De plus, le Magasin des requêtes peut rapidement dépasser le quota de taille en raison du nombre potentiellement élevé de textes de requête différents et ainsi du nombre élevé de plans d’exécution différents de forme similaire. Par conséquent, votre charge de travail ne fonctionne pas de façon optimale et le Magasin des requêtes risque de basculer en mode lecture seule ou de supprimer constamment des données pour essayer de suivre le rythme des requêtes entrantes.

Considérez les options suivantes :

• Paramétrez les requêtes lorsque cela est possible. Par exemple, wrappez les requêtes dans une procédure stockée ou dans sp_executesql. Pour plus d’informations, consultez Réutilisation des paramètres et des plans d’exécution.
• Utilisez l’option Optimiser pour les charges de travail ad hoc si votre charge de travail contient de nombreux lots ad hoc à usage unique avec des plans de requête différents.
  • Comparez le nombre de valeurs query_hash distinctes au nombre total d’entrées dans sys.query_store_query. Si le rapport est proche de 1, votre charge de travail ad hoc génère des requêtes différentes.
• Appliquez un paramétrage forcé pour la base de données ou un sous-ensemble de requêtes si le nombre de plans de requête différents est modéré.
  • Utilisez un repère de plan pour forcer le paramétrage uniquement pour la requête sélectionnée.
  • Configurez le paramétrage forcé en utilisant la commande parameterization database option si votre charge de travail ne contient qu’un petit nombre de plans de requête différents. C’est le cas, par exemple, lorsque le rapport entre le nombre de valeurs query_hash distinctes et le nombre total d’entrées dans sys.query_store_query est nettement inférieur à 1.
• Définissez QUERY_CAPTURE_MODE sur AUTO pour éliminer automatiquement les requêtes ad hoc qui consomment peu de ressources.

Rechercher les requêtes non paramétrables dans le Magasin des requêtes

Vous pouvez trouver le nombre de plans stockés dans le Magasin des requêtes en utilisant la requête ci-dessous, des DMV du Magasin de requêtes, dans SQL Server, Azure SQL Managed Instance ou Azure SQL Database :

SELECT count(Pl.plan_id) AS plan_count, Qry.query_hash, Txt.query_text_id, Txt.query_sql_text
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id
GROUP BY Qry.query_hash, Txt.query_text_id, Txt.query_sql_text
ORDER BY plan_count desc;

L’exemple suivant crée une session Événements étendus pour capturer l’événement query_store_db_diagnostics, ce qui peut être utile pour diagnostiquer la consommation des ressources de requête. Dans SQL Server, cette session d’événements étendus crée un fichier d’événements dans le dossier journal SQL Server par défaut. Par exemple, dans une installation SQL Server 2019 (15.x) par défaut sur Windows, le fichier d’événements (fichier .xel) doit être créé dans le dossier C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log. Pour Azure SQL Managed Instance, spécifiez plutôt un emplacement Stockage Blob Azure. Pour plus d’informations, consultez Fichier d’événements XEvent pour Azure SQL Managed Instance. L’événement « qds.query_store_db_diagnostics » n’est pas disponible pour Azure SQL Database.

CREATE EVENT SESSION [QueryStore_Troubleshoot] ON SERVER 
ADD EVENT qds.query_store_db_diagnostics(
      ACTION(sqlos.system_thread_id,sqlos.task_address,sqlos.task_time,sqlserver.database_id,sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N'QueryStore',max_file_size=(100))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);

Avec ces données, vous pouvez également trouver le nombre de plans dans le Magasin des requêtes ainsi que de nombreuses autres statistiques. Recherchez les colonnes plan_count, query_count, max_stmt_hash_map_size_kb et max_size_mb dans les données d’événement pour comprendre la quantité de mémoire utilisée et le nombre de plans suivis par le Magasin des requêtes. Si le nombre de plans est supérieur à la normale, cela peut indiquer une augmentation des requêtes non paramétrables. Utilisez la requête DMV du Magasin des requêtes ci-dessous pour passer en revue les requêtes paramétrables et les requêtes non paramétrables dans le Magasin des requêtes.

Pour les requêtes paramétrables :

SELECT qsq.query_id, qsqt.query_sql_text
FROM sys.query_store_query AS qsq 
INNER JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id= qsqt.query_text_id 
WHERE qsq.query_parameterization_type<>0 or qsqt.query_sql_text like '%@%';

Pour les requêtes non paramétrables :

SELECT qsq.query_id, qsqt.query_sql_text
FROM sys.query_store_query AS qsq 
INNER JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id= qsqt.query_text_id 
WHERE query_parameterization_type=0;

Éviter un modèle DROP et CREATE pour les objets conteneurs

Le Magasin des requêtes associe une entrée de requête à un objet conteneur, tel qu’une procédure stockée, une fonction ou un déclencheur. Quand vous recréez un objet conteneur, une nouvelle entrée de requête est générée pour le même texte de requête. Cela vous empêche de suivre les statistiques de performances de cette requête dans le temps et d’utiliser un mécanisme permettant de forcer le plan. Pour éviter une telle situation, utilisez le processus ALTER <object> pour modifier la définition d’un objet conteneur chaque fois que cela est possible.

Vérifier régulièrement l’état des plans forcés

Le forçage de plan est un mécanisme pratique qui permet de corriger les problèmes de performances des requêtes importantes et de les rendre plus prévisibles. Comme pour les indicateurs de plan et les repères de plan, forcer un plan n’est pas la garantie qu’il sera utilisé dans les exécutions futures. En règle générale, quand le schéma de base de données change de manière à ce que les objets référencés par le plan d’exécution sont modifiés ou supprimés, le forçage de plan échoue. Dans ce cas, SQL Server a recours à la recompilation des requêtes et la raison réelle de l’échec du forçage apparaît dans sys.query_store_plan. La requête suivante retourne des informations sur les plans forcés :

USE [QueryStoreDB];
GO

SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,
    force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1;

Pour obtenir la liste complète des raisons, consultez sys.query_store_plan. Vous pouvez aussi utiliser le XEvent query_store_plan_forcing_failed pour suivre les échecs de l’utilisation forcée d’un plan et y remédier.

Éviter de renommer les bases de données pour des requêtes associées à des plans forcés

Les plans d’exécution référencent les objets avec des noms en trois parties, tels que database.schema.object.

Si vous renommez une base de données, le forçage de plan échoue, ce qui entraîne une recompilation dans toutes les exécutions de requête suivantes.

Utiliser le Magasin des requêtes dans des serveurs stratégiques

Les indicateurs de trace globaux 7745 et 7752 peuvent être utilisés pour améliorer la disponibilité des bases de données à l’aide du Magasin des requêtes. Pour plus d’informations, consultez Indicateurs de trace.

• L’indicateur de trace 7745 empêche le comportement par défaut selon lequel le Magasin des requêtes écrit des données sur le disque avant que SQL Server puisse être arrêté. Cela signifie que les données du Magasin des requêtes qui ont été collectées mais pas encore enregistrées de façon permanente sur le disque seront perdues, jusqu’à la fenêtre de temps définie avec DATA_FLUSH_INTERVAL_SECONDS.
• L’indicateur de trace 7752 permet le chargement asynchrone du Magasin des requêtes. Cela permet de mettre en ligne une base de données et d’exécuter des requêtes avant la récupération complète du Magasin des requêtes. Le comportement par défaut consiste à charger de façon synchrone le Magasin de données des requêtes. Le comportement par défaut empêche l’exécution des requêtes avant la récupération du Magasin des requêtes, mais il évite également qu’une requête soit manquée lors de la collecte des données.

Utilisez le Magasin des requêtes dans la géoréplication active d’Azure SQL Database

Le Magasin des requêtes sur une géoréplication active secondaire d’Azure SQL Database est une copie en lecture seule de l’activité sur le réplica principal.

Évitez les niveaux incompatibles avec la géoréplication d’Azure SQL Database. La taille de calcul de la base de données secondaire doit être égale à celle de la base de données primaire, ou proche de celle-ci, et se trouver dans le même niveau de service que celui de la base de données primaire. Recherchez le type d’attente HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO dans sys.dm_db_wait_stats, qui indique une limitation du taux de journalisation des transactions sur le réplica principal en raison d’un décalage au niveau secondaire.

Pour plus d’informations sur l’estimation et la configuration de la taille de la base de données Azure SQL secondaire de la géoréplication active, consultez Configuration d’une base de données secondaire.

Veiller à l’adéquation entre le Magasin des requêtes et votre charge de travail

Les meilleures pratiques et les recommandations relatives à la configuration et à la gestion du Magasin des requêtes ont été développées dans cet article : Meilleures pratiques pour la gestion du Magasin des requêtes.

Contenu connexe

• Options ALTER DATABASE SET (Transact-SQL)
• Affichages catalogue du Magasin des requêtes (Transact-SQL)
• Procédures stockées du Magasin des requêtes (Transact-SQL)
• Utilisation du Magasin des requêtes avec l’OLTP en mémoire
• Guide d’architecture de traitement des requêtes
• Indicateurs du Magasin des requêtes
• Surveillance des performances à l’aide du magasin des requêtes
• Réglage des performances avec le Magasin des requêtes
• Analyse et stockage des requêtes historiques dans Azure Synapse Analytics