Qu'est ce que le Parameter Sniffing
Qu'est ce que le Parameter Sniffing?
Il ne faut pas avoir peur du "parameter sniffing". Il s'agit d'un comportement normal du moteur.
Lorsqu'une procédure stockée est compilée ou recompilée, les valeurs des paramètres sont «reniflées» et utilisées pour l'estimation des cardinalités. Le plan est alors optimisé avec ces valeurs de paramètres comme valeurs littérals dans la requête. Prenez par exemple la procédure stockée suivante :
create procedure dbo.SearchProducts @Keyword varchar(100) as
select * from Products where Keyword like @Keyword
Supposons que la table posséde environ 100.000 lignes, et un index de colonne unique non ordonné en clusters sur la colonne Keyword.
Disons que vous appelez pour la première fois en paramètre @Keyword LIKE "XBOX%". Supposons que le nombre de lignes dans la table avec la valeur XBOX est très petite - quelques dizaines de lignes. L'optimiseur peut choisir d'utiliser un plan de requête qui utilise l'index sur la colonne Keyword pour évaluer le LIKE, puis une boucle pour récupérer les autres colonnes de la ligne.
Cet index seek + bookmark lookup sera mis en cache et réutilisé pour les exécutions ultérieures de la procédure stockée.
Les problèmes de performance causés par la Parameter Sniffing
Cependant, à un moment donné dans le futur, le moteur doit compiler/recompiler un nouveau plan pour la procédure stockée (le plan peut être trop vieux ou la mise à jours des statistiques a éjecté le plan pour la table Produits, etc.) Malheureusement, l'exécution de la procédure qui a compilé le nouveau plan avait un paramètre @Keyword LKE 'KINECT%. Supposons que le filtre 'KINECT%" renvoie 10% des lignes dans la table. Lors de la compilation de la procédure avec ce paramètre, SQL peut sélectionner un plan de requête qui utilise un scan complet de table. Ce plan serait idéal pour le paramètre 'KINECT%", mais serait désastreux pour d'autres valeurs avec plus de sélectivité.
Malheureusement, suivant la recompilation, le scan de la table pourrait également êre mis en cache, puis réutilisé. La performance des exécutions ultérieures avec les autres valeurs serait dégradée.
Le parameter sniffing permet à SQL de compiler un plan qui est adapté au type de paramètre qui est passé dans la procédure stockée. De manière générale, cette fonctionalité permet d'économiser la phase de compilation pendant l'exécution de la procédure stockée, mais ce mécanisme requière une exigence particulière pour que tout fonctionne comme prévu, c'est que les valeurs des paramètres utilisé pour la compilation soit «typique». Comme illustré dans cet exemple hypothétique, une procédure ou une requête paramétrée peut malheureusement parfois être exécutée avec un paramètre atypique (des données biaiser sont souvent en jeu dans ces cas).
Le parameter sniffing peut affecter toutes sortes de requêtes, mais les requêtes qui utilisent LIKE sont particulièrement sujet à ce genre de problème. Les problèmes de performance causés par le parameter sniffing sont généralement considérés By Design.
Plan d'action:
Depuis SQL Server 2005, nous avons une nouvelle fonctionnalité qui permet la recompilation individuelle des statments au lieu de compiler le plan d'exécution pour toute procédure stockée. Vous trouverez ci-dessous quelques solutions de contournement lorsque vous êtes affecté par un problème de performance.
1. Utilisez une variable pour feinter le moteur. Comme la variable @Keyworddummy n'est pas parametrable, le moteur sera obligé de recompiler à chaque exécution du statment, exemple ci-dessous:
create procedure dbo.SearchProducts
@Keyword varchar(100)
As
Declare @Keyworddummy as varchar(100)
Set @Keyworddummy = @Keyword
select * from Products where Keyword like @Keyworddummy
2. Pour éviter cela ou d'autres situations similaires, vous pouvez utiliser l'option de requête suivante:
OPTIMIZE FOR
RECOMPILE
3. Désactivez la mise à jour des statistiques automatique.
Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |