Partage via

Meilleures pratiques pour la gestion du Magasin des requêtes

  • Article

S’applique à : SQL Server base de données Azure SQL Azure SQL Managed Instance

Cet article décrit la gestion du Magasin des requêtes SQL Server et des fonctionnalités environnantes.

Remarque

Dans SQL Server 2022 (16.x), le Magasin des requêtes est désormais activé par défaut pour toutes les bases de données SQL Server nouvellement créées pour mieux suivre l’historique des performances, résoudre les problèmes liés au plan de requête et activer les nouvelles fonctionnalités du processeur de requêtes.

Magasin des requêtes par défaut dans base de données Azure SQL

Cette section décrit les paramètres optimaux de configuration par défaut dans Azure SQL Database, conçus pour garantir un fonctionnement fiable du magasin de requêtes et des fonctionnalités dépendantes. La configuration par défaut est optimisée pour la collecte des données en continu, c’est-à-dire pour passer le moins de temps possible aux états OFF/READ_ONLY. Pour plus d’informations sur toutes les options de Magasin des requêtes disponibles, consultez Options ALTER DATABASE SET (Transact-SQL).

Configuration Description Default Commentaire
MAX_STORAGE_SIZE_MB Spécifie la limite d’espace de données que le magasin de requêtes peut inclure dans la base de données client 100 avant SQL Server 2019 (15.x)
1000 à compter de SQL Server 2019 (15.x)		 Appliqué aux nouvelles bases de données
INTERVAL_LENGTH_MINUTES Définit la durée pendant laquelle les statistiques d’exécution collectées pour les plans de requête sont agrégées et rendues persistantes. Chaque plan de requête actif dispose au maximum d’une ligne pour une période définie avec cette configuration 60 Appliqué aux nouvelles bases de données
STALE_QUERY_THRESHOLD_DAYS Stratégie de nettoyage basée sur la durée, et qui contrôle la période de rétention des statistiques d’exécution persistantes et des requêtes inactives 30 Appliqué aux nouvelles bases de données et aux bases de données ayant la valeur par défaut précédente (367)
SIZE_BASED_CLEANUP_MODE Indique si un nettoyage automatique des données a lieu lorsque la taille des données du magasin de requêtes approche de la limite AUTO Appliqué à toutes les bases de données
QUERY_CAPTURE_MODE Indique si toutes les requêtes sont suivies, ou seulement un sous-ensemble de requêtes AUTO Appliqué à toutes les bases de données
DATA_FLUSH_INTERVAL_SECONDS Indique la durée maximale pendant laquelle les statistiques d’exécution capturées sont conservées dans la mémoire, avant le vidage sur disque 900 Appliqué aux nouvelles bases de données

Important

Ces paramètres par défaut sont automatiquement appliqués à l’étape finale de l’activation du magasin des requêtes dans une base de données Azure SQL. Après cela, la base de données Azure SQL ne modifiera pas les valeurs de configuration définies par les clients, à moins qu’elles aient un impact négatif sur les charges de travail principales ou sur la fiabilité des opérations du Magasin des requêtes.

Remarque

Le Magasin des requêtes ne peut pas être désactivé dans la base de données unique Azure SQL Database et le pool élastique. L’exécution de ALTER DATABASE [database] SET QUERY_STORE = OFF retourne l’avertissement 'QUERY_STORE=OFF' is not supported in this version of SQL Server.

Si vous souhaitez conserver vos paramètres personnalisés, utilisez ALTER DATABASE avec les options du magasin de requêtes pour rétablir la configuration à l’état précédent. Découvrez les meilleures pratiques liées au Magasin de données des requêtes pour savoir comment choisir des paramètres de configuration optimaux.

Définir le mode de capture optimal du Magasin des requêtes

Conservez les données les plus pertinentes dans le magasin de requêtes. Le tableau suivant décrit des scénarios standard pour chaque mode de capture du Magasin des requêtes :

Mode de capture du magasin des requêtes Scénario
Tous Analysez minutieusement votre charge de travail, c’est-à-dire toutes les formes de requêtes, leur fréquence d’exécution et les autres statistiques.

Identifiez les nouvelles requêtes dans votre charge de travail.

Détectez si des requêtes ad hoc sont utilisées pour identifier les opportunités de paramétrage défini par l’utilisateur ou automatique.

Remarque : il s’agit du mode de capture par défaut dans SQL Server 2016 (13.x) et SQL Server 2017 (14.x).
Automatique Concentrez-vous sur les requêtes pertinentes et actionnables. Les requêtes qui s’exécutent régulièrement ou qui consomment beaucoup de ressources en sont un exemple.

Remarque : dans SQL Server 2019 (15.x) et versions ultérieures, il s’agit du mode de capture par défaut.
Aucun Vous avez déjà capturé le jeu de requêtes que vous vouliez surveiller dans le runtime et souhaitez éliminer les confusions que pourraient provoquer les autres requêtes.

L’option Aucun est adaptée aux environnements de test et d’évaluation.

Elle est aussi appropriée pour les éditeurs de logiciels qui proposent le magasin de requêtes avec une configuration destinée à surveiller la charge de travail de leur application.

Cette option doit être utilisée avec précaution, car vous risquez de ne pas pouvoir suivre et optimiser de nouvelles requêtes importantes. Évitez d’utiliser l’option Aucun(e) sauf si l’un de vos scénarios l’exige.
Personnalisée SQL Server 2019 (15.x) a introduit un mode de capture personnalisé sous la commande ALTER DATABASE ... SET QUERY_STORE. Tandis que Auto est la valeur par défaut et recommandée, si la surcharge pouvant être introduite par le Magasin des requêtes est source de préoccupation, les administrateurs de la base de données peuvent utiliser des stratégies de capture personnalisées pour optimiser davantage e comportement de capture par le Magasin des requêtes. Pour plus d’informations et de recommandations, consultez les stratégies de capture personnalisée plus loin dans cet article. Pour plus d’informations sur la syntaxe, consultez l’article Options SET d’ALTER DATABASE.

Remarque

Les curseurs, les requêtes dans les procédures stockées et les requêtes compilées en mode natif sont toujours capturés quand le mode de capture du Magasin des requêtes est défini sur Tous, Auto ou Personnalisé. Pour capturer des requêtes compilées en mode natif, activez la collecte des statistiques par requête avec sys.sp_xtp_control_query_exec_stats.

Conserver les données les plus pertinentes dans le magasin des requêtes

Configurez le Magasin des requêtes afin qu’il contienne uniquement les données pertinentes. Ainsi, il s’exécutera toujours en offrant une excellente expérience de résolution des problèmes tout en ayant un impact minimal sur votre charge de travail normale.

Le tableau suivant décrit les bonnes pratiques :

Bonnes pratiques Setting
Limiter la conservation des données d’historique. Configurez la stratégie basée sur la durée pour activer le nettoyage automatique.
Filtrer les requêtes non pertinentes. Configurez l’option Mode de capture du Magasin des requêtes sur Auto.
Supprimer les requêtes les moins pertinentes quand la taille maximale est atteinte. Activez la stratégie de nettoyage basée sur la taille.

Stratégies de capture personnalisées

Quand le mode Capture Magasin des requêtes PERSONNALISÉ est activé, vous pouvez affiner la collecte de données sur un serveur spécifique au moyen de configurations supplémentaires du Magasin des requêtes, disponibles sous un nouveau paramètre de stratégie de capture du Magasin des requêtes.

Les nouveaux paramètres personnalisés définissent ce qui se passe pendant la durée seuil de la stratégie de capture interne. Il s’agit d’une durée limite pendant laquelle les conditions configurables sont évaluées et, si elles ont la valeur true, la requête peut être capturée par le Magasin des requêtes.

Le mode de capture du magasin de requêtes spécifie la stratégie de capture de requêtes du magasin de requêtes.

  • Tout : capture toutes les requêtes. Cette option est la valeur par défaut dans SQL Server 2016 (13.x) et SQL Server 2017 (14.x).
  • Auto : les requêtes peu fréquentes et les requêtes dont la durée de compilation et d’exécution n’est pas significative sont ignorées. Les seuils concernant le nombre d’exécutions, la durée de compilation et la durée d’exécution sont déterminés en interne. À partir de SQL Server 2019 (15.x), il s’agit de la valeur par défaut.
  • Aucune : le Magasin des requêtes cesse de capturer les nouvelles requêtes.
  • Personnaliser : permet un contrôle supplémentaire et permet d’ajuster la stratégie de collecte des données. Les nouveaux paramètres personnalisés définissent ce qui se passe pendant la durée seuil de la stratégie de capture interne. Il s’agit d’une durée limite pendant laquelle les conditions configurables sont évaluées et, si elles ont la valeur true, la requête peut être capturée par le Magasin des requêtes.

Le réglage d’une stratégie de capture personnalisée appropriée pour votre environnement doit être pris en compte lorsque :

  • La base de données est très volumineuse.
  • La base de données a un grand nombre de requêtes ad hoc uniques.
  • La base de données présente des limitations spécifiques en matière de taille ou de croissance.

Télécharger la dernière version de SQL Server Management Studio (SSMS)

Pour afficher les paramètres actuels dans Management Studio :

  1. Dans l’Explorateur d’objets SQL Server Management Studio, cliquez avec le bouton droit sur la base de données.
  2. Sélectionnez Propriétés.
  3. Sélectionnez Magasin des requêtes. Dans la page Magasin des requêtes, vérifiez que le mode d’opération (demandé) est en lecture-écriture.
  4. Modifiez le mode de capture du Magasin des requêtes en personnalisé.
  5. Notez que les quatre champs de stratégie de capture sous Stratégie de capture du Magasin des requête sont désormais activés et configurables.

Exemples de stratégies de capture personnalisées

L’exemple suivant définit QUERY_CAPTURE_MODE sur AUTO et définit un mode de capture personnalisé. Chacun des éléments suivants définit les stratégies de capture personnalisées sur sa valeur par défaut dans SQL Server 2022 (16.x). Envisagez d’ajuster ces valeurs pour réduire le nombre de requêtes capturées et, par conséquent, réduire l’empreinte sur disque du Magasin des requêtes. Il est recommandé de modifier progressivement ces valeurs par petits incréments.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

L’exemple de requête suivant modifie un Magasin des requêtes existante pour utiliser une stratégie de capture personnalisée qui remplace les paramètres par défaut pour EXECUTION_COUNT et TOTAL_COMPILE_CPU_TIME_MS.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        EXECUTION_COUNT = 100,
        TOTAL_COMPILE_CPU_TIME_MS = 10000
      )
    );

Taille maximale du Magasin des requêtes

La valeur de taille maximale par défaut du Magasin des requêtes est de 1000 Mo, à partir de SQL Server 2019 (15.x). Dans les versions précédentes, la valeur par défaut était de 100 Mo. L’augmentation de la limite de taille maximale du Magasin des requêtes est appropriée dans une base de données occupée avec de nombreux plans de requête uniques. L’ajustement de la stratégie de capture (voir la section précédente) est une considération plus importante pour limiter la taille sur disque du Magasin des requêtes et empêcher le Magasin des requêtes d’entrer en mode READ_ONLY. Pendant que le Magasin des requêtes collecte des requêtes, des plans d’exécution et des statistiques, sa taille dans la base de données croît jusqu’à atteindre cette limite. Quand cela se produit, le magasin de requêtes change automatiquement de mode d’opération pour passer en READ_ONLY et cesse de collecter les nouvelles données, ce qui signifie que l’analyse de vos performances n’est plus précise.

  • Dans SQL Server et Azure SQL Managed Instance, la limite de MAX_STORAGE_SIZE_MB n’est pas strictement appliquée.
  • Dans base de données Azure SQL, la valeur maximale autorisée MAX_STORAGE_SIZE_MB est de 10 240 Mo.

La taille de stockage est vérifiée seulement quand le Magasin des requêtes écrit des données sur le disque. Cet intervalle est défini par l’option DATA_FLUSH_INTERVAL_SECONDS ou par l’option de la boîte de dialogue Magasin des requêtes de Management Studio Intervalle de vidage des données.

  • La valeur par défaut de l’intervalle est 900 secondes (ou 15 minutes).
  • Si le Magasin des requêtes a enfreint la limite de MAX_STORAGE_SIZE_MB entre des vérifications de la taille de stockage, il passe en mode lecture seule.
  • Si l’option SIZE_BASED_CLEANUP_MODE est activée, le mécanisme de nettoyage permettant d’appliquer la limite MAX_STORAGE_SIZE_MB est également déclenché.
    • Une fois que suffisamment d’espace a été libéré, le mode Magasin des requêtes revient automatiquement en READ_WRITE.

Pour plus d’informations, consultez Options SET d’ALTER DATABASE MAX_STORAGE_SIZE_MB.

Intervalle de vidange des données (minutes)

L’intervalle de vidage des données définit la fréquence avant que les statistiques d’exécution collectées soient conservées sur le disque. Dans SQL Server Management Studio, la valeur est exprimée en minutes mais, dans Transact-SQL, elle est exprimée en secondes. La valeur par défaut est de 15 minutes (900 secondes).

  • L’augmentation de l’intervalle de vidage des données peut réduire l’impact global des E/S de stockage du Magasin des requêtes, mais la charge de travail d’E/S de stockage est plus volumineuse, avec moins d’impact sur l’utilisation du disque. Utilisez une valeur plus élevée si votre charge de travail ne génère pas de grandes quantités de requêtes et de plans différents, ou si vous pouvez supporter une durée de conservation des données plus élevée avant un arrêt de la base de données.
  • La diminution de l’intervalle de vidage des données diminue la quantité de données du Magasin des requêtes qui seraient perdues en cas d’arrêt, de perte d’alimentation ou de basculement. Il peut également faciliter l’impact des E/S de stockage à partir du Magasin des requêtes en écrivant sur le disque plus souvent, mais avec moins de données.

Remarque

L’indicateur de trace 7745 empêche l’écriture sur le disque des données du Magasin des requêtes en cas de commande d’arrêt ou de basculement. Pour plus d’informations, consultez Utiliser Magasin des requêtes dans les serveurs stratégiques.

Modifier les valeurs par défaut du Magasin des requêtes

Configurez le magasin de requêtes en fonction de votre charge de travail et selon vos besoins en matière de résolution des problèmes de performances. Les paramètres par défaut sont assez bons pour démarrer, mais vous devez surveiller le comportement du Magasin des requêtes au fil du temps et ajuster sa configuration en conséquence.

Afficher les paramètres actuels du Magasin des requêtes

Affichez les paramètres de Magasin des requêtes actuels dans SQL Server Management Studio (SSMS) ou T-SQL.

Télécharger la dernière version de SQL Server Management Studio (SSMS)

Pour afficher les paramètres actuels dans Management Studio :

  1. Dans l’Explorateur d’objets SQL Server Management Studio, cliquez avec le bouton droit sur la base de données.
  2. Sélectionnez Propriétés.
  3. Sélectionnez Magasin des requêtes.

Le script suivant définit une nouvelle valeur pour Taille maximale (Mo) :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

Utilisez SQL Server Management Studio ou Transact-SQL pour définir une valeur différente pour Intervalle de vidage des données :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

Intervalle de collecte des statistiques : définit le niveau de granularité des statistiques d’exécution collectées, exprimé en minutes. La valeur par défaut est de 60 minutes. Envisagez d’utiliser une valeur inférieure si vous avez besoin d’une précision plus fine ou de moins de temps pour détecter et atténuer les problèmes. Gardez à l’esprit que la valeur affecte directement la taille des données du Magasin des requêtes. Utilisez SQL Server Management Studio ou Transact-SQL pour définir une autre valeur pour Intervalle de collecte des statistiques :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);

Seuil de requêtes périmées (jours) : stratégie de nettoyage basé sur la durée qui permet de contrôler la période de rétention des statistiques d’exécution persistantes et des requêtes inactives, exprimées en jours. Par défaut, le Magasin des requêtes est configuré pour conserver les données pendant 30 jours, ce qui est peut-être inutilement long pour votre scénario.

Évitez de conserver les données d’historique que vous ne prévoyez pas d’utiliser. Cette pratique réduit les passages à l’état lecture seule. La taille des données du Magasin des requêtes et le temps de détection et d’atténuation des problèmes seront plus prévisibles. Utilisez Management Studio ou le script suivant pour configurer la stratégie de nettoyage basée sur la durée :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));

Mode de nettoyage basé sur la taille : indique si le nettoyage de données automatique aura lieu dès que la taille des données du magasin de requêtes s’approchera de la limite. Activez le nettoyage basée sur la taille pour vous assurer que le Magasin des requêtes s’exécutera toujours en mode lecture-écriture et collectera les données les plus récentes. Il n’y a aucune garantie sous des charges de travail lourdes que le nettoyage du magasin des requêtes maintiendra constamment la taille des données dans la limite. Il est possible que le nettoyage automatique des données passe en arrière-plan et bascule (temporairement) en mode lecture seule.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

Mode de capture du magasin de requêtes : spécifie la stratégie de capture de requêtes du magasin de requêtes.

  • Tout : capture toutes les requêtes. Cette option est la valeur par défaut dans SQL Server 2016 (13.x) et SQL Server 2017 (14.x).
  • Auto : les requêtes peu fréquentes et les requêtes dont la durée de compilation et d’exécution n’est pas significative sont ignorées. Les seuils concernant le nombre d’exécutions, la durée de compilation et la durée d’exécution sont déterminés en interne. À partir de SQL Server 2019 (15.x), il s’agit de la valeur par défaut.
  • Aucune : le Magasin des requêtes cesse de capturer les nouvelles requêtes.
  • Personnaliser : permet un contrôle supplémentaire et permet d’ajuster la stratégie de collecte des données. Les nouveaux paramètres personnalisés définissent ce qui se passe pendant la durée seuil de la stratégie de capture interne. Il s’agit d’une durée limite pendant laquelle les conditions configurables sont évaluées et, si elles ont la valeur true, la requête peut être capturée par le Magasin des requêtes.

Important

Les curseurs, les requêtes dans les procédures stockées et les requêtes compilées en mode natif sont toujours capturés quand le mode de capture du Magasin des requêtes est défini sur Tous, Auto ou Personnalisé. Pour capturer des requêtes compilées en mode natif, activez la collecte des statistiques par requête avec sys.sp_xtp_control_query_exec_stats.

Le script suivant définit QUERY_CAPTURE_MODE sur AUTO :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

Exemples

L’exemple suivant définit QUERY_CAPTURE_MODE sur AUTO et définit d’autres options recommandées dans SQL Server 2016 (13.x) :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60
    );

L’exemple suivant définit QUERY_CAPTURE_MODE sur AUTO et définit d’autres options recommandées dans SQL Server 2017 (14.x) pour inclure des statistiques d’attente :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

L’exemple suivant définit la stratégie de capture PERSONNALISÉE sur les valeurs par défaut de SQL Server 2019 (15.x), au lieu du nouveau mode de capture AUTOMATIQUE par défaut. Pour plus d’informations sur les options de stratégie de capture personnalisée et les valeurs par défaut, consultez <query_capture_policy_option_list>.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Maintenance du Magasin des requêtes

Cette section fournit des instructions sur la gestion de la fonctionnalité de magasin de requête proprement dite.

État du Magasin des requêtes

Le Magasin des requêtes stocke ses données dans la base de données utilisateur, ceci expliquant pourquoi sa taille est limitée (configurée avec MAX_STORAGE_SIZE_MB). Si les données du magasin de requêtes atteignent cette limite, le magasin de requêtes fait passer automatiquement l'état de Lecture-écriture à Lecture seule et arrête la collecte de nouvelles données.

Interrogez sys.database_query_store_options pour déterminer si le magasin de requêtes est actif et s’il collecte des statistiques d’exécution.

SELECT actual_state, actual_state_desc, readonly_reason,
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

L’état du Magasin de requêtes est déterminé par la colonne actual_state. S’il diffère de l’état souhaité, la colonne readonly_reason peut vous donner plus d’informations. Lorsque la taille du Magasin des requêtes dépasse le quota, la fonctionnalité passe en mode lecture seule et fournit une raison. Pour obtenir des informations sur les raisons, consultez sys.database_query_store_options.

Accès aux options du magasin de requêtes

Pour trouver des informations détaillées sur l'état du magasin de requêtes, exécutez ce qui suit dans une base de données utilisateur.

SELECT * FROM sys.database_query_store_options;

Définir l’intervalle du Magasin des requêtes

Vous pouvez remplacer l'intervalle d'agrégation des statistiques d'exécution de requête (la valeur par défaut est 60 minutes). La nouvelle valeur de l'intervalle est exposée via l'affichage sys.database_query_store_options.

ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

Les valeurs arbitraires ne sont pas autorisées pour INTERVAL_LENGTH_MINUTES. Utilisez l’un des intervalles suivants : 1, 5, 10, 15, 30, 60 ou 1440 minutes.

Remarque

Pour Azure Synapse Analytics, la personnalisation des options de configuration du Magasin des requêtes, comme illustré dans cette section, n’est pas prise en charge.

Utilisation de l’espace du magasin de requêtes

Pour vérifier la taille et la limite actuelles du magasin de requête, exécutez l’instruction suivante dans la base de données utilisateur.

SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Si le stockage du magasin de requêtes est saturé, utilisez l'instruction suivante pour l’étendre.

ALTER DATABASE <database_name>
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

Définir les options du magasin des requêtes

Vous pouvez définir simultanément plusieurs options de magasin de requêtes avec l'instruction ALTER DATABASE.

ALTER DATABASE <database name>
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON
);

Pour obtenir une liste complète des options de configuration, consultez Options ALTER DATABASE SET (Transact-SQL).

Nettoyer l’espace

Les tables internes du magasin de requêtes sont créées dans le groupe de fichiers PRIMARY lors de la création de la base de données. Cette configuration ne peut pas être modifiée ultérieurement. Si vous manquez d’espace, vous pouvez effacer les anciennes données du magasin des requêtes à l’aide de l’instruction suivante.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

Vous pouvez également effacer uniquement les données de requête ad hoc, car elles sont moins pertinentes pour les optimisations de requête et l’analyse du plan, mais utilisent autant d’espace.

Dans Azure Synapse Analytics, l’effacement du Magasin des requêtes n’est pas disponible. Les données des sept derniers jours sont automatiquement conservées.

Supprimer les requêtes ad hoc

Cela vous permet de purger les requêtes ad hoc et internes du magasin des requêtes, de sorte que celui-ci ne manque pas d’espace ni ne supprime les requêtes que nous devons vraiment suivre.

SET NOCOUNT ON
-- This purges adhoc and internal queries from
-- the Query Store in the current database
-- so that the Query Store does not run out of space
-- and remove queries we really need to track

DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
    SELECT q.query_id
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
    WHERE q.is_internal_query = 1  -- is it an internal query then we dont care to keep track of it
       OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
    GROUP BY q.query_id
    HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE())  -- if it has been more than 5 minutes since the adhoc query ran
    ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
    PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;

Vous pouvez définir votre propre procédure avec une logique différente pour effacer les données dont vous n’avez plus besoin.

L’exemple précédent utilise la procédure stockée étendue sp_query_store_remove_query pour supprimer les données inutiles. Vous pouvez également :

  • Utilisez sp_query_store_reset_exec_stats pour effacer les statistiques d’exécution d’un plan donné.
  • Utilisez sp_query_store_remove_plan pour supprimer un plan unique.

Contenu connexe