Parameter sniffing SQL Server : diagnostiquer avant de corriger

Une procédure stockée s'exécute en 80 millisecondes pour un client et en 40 secondes pour un autre. Le code n'a pas changé, le serveur n'est pas saturé et une nouvelle exécution semble parfois faire disparaître le problème. C'est un signe classique de plan sensible aux paramètres, souvent appelé parameter sniffing.

Le parameter sniffing n'est pas automatiquement un défaut. Lors de la compilation d'une requête paramétrée, SQL Server utilise les valeurs présentes pour estimer le nombre de lignes et choisir un plan. Réutiliser ce plan économise des compilations et reste généralement bénéfique. Le problème apparaît lorsqu'un seul plan doit couvrir des distributions de données très différentes.

Pourquoi un plan peut échouer

Imaginons que la plupart des clients aient moins de 100 commandes, tandis qu'un grand client en possède 8 millions. Un plan compilé pour un petit client peut privilégier les recherches d'index et les boucles imbriquées. Il devient très lent pour le grand client. À l'inverse, un plan compilé pour le grand client peut effectuer des scans et des jointures de hachage inutilement coûteux pour les autres.

Les données déséquilibrées, les filtres facultatifs, les statistiques obsolètes et les colonnes corrélées augmentent le risque.

Reproduire le comportement sans danger

Partez d'une procédure représentative :

CREATE OR ALTER PROCEDURE dbo.GetOrdersByCustomer
    @CustomerId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, OrderDate, TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderDate DESC;
END;

Dans un environnement hors production, testez une valeur sélective puis une valeur peu sélective. Capturez le plan d'exécution réel et les statistiques d'exécution. Si les performances dépendent de la valeur ayant servi à compiler le plan en cache, la sensibilité aux paramètres est fortement établie.

Ne videz pas tout le cache des plans en production. Cela provoque un pic de compilation et touche des charges sans rapport.

Méthode de diagnostic

1. Utilisez Query Store pour repérer les fortes variations de durée, CPU ou lectures logiques.
2. Comparez les nombres de lignes réels et estimés au premier join ou lookup important.
3. Notez les paramètres de compilation et d'exécution.
4. Contrôlez l'histogramme et la date de mise à jour des statistiques concernées.
5. Éliminez les conversions implicites, les prédicats non sargables et les index manquants.
6. Vérifiez que les exécutions rapides et lentes partagent la même requête et le même plan.

La bonne question n'est pas « Le plan est-il mauvais ? », mais « Est-il bon pour une forme de données importante et mauvais pour une autre ? »

Choisir la correction la plus ciblée

Commencez par les bases. Actualisez les statistiques inexactes et ne créez un index que si le modèle d'accès le justifie. Si la requête possède réellement plusieurs formes, les séparer est souvent plus clair que d'imposer un plan de compromis.

À partir de SQL Server 2022, l'optimisation Parameter Sensitive Plan peut conserver plusieurs variantes pour certains prédicats d'égalité. Vérifiez le niveau de compatibilité et l'éligibilité de la requête, puis observez les variantes dans Query Store.

Un hint Query Store ou un plan forcé peut stabiliser temporairement la situation. Surveillez-le, car l'évolution des données peut le rendre inadapté.

Utilisez OPTION (RECOMPILE) lorsque la compilation est peu coûteuse et que chaque exécution exige un plan adapté. Limitez-la à l'instruction concernée. OPTIMIZE FOR convient seulement avec une valeur représentative stable ou lorsqu'un plan moyen est volontairement recherché. Le SQL dynamique avec sp_executesql est utile pour les filtres facultatifs, car il sépare les formes de requêtes tout en conservant la paramétrisation.

Erreurs fréquentes

• Ne désactivez pas globalement le parameter sniffing pour une seule requête.
• N'ajoutez pas tous les index suggérés sans mesurer les coûts d'écriture et de stockage.
• Ne forcez pas un plan sans responsable, surveillance et condition de retrait.
• N'attribuez pas tout ralentissement intermittent au parameter sniffing.

Checklist de production

Capturez une référence, conservez le plan d'origine, testez des groupes de paramètres représentatifs, mesurez CPU et lectures logiques, déployez le changement le plus limité et surveillez Query Store. Préparez un retour arrière avant le déploiement.

Le parameter sniffing est avant tout un problème de distribution des données, pas un mystérieux défaut du cache. Prouvez les formes de données concurrentes, puis choisissez une solution adaptée. Pour un second avis, utilisez le formulaire de questions ci-dessous avec la procédure, des paramètres représentatifs et un plan anonymisé.