Activer et utiliser le Query Store

Le Query Store est une fonctionnalité disponible dans SQL Server depuis la version 2016, qui conserve et agrège les informations de performance des requêtes.

Activer le Query Store

Vous pouvez activer le Query Store ou vérifier que le Query Store est bien activé en utilisant les propriétés de la base de données.

Pour ouvrir la fenêtre des propriétés de la base de données, faites un clic droit sur la base de données dans SQL Server Management Studio (SSMS) et choisissez « Propriétés » ou en anglais « Properties ». Voir l’image ci-dessous.

Dans la fenêtre qui s’ouvre, cliquez sur « Query Store » dans le menu de gauche, en français, « Magasin de requêtes ».

Le query store doit être activé (Read-Write), et le mode de capture doit être « Auto ». Voir l’image ci-dessous.

Activer le Query Store par script

Cette requête peut être utilisée pour voir sur quelles bases de données le Query Store est activé sur votre instance :

-----------------------------------------------------------------
-- check is the query store is enabled on some databases.
--
-- rudi@babaluga.com, go ahead license
-----------------------------------------------------------------

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT 
	d.name,
	d.database_id,
	d.create_date,
	d.state_desc as [state]
FROM sys.databases d
WHERE d.is_query_store_on = 1
OPTION (RECOMPILE, MAXDOP 1);

Pour savoir s’il collecte vraiment, interrogez la vue sys.database_query_store_options dans la base, et vérifiez les colonnes suivantes :

• actual_state_desc : doit être à READ_WRITE ;
• query_capture_mode_desc : doit être à AUTO ;
• max_storage_size_mb devrait être à 1000 au moins pour être sûr qu’il y a cet espace dédié au Query Store pour contenir un historique suffisant au diagnostic.

Pour activer le Query Store sur une base de données par requête, utilisez la requête suivante :

-----------------------------------------------------------------------------------
-- Activate the Query Store on a database and apply recommended settings
-- (change db_name)

-- To specify the database name, use the "Specify Values for Template Parameters"
-- Navigate to Query-> Specify Values for Template Parameters.
-- Or use keyboard shortcut key Ctrl+Shift+M. 
--
-- rudi@babaluga.com, go ahead license
-----------------------------------------------------------------------------------

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

ALTER DATABASE [<db_name, sysname, >] SET QUERY_STORE = ON
(
	OPERATION_MODE = READ_WRITE, 

	-- Data retention period (30 days by default, 60 for more history)
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),

    -- Flush to disk (900 sec = 15 min by default)
    DATA_FLUSH_INTERVAL_SECONDS = 900,

   -- Maximum storage size for Query Store
   -- Default 2019+ = 1000 MB, but 2000-5000 MB recommended in production
 	MAX_STORAGE_SIZE_MB = 2000, 

    -- Stats aggregation interval (60 min by default, 30 for more granularity)
    INTERVAL_LENGTH_MINUTES = 60,

   -- Automatic cleanup when close to the limit
    SIZE_BASED_CLEANUP_MODE = AUTO,
 
    -- Capture mode: AUTO filters trivial queries
    -- ALL = capture everything (debug), NONE = pause, CUSTOM = fine rules (2019+)
	QUERY_CAPTURE_MODE = AUTO
)
GO

Le Query Store a-t-il un impact sur les performances ?

Une crainte fréquente est que l’activation du Query Store dégrade les performances du serveur. La réponse dépend de la charge et du mode de capture, et Microsoft ne publie aucun pourcentage : le chiffre de 3 à 5 % qui circule ne vient pas de sa documentation.

Microsoft l’active par défaut

Depuis SQL Server 2022, le Query Store est activé par défaut sur toutes les nouvelles bases de données. Une base restaurée ou migrée depuis une version antérieure conserve en revanche ses propres réglages : après une migration, vérifiez qu’il est bien actif. Il est également activé par défaut sur Azure SQL Database et Azure SQL Managed Instance.

Plusieurs fonctionnalités de l’optimiseur en dépendent : les Query Store hints, le CE feedback et le DOP feedback.

Une collecte en mémoire, écrite plus tard

Les statistiques d’exécution sont collectées en mémoire, puis écrites sur disque de façon asynchrone, par défaut toutes les 15 minutes (DATA_FLUSH_INTERVAL_SECONDS = 900). La capture d’une requête nouvelle est elle aussi asynchrone : elle apparaît dans le magasin quelques secondes après son exécution.

Le coût de la collecte selon la charge

Mesuré sur SQL Server 2025 :

• sur une charge paramétrée (procédures stockées, requêtes préparées), le coût du magasin n’a pas été mesurable en mode ALL, AUTO ou CUSTOM ;
• sur une charge ad hoc, où chaque requête a un texte différent, le mode ALL a ralenti les requêtes de 19 % sur une connexion et de 41 % sur huit ; AUTO et CUSTOM, qui ne capturent pas ces requêtes exécutées une seule fois, se sont limités à quelques pour cent ;
• le mode NONE, qui arrête la capture des nouvelles requêtes, n’allège pas le magasin : il prend un verrou exclusif à chaque exécution d’une instruction qu’il n’a pas capturée, et a multiplié par 3,6 la durée d’une requête paramétrée exécutée par huit connexions.

Le mode de capture AUTO est le défaut depuis SQL Server 2019 (ALL en 2016 et 2017). Le mode CUSTOM, disponible depuis SQL Server 2019, permet de régler les seuils de capture si AUTO laisse encore passer trop de requêtes.

Quand le Query Store passe en lecture seule

Si le Query Store atteint sa limite de stockage (MAX_STORAGE_SIZE_MB), il bascule automatiquement en mode READ_ONLY. Il bascule aussi, temporairement, quand la mémoire des données en attente d’écriture atteint sa limite interne (readonly_reason = 262144), ce qui arrive sous une charge ad hoc soutenue en mode ALL. Dans les deux cas, les requêtes continuent de s’exécuter normalement, mais le magasin n’enregistre plus rien : vos données de diagnostic ont un trou. Le nettoyage automatique par taille (SIZE_BASED_CLEANUP_MODE = AUTO) évite le premier cas ; le mode de capture AUTO a évité le second dans nos essais.

Les bonnes pratiques pour un impact minimal

• Utilisez le mode de capture AUTO ou CUSTOM (jamais ALL en production).
• N’utilisez pas NONE pour alléger le magasin : pour suspendre la collecte, passez en READ_ONLY ou désactivez-le.
• Définissez MAX_STORAGE_SIZE_MB à 1000 MB minimum.
• Gardez SIZE_BASED_CLEANUP_MODE = AUTO pour le nettoyage automatique.
• Sur SQL Server 2016, 2017 et 2019, installez les dernières mises à jour cumulatives : les limites de mémoire qui protègent l’instance d’une charge ad hoc y sont arrivées par des CU.

Si vous n’avez pas accès au Query Store (SQL Server 2014 et antérieurs)

Vous pouvez sinon récupérer, avec les événements étendus, une trace des requêtes dont la durée dépasse une seconde : https://github.com/rudi-bruchez/tsql-scripts/blob/main/extended-events/on-prem/long-running-queries-create.sql

Pour aller plus loin

• Vidéos de sessions DataFrogs sur le Query Store.
  • vidéo : Nicolas Souquet – Tout savoir sur le Query Store.
  • vidéo : Steven Naudet