question

rudrapbiswas avatar image
rudrapbiswas 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.
stored-proceduresparameters
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You'll probably need to post an example to help the experts to help you. Interesting question...
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.