Parameter Sniffing Gone Bad

In rare instances, SQL Server's parameter sniffing behavior can result in dramatically poor performance.

The test is simple.

If your stored procedure takes an inordinate amount of time to run, but an equivalent query can runs much faster with local variables in place of the stored procedure parameters, then you may have a case of parameter sniffing gone bad.

Adding the local variable layer between your parameters and the interior of your proc is a good workaround to this problem.

It's possible too that the proc is overly complex if this occurs.