I have one old legacy stored procedure, which is taking long time in execution. It takes few parameters. I had reviewed my SP with DBA and she recommended me not to use these parameters directly into the stored procedure instead declare variables inside the SP AND assign those parameters to these variables. And then use it. I still want to understand how it is important? And how it improves performance. I have read about parameter sniffing but could not relate to this particular scenario, if someone could help here.
1 Answers
This suppresses parameter sniffing, as the local variable values are not known at the time of query optimization. Disabling parameter sniffing is not generally recommended. It's like using a query hint: an optimization done by exception for a particular reason. And in fact there is a query hint you can use to disable parameter sniffing for a particular query.
OPTION ( OPTIMIZE FOR UNKNOWN );
Related