February 16, 2012 at 11:13 am
Hi Guys,
I created one SP.
It takes time to execute first time. From second time onwards it is execute fast.
But if I change the value for input parameters, again it takes time to execute.
Example:
SP Name: GetEmployee
InPutParameters: EmpName
First time: EXEC GetEmployee 'vijay' -- It took 2 seconds to display results
Second time: EXEC GetEmployee 'vijay' -- It took 0 seconds to display results
If I change input parameter (EmpName) value, again it is taking time
Third time: EXEC GetEmployee 'ABCD' -- It took 2 seconds to display results
Fourth time: EXEC GetEmployee 'ABCD'-- It took 0 seconds to display results
Can anyone tel me how/when stored procedure is recompiled.
Regards,
Vijaykumar
February 16, 2012 at 11:21 am
It will be recompiled when table structure changes or significant changes in table indexing and statistic s take place.
Make sure you statistics are always up to date, but the plan arrived at the first time it is compiled may not be optimum for subsequent parameter values. If the index used for query can have wide variations in distribution and densities with different values you may want to try various query hints to force a more appropriate plan.
The other option is to use dynamic SQL of some sort in the query.
The probability of survival is inversely proportional to the angle of arrival.
February 16, 2012 at 11:22 am
That's most likely data caching (data pages cached in the buffer pool), not plan caching as the plan would have been reused for all unless you did something to clear the plan cache (or alter the procedure or tables it depends on)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2012 at 11:33 am
GilaMonster (2/16/2012)
That's most likely data caching (data pages cached in the buffer pool), not plan caching as the plan would have been reused for all unless you did something to clear the plan cache (or alter the procedure or tables it depends on)
Excellent point and that may be the more practical reason you are seeing what you are seeing.
You would have to analyze the query plans generated for different values to evaluate whether or not you had a parameter sniffing (or indexing) issue.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply