M365Con 2027 – conférence en ligne gratuite · 11–22 Jan 2027 · 120 sessions m365con.net

Parameter sniffing

Concept

Comportement des moteurs de type SQL Server qui compilent un plan pour les valeurs de paramètres observées à la compilation puis réutilisent le plan en cache, ce qui peut nuire aux performances pour des valeurs de paramètres très différentes ; également appelé sensibilité aux paramètres.

✦ CONCEPTParameter sniffingM365.FM
Image titre : Parameter sniffing (Concept)

Description

Le parameter sniffing est un comportement des moteurs de bases de données de type SQL Server dans lequel un plan de requête est compilé pour les valeurs de paramètres observées au moment de la compilation, puis le plan mis en cache est réutilisé. On parle aussi de sensibilité aux paramètres. Ce comportement n'est pas un défaut en soi : le moteur examine les valeurs de paramètres en vigueur lors de la compilation ou de la recompilation d'une requête et les transmet à l'optimiseur de requêtes, qui peut alors produire un plan d'exécution plus efficace pour ces valeurs. La difficulté apparaît lorsque le plan mis en cache est ensuite utilisé avec des valeurs de paramètres qui semblent très différentes des valeurs d'origine, car un plan adapté à une valeur peut être peu performant pour une autre.

Le guide d'architecture du traitement des requêtes de SQL Server publié par Microsoft décrit le parameter sniffing dans une section consacrée à la sensibilité aux paramètres. Selon ce guide, ce processus s'applique aux procédures stockées, aux requêtes exécutées via sp_executesql et aux requêtes préparées. Il interagit directement avec la réutilisation des plans, car les plans compilés sont stockés dans le cache de plans et peuvent être réutilisés par des exécutions ultérieures. Le premier ensemble de valeurs qui déclenche une compilation façonne donc le plan reçu par les appels suivants, ce qui explique que les résultats puissent varier selon l'appel qui a compilé le plan.

Le guide mentionne un remède : l'indicateur de requête OPTION (RECOMPILE). Avec cet indicateur, l'optimiseur traite les paramètres et les variables locales comme des constantes à la compilation, qui peuvent être intégrées sous forme de littéraux. Les valeurs utilisées sont celles en vigueur juste avant l'exécution de l'instruction, qui ne sont pas nécessairement les valeurs initialement transmises. Cela peut aider lorsque les données d'entrée sont très variables ou asymétriques. Le guide avertit aussi que l'indicateur recompile la requête à chaque exécution, ce qui peut être coûteux, et conseille de mesurer l'effet sur l'ensemble de la charge de travail avant de l'adopter. Le guide renvoie également à un article distinct sur l'optimisation Parameter Sensitive Plan pour une lecture complémentaire.

Un détail connexe de la même documentation concerne les variables locales. Leurs valeurs ne sont pas examinées à la compilation. L'optimiseur estime plutôt à partir des informations de densité ou, en l'absence de statistiques, utilise des pourcentages fixes pour les comparaisons d'égalité et d'intervalle. Cette distinction est importante lors du diagnostic d'une requête lente, car un paramètre et une variable locale peuvent conduire à des estimations et donc à des plans différents.

En pratique, le parameter sniffing se manifeste par des requêtes rapides pour certaines entrées et lentes pour d'autres, ou par une performance qui change après l'éviction d'un plan et sa recompilation. L'épisode de M365.FM « Fix Bad Data Upstream with T-SQL Contracts » cite le parameter sniffing parmi les défaillances courantes en entreprise, le décrivant comme un phénomène qui peut ressembler à du hasard. L'épisode présente aussi la forme des plans comme une question de gouvernance : si la forme des plans change, les dépenses et la fiabilité changent avec elle, de sorte que le comportement des plans se traduit en stabilité des coûts et en risque opérationnel.

L'épisode « Design Reliable and Resilient Azure Architectures » place le parameter sniffing dans un processus de dépannage pour Azure SQL Database. Il suggère d'activer Intelligent Insights et Query Performance Insight, de capturer les requêtes de longue durée, d'examiner les plans d'exécution et d'identifier les index manquants ou le parameter sniffing. Il mentionne en outre les recommandations de réglage automatique d'Azure SQL et les suggestions d'index via Azure Advisor, ainsi que l'optimisation des requêtes, un indexage approprié, et la possibilité d'augmenter les DTU ou les vCores ou de passer à Hyperscale.

Pris ensemble, les sources traitent le parameter sniffing comme un compromis de mise en cache des plans : réutiliser un plan compilé économise du travail de compilation, alors qu'un plan optimisé pour une valeur de paramètre peut ne pas convenir à d'autres. Les décisions courantes à ce sujet consistent à examiner les plans d'exécution, à vérifier si la distribution des données est asymétrique, et à mettre en balance le coût de compilation d'options comme OPTION (RECOMPILE) avec le bénéfice de plans mieux adaptés.

Aperçu compilé avec l'IA à partir des épisodes M365.FM et des informations de cette page. Tu peux l'améliorer avec le bouton de modification ci-dessous.

TypeConcept

Questions fréquentes

Les questions marquées IA ont été rédigées avec l'IA à partir des informations de cette page.

Qu'est-ce que le parameter sniffing ? (IA)

C'est le comportement des moteurs de type SQL Server qui compilent un plan de requête pour les valeurs de paramètres observées au moment de la compilation, puis réutilisent le plan en cache. Cela peut nuire aux performances lorsque les valeurs de paramètres suivantes diffèrent fortement. On parle aussi de sensibilité aux paramètres.

Comment OPTION (RECOMPILE) peut-il influencer les problèmes de parameter sniffing ? (IA)

Selon le guide de traitement des requêtes de Microsoft, cet indicateur fait traiter par l'optimiseur les paramètres et les variables locales comme des constantes à la compilation, pouvant être intégrées sous forme de littéraux, ce qui peut aider avec des données très variables ou asymétriques. La requête est recompilée à chaque exécution, ce qui peut être coûteux ; il convient donc de mesurer d'abord l'effet sur l'ensemble de la charge de travail.

Les variables locales sont-elles examinées de la même manière que les paramètres ? (IA)

Non. La documentation indique que les valeurs des variables locales ne sont pas examinées à la compilation. L'optimiseur estime plutôt à partir des informations de densité ou, sans statistiques, utilise des pourcentages fixes pour les comparaisons d'égalité et d'intervalle.

Glossaire

Community wiki

Short info above, community knowledge below: add practical tips, examples or corrections. New contributions are reviewed first; trusted members publish directly.

Experts et entreprises pour ce sujet

Personne n'a encore indiqué ce sujet. Ajoutez-le à votre profil.

Maintenu par Mirko Peters (Microsoft MVP) · Dernière mise à jour: