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?