Pourquoi y a-t-il des différences de performances lorsqu'une fonction SQL est appelée à partir de l'application .Net et lorsque le même appel est effectué dans Management Studio ?

Question

Nous avons un problème dans nos environnements de test et de développement avec une fonction qui s'exécute parfois très lentement lorsqu'elle est appelée depuis une application .Net. Lorsque nous appelons cette fonction directement depuis Management Studio, elle fonctionne parfaitement.

Voici les différences lorsqu'ils sont profilés : De l'application :
CPU : 906
Lire : 61853
Ecritures : 0
Durée : 926

Depuis SSMS :
CPU : 15
Lectures : 11243
Ecritures : 0
Durée : 31

Maintenant, nous avons déterminé que lorsque nous recompilons la fonction, les performances reviennent à ce que nous attendons et le profil de performance lorsqu'il est exécuté à partir de l'application correspond à celui que nous obtenons lorsque nous l'exécutons à partir de SSMS. Il commence à ralentir à nouveau à des intervalles qui semblent aléatoires.

Nous n'avons pas constaté cela dans la prod, mais c'est peut-être en partie parce que tout y est recompilé sur une base hebdomadaire.

Alors qu'est-ce qui peut causer ce genre de comportement ?

Editer -
Nous avons finalement pu nous attaquer à ce problème et la restructuration des variables pour gérer le reniflage des paramètres semble avoir fait l'affaire...un extrait de ce que nous avons fait ici : Merci pour votre aide.

        -- create set of local variables for input parameters - this is to help performance - vis a vis "parameter sniffing"
    declare @dtDate_Local                  datetime
           ,@vcPriceType_Local             varchar(10)
           ,@iTradingStrategyID_Local      int
           ,@iAccountID_Local              int
           ,@vcSymbol_Local                varchar(10)
           ,@vcTradeSymbol_Local           varchar(10)
           ,@iDerivativeSymbolID_Local     int
           ,@bExcludeZeroPriceTrades_Local bit

   declare @dtMaxAggregatedDate     smalldatetime
          ,@iSymbolID               int
          ,@iDerivativePriceTypeID  int

   select @dtDate_Local                  = @dtDate
          ,@vcPriceType_Local             = @vcPriceType
          ,@iTradingStrategyID_Local      = @iTradingStrategyID
          ,@iAccountID_Local              = @iAccountID
          ,@vcSymbol_Local                = @vcSymbol
          ,@vcTradeSymbol_Local           = @vcTradeSymbol
          ,@iDerivativeSymbolID_Local     = @iDerivativeSymbolID
          ,@bExcludeZeroPriceTrades_Local = @bExcludeZeroPriceTrades

Answer 1

J'ai eu un problème similaire avec les procédures stockées, et pour moi, il s'est avéré que c'était le "reniflage des paramètres". Cherchez sur Google et voyez si cela résout votre problème. Pour moi, l'accélération a été spectaculaire une fois le problème résolu.

Dans mon cas, j'ai résolu le problème en déclarant une variable locale pour chaque paramètre passé, puis j'ai assigné la variable locale à la valeur du paramètre, et le reste de la procédure a utilisé les variables locales pour le traitement... pour une raison quelconque, cela a empêché le reniflement des paramètres.

Answer 2

C'est généralement parce que vous obtenez un plan d'exécution différent dans votre connexion SSMS. Cela est souvent lié à des problèmes de reniflage de paramètres, lorsque le plan est généré avec une valeur spécifique qui n'est pas optimale pour d'autres valeurs des paramètres. Cela explique également pourquoi la recompilation résoudrait le problème. Ce fil de discussion semble avoir une bonne explication Reniflage de paramètres (ou Spoofing) dans SQL Server

Answer 3

Une cause probable est l'utilisation de statistiques périmées et/ou le reniflage de paramètres, ce qui entraîne la réutilisation d'un plan de requête en cache qui n'est pas optimal.

SSMS émet des déclarations de préambule que vous ne voyez pas, qui font que la requête soumise est recompilée à chaque fois, éliminant ainsi la possibilité d'utiliser un plan incorrect mis en cache.

Cela mettra à jour toutes les statistiques et rafraîchira les vues et les procs stockés (mais attention à l'exécution sur une machine de production) :

EXEC sp_updatestats

EXEC sp_refreshview 

EXEC sp_msForEachTable 'EXEC sp_recompile ''?'''