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.
Here are the differences when they are profiled: From the Application:
CPU: 906 Reads: 61853 Writes: 0 Duration: 926From SSMS:
CPU: 15 Reads: 11243 Writes: 0 Duration: 31Now we have determined that when we recompile the function the performance returns to what we are expecting and the performance profile w开发者_Go百科hen 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.
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
I had similar problem with stored procedures, and for me it turned out to be 'parameter sniffing'. Google that and see if it solves your problem, for me it was dramatic speed-up once I fixed it.
In my case, I fixed it by declaring a local variable for each parameters that was passed in, and then assigned the local variable to that parameter value, and the rest of the proc used the local variables for processing...for whatever reason, this defeated the parameter sniffing.
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
A likely cause is out of date statistics and/or parameter sniffing causing a cached query plan to be re-used that is sub-optimal.
SSMS emits pre-amble statements that you don't see, that cause the submitted query to be re-compiled each time, thus eliminating the possibility of using an incorrect cached plan.
This will update all statistics and refresh views and stored procs (but be careful about running on a Production machine):
EXEC sp_updatestats
EXEC sp_refreshview
EXEC sp_msForEachTable 'EXEC sp_recompile ''?'''
精彩评论