Le parameter sniffing est un concept dans SQL Server qui affecte les performances des procédures stockées. Dans cet article de blog, nous explorerons ce qu’est le parameter sniffing et comment il peut affecter les plans d’exécution des procédures stockées.

Qu’est-ce que le parameter sniffing ?

Lorsqu’une procédure stockée est exécutée pour la première fois, SQL Server crée un plan d’exécution optimal en fonction des paramètres passés à la procédure. Ce processus est appelé parameter sniffing. L’idée est de gagner du temps en réutilisant le plan d’exécution pour les exécutions ultérieures de la même procédure stockée avec des valeurs de paramètres différentes.

Cependant, le parameter sniffing peut parfois entraîner des problèmes de performances. Si le plan d’exécution généré pour la valeur de paramètre initiale n’est pas optimal pour une valeur de paramètre différente, les performances de la procédure stockée peuvent se dégrader considérablement.

Un exemple de parameter sniffing

Prenons un exemple simple pour comprendre le parameter sniffing. Nous avons une procédure stockée appelée GetCustomerOrders qui récupère les commandes pour un client spécifique à partir de la base de données WideWorldImporters.

CREATE OR ALTER PROC GetCustomerOrders (@CustomerID INT)
AS
SELECT *
FROM WideWorldImporters.Sales.Orders
WHERE CustomerID = @CustomerID
GO

Lorsque nous exécutons cette procédure stockée avec la première valeur, disons 1060, le plan d’exécution est généré en fonction de cette valeur de paramètre. Si nous exécutons ensuite la même procédure stockée avec une valeur différente, disons 90, le plan d’exécution peut ne pas être optimal pour cette nouvelle valeur de paramètre.

Cela peut être observé en activant le plan d’exécution réel dans SQL Server Management Studio (SSMS). Lorsque nous exécutons la procédure stockée avec la première valeur, le plan d’exécution montre qu’il lit 4 lignes. Cependant, lorsque nous exécutons la procédure stockée avec la deuxième valeur, le plan d’exécution montre qu’il lit 150 lignes.

Cette différence dans les plans d’exécution est due au parameter sniffing. Le plan d’exécution généré pour la première valeur de paramètre n’est pas optimal pour la deuxième valeur de paramètre, ce qui entraîne une dégradation potentielle des performances.

Surmonter le parameter sniffing

Il existe plusieurs techniques pour surmonter le parameter sniffing dans SQL Server :

1. Recompiler la procédure stockée : Une façon de surmonter le parameter sniffing est de recompiler la procédure stockée. Cela peut être fait en exécutant la procédure stockée système sp_recompile pour la procédure stockée spécifique. Cependant, cette approche a ses propres inconvénients et peut ne pas convenir à tous les scénarios.
2. Utiliser des variables locales : Une autre technique consiste à déclarer des variables locales à l’intérieur de la procédure stockée et à assigner les valeurs des paramètres à ces variables. Cela permet d’éviter le parameter sniffing car le plan d’exécution est généré en fonction des variables locales au lieu des paramètres.
3. Optimize For Unknown Query Hint : L’indice de requête Optimize For Unknown peut être utilisé pour simuler l’utilisation de variables locales dans une procédure stockée. Cet indice indique à SQL Server de générer un plan d’exécution en supposant que la valeur du paramètre est inconnue.
4. Configuration spécifique à la base de données : SQL Server a introduit une nouvelle amélioration au niveau de la base de données appelée Configuration spécifique à la base de données, qui peut aider à surmonter les problèmes de parameter sniffing. Cette fonctionnalité vous permet de configurer des paramètres spécifiques au niveau de la base de données pour optimiser les performances des requêtes.
5. Option (Recompile) : La technique la plus ancienne et la plus traditionnelle pour éviter la mise en cache des plans de requête est d’utiliser l’indice de requête OPTION (RECOMPILE). Cet indice force SQL Server à recompiler la procédure stockée ou la requête à chaque exécution, garantissant des performances optimales.

Chacune de ces techniques a ses propres avantages et inconvénients, et le choix de la technique dépend du scénario spécifique et des exigences.

Conclusion

Le parameter sniffing est un concept important à comprendre dans SQL Server. Bien qu’il puisse améliorer les performances en réutilisant les plans d’exécution, il peut également entraîner une dégradation des performances si le plan d’exécution n’est pas optimal pour différentes valeurs de paramètres. En utilisant des techniques telles que la recompilation des procédures stockées, l’utilisation de variables locales ou l’exploitation de la configuration spécifique à la base de données, vous pouvez surmonter les problèmes de parameter sniffing et optimiser les performances des requêtes.

Pour plus d’informations approfondies sur le parameter sniffing et d’autres techniques d’optimisation des performances, vous pouvez consulter ma Comprehensive Database Performance Health Check.