• JALLYKAMOZE (8/29/2016)


    Grant Fritchey (8/29/2016)


    You're saying parameters and variables. Let's get real clear, because it matters. Parameters are values defined with the CREATE statement for a stored procedure, as part of the preparation of a prepared statement, or within the defined parameter values of sp_executesql. Variables are defined within a procedure or prepared statement. They look the same, but their location of definition matters. If you are using variables, then they are not sniffed except in the case of a recompile event and, because the variable value is unknown the optimizer, use only averages for the statistics outside of the variable sniffing situation. It's possible that you're getting bad plans because you're using variables (if you are) and they aren't giving the optimizer accurate enough row estimates. So which is it, variables or parameters?

    Thanks for your response, this is the scenerio, The parameters are used as part of the prepared statetment of the parent procedure, Now within the parent procedure i have a couple of other SPs that use the same values of the parameters for parent SP but this time they are used as variables in the subsequent Sps..

    So within the code you're doing this:

    SET @Variable = @Parameter;

    If so, then the variables will use average statistics rather than specific values to create the execution plans except in the case of a recompile when the actual value of the variable can be used. This means that if you don't use a recompile and you get slow performance, it's because average statistics are not generating a good enough plan. If, no the other hand, you're using recompile and you get slow performance, then you're hitting bad variable sniffing where the specific value is generating a plan that doesn't work well with the data. Frequently this second situation is in indication that your statistics at simply out of date.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning