Optimisation du plan d'exécution de procédure stockée SQL: reniflement des paramètres et son impact

Les performances des procédures stockées SQL peuvent être considérablement affectées par un phénomène connu sous le nom de sniffing paramètre. Cela se produit lorsque le moteur de base de données compile un plan d'exécution de procédure stocké en fonction des valeurs des paramètres d'entrée fournies au moment de la compilation.

Considérez la procédure stockée suivante:

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
AS
    IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP
    -- Do Something using @MyDate

Dans ce scénario, si @MyDate est transmis en tant que NULL pendant la compilation initiale, la base de données optimise le plan d'exécution pour cette valeur. Cependant, les appels ultérieurs à la procédure avec d'autres valeurs d'entrée peuvent entraîner de mauvaises performances, même si ces valeurs sont également nulles. La procédure peut varier radicalement en fonction des valeurs d'entrée transmises, conduisant à des temps d'exécution imprévisibles.

Plans d'exécution médiocres: Le moteur peut générer des plans d'exécution inefficaces qui ne sont pas optimaux pour les données réelles en cours de traitement, ce qui résulte de l'étude de la requête lente. Mauvais

Dans le cas mentionné, le plan d'exécution généré pour @mydate était médiocre même lorsque la valeur utilisée était nul. Ce comportement est inhabituel et suggère un problème avec le reniflement des paramètres. @Mydate datetime = null COMME DÉCLAREZ @MYDATE_COPY DateTime Définissez @MyDate_Copy = @MyDate Si @mydate_copy est null set @mydate_copy = current_timestamp - Faites quelque chose en utilisant @MyDate_Copy

• Aperçu du problème
• Le problème sous-jacent dans SQL Server 2005 est un problème connu avec le reniflement des paramètres à certains niveaux de patch. Dans SQL Server 2008, la clause Optimize for Unknown peut être utilisée pour résoudre ces problèmes. efficacement. Évitez d'utiliser des valeurs nulles comme paramètres d'entrée, car ils peuvent déclencher des problèmes d'optimisation involontaires.

Surveillez régulièrement les performances des procédures stockées et ajustez les plans d'exécution selon les besoins.