Victim Of Parameter Sniffing?

Have you come across a time when a stored procedure takes a lot longer to run than normal but the T-SQL itself runs a lot faster in Query Analyzer? This might be due to “parameter sniffing”. During compilation of a stored procedure the query optimizer attempts to “sniff” or “seek out” the values being passed in… in order to create an optimized query plan. The problem occurs when values that are passed in does not match a plan in the procedure cache and SQL Server attempts to create a new plan in the cache. This will cause the procedure to take much longer to execute and performance decreases.

The “fix” for parameter sniffing is to create local variables within the stored procedure that match the parameters being passed in. So for example,

Create Procedure myProc @name varchar(30), @age int

As

Declare @name_local varchar(30), @age_local int

Set @name_local = @name

Set @age_local = @age

Then use @name_local and @age_local throughout your stored procedure and you will get the same execution times as if you were running the straight T-SQL in query analyzer. Let me know if this helps!

Tags: parameter sniffing, parameters, SQL, SQL Server