asked
Parameter Sniffing
One of my procs was not returning proper result sets. If I run the raw sql from the proc it returns the desired output. I just gave a try and recompiled the proc and it started working. Later on I observed that the stored proc has a parameter sniffed in. My question is, can parameter sniffing cause wrong data fetch? What else could have happened before recompilation of he proc, that it was not functioning properly. Before recompilation the proc was returning completely wrong set of data. In addition to that my proc has a 'OUT' parameter, which actually returns the data.
Parameter sniffing is occurring any time you have a parameter within a stored procedure or prepared SQL statement. You also get variable sniffing during a recompile. Parameter sniffing is only a problem when you have skewed or out of date statistics that can lead to plans that cause bad performance for some data sets. Parameter sniffing won't affect the data returned by a query, just how that data is retrieved from the system. So, no, you won't get incorrect data. That suggests a possible corruption problem with the database.
16 People are following this question.
