为什么从 .Net 应用程序调用 SQL 函数时与在 Management Studio 中进行相同调用时存在性能差异 [英] Why are there performance differences when a SQL function is called from .Net app vs when the same call is made in Management Studio

问题描述

我们在测试和开发环境中遇到了一个问题，当从 .Net 应用程序调用时，该函数运行速度非常缓慢.当我们直接从管理工作室调用这个函数时，它工作正常.

We are having a problem in our test and dev environments with a function that runs quite slowly at times when called from an .Net Application. When we call this function directly from management studio it works fine.

以下是分析时的差异:来自应用程序:
CPU:906
读取:61853
写:0
持续时间:926

Here are the differences when they are profiled: From the Application:
CPU: 906
Reads: 61853
Writes: 0
Duration: 926

来自 SSMS:
中央处理器:15
读取:11243
写:0
持续时间:31

From SSMS:
CPU: 15
Reads: 11243
Writes: 0
Duration: 31

现在我们已经确定，当我们重新编译函数时，性能会返回到我们预期的状态，并且从应用程序运行时的性能配置文件与我们从 SSMS 运行时获得的性能配置文件相匹配.它将以随机间隔再次开始减速.

Now we have determined that when we recompile the function the performance returns to what we are expecting and the performance profile when run from the application matches that of what we get when we run it from SSMS. It will start slowing down again at what appear to random intervals.

我们还没有在 prod 中看到过这种情况，但部分原因可能是因为每周都会在那里重新编译所有内容.

We have not seen this in prod but they may be in part because everything is recompiled there on a weekly basis.

那么什么可能导致这种行为?

So what might cause this sort of behavior?

编辑 -
我们终于能够解决这个问题并重组变量来处理参数嗅探似乎已经成功了……我们在这里所做的一个片段:感谢您的帮助.

Edit -
We finally were able to tackle this and restructuring the varables to deal with parameter sniffing appears to have done the trick...a snippet of what we did here: Thanks for your help.

        -- 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

推荐答案

这通常是因为您在 SSMS 连接中获得了不同的执行计划.通常与参数嗅探问题有关，当计划使用特定值生成时，该值对于其他参数值来说是次优的.这也解释了为什么重新编译会解决这个问题.这个线程似乎有一个很好的解释 SQL Server 中的参数嗅探(或欺骗)

This is usually because you are getting a different execution plan in your SSMS connection. Often related to parameter sniffing issues where when the plan gets generated with a specific value that is sub optimal for other values of the parameters. This also explains why recompiling would resolve the issue. This thread seems to have a good explanation Parameter Sniffing (or Spoofing) in SQL Server

这篇关于为什么从 .Net 应用程序调用 SQL 函数时与在 Management Studio 中进行相同调用时存在性能差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！