Cache Stored Procedure

  • 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

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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply