The other day I was looking into one of the bugs on production. There was a stored proc that was running extremely slow. I thought lets see what is written in the proc. When I copied the query and ran it separately, I was zapped. The query ran in 2-3 seconds, where as the proc took atleast 4-5 mins.
This made me curious, on digging I found a concept called "Parameter Sniffing". In simple words, according to a MSDN SQL Blog here, Parameter sniffing is:
SQL Server compiles the stored procedures using (sniffing) the parameters send the first time the procedure is compiled and put it in plan cache. After that every time the procedure executed again, SQL Server retrieves the execution plan from the cache and uses it (unless there is a reason for recompilation). The potential problem arises when the first time the stored procedure is executed, the set of parameters generate an acceptable plan for that set of parameters but very bad for other more common sets of parameters. link here
The solution to this problem was I just copied the stored proc parameters to a local variable of the proc. I then used these local variables to run the proc and Et Voila !. This works perfect!
Did someone else come across such scenarios or some simple but great solutions?
I am curious.
Top comments (0)