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);

Dans les propriétés du Query Store, vous pouvez aussi vérifier les paramètres suivants :

• Actual_state – doit être à READ_WRITE ;
• Query_capture_mode – 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] 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. Cette crainte n’est pas fondée. L’impact est négligeable dans la grande majorité des cas, et les informations fournies par le Query Store sont indispensables pour diagnostiquer et résoudre les problèmes de performance.

Microsoft l’active par défaut

L’argument le plus convaincant : depuis SQL Server 2022, le Query Store est activé par défaut sur toutes les nouvelles bases de données. Il est également activé par défaut sur Azure SQL Database et Azure SQL Managed Instance depuis plusieurs années, sur des millions de bases de données en production. Microsoft ne prendrait pas cette décision si le Query Store posait un problème de performances.

Une architecture asynchrone conçue pour ne pas gêner

Le Query Store a été conçu dès le départ pour minimiser son impact :

1. Écriture en mémoire d’abord : les statistiques d’exécution sont collectées en mémoire, pas sur disque.
2. Écriture asynchrone sur disque : les données en mémoire sont écrites sur disque par un processus en arrière-plan, de manière asynchrone. L’exécution des requêtes n’est jamais bloquée par l’écriture des données du Query Store.
3. Intervalle de vidage configurable : par défaut, les statistiques sont écrites sur disque toutes les 15 minutes (DATA_FLUSH_INTERVAL_SECONDS = 900), ce qui réduit encore les opérations d’entrée/sortie.

Un overhead de 3 à 5 % dans le pire des cas

Microsoft estime l’overhead moyen à 3 à 5 %. En pratique, pour les charges de travail classiques basées sur des procédures stockées et des requêtes paramétrées, l’impact est souvent imperceptible. Seules les charges très lourdes avec beaucoup de requêtes ad hoc non paramétrées peuvent observer un impact plus marqué, ce qui est atténué par le mode de capture AUTO (par défaut) ou CUSTOM (disponible depuis SQL Server 2019).

Un mécanisme de protection automatique

Si le Query Store atteint sa limite de stockage (MAX_STORAGE_SIZE_MB), il bascule automatiquement en mode READ_ONLY. Il cesse alors de collecter de nouvelles données, mais n’a plus aucun impact sur les performances. Les requêtes continuent de s’exécuter normalement. C’est un mécanisme de sécurité : le Query Store se dégrade gracieusement plutôt que d’impacter la charge de travail.

Les bonnes pratiques pour un impact minimal

• Utilisez le mode de capture AUTO ou CUSTOM (jamais ALL en production).
• Définissez MAX_STORAGE_SIZE_MB à 1000 MB minimum.
• Gardez SIZE_BASED_CLEANUP_MODE = AUTO pour le nettoyage automatique.
• Maintenez votre instance SQL Server à jour avec les derniers correctifs cumulatifs.

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

Alternativement vous pouvez récupérer une trace en utilisant les événements étendus, des requêtes les plus coûteuses et les plus consommatrices. 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