I am having an issue where an update query with about 70 parameters times-out on occasion. Based on some research, I believe this is due to packet sniffing. I saw that in newer versions of SQL Server, I can use the Option(recompile) clause, but that does not work in my case, since I am using server 2000.
I am using sqlhelper.executeNonQuery and not a stored procedure.
An easy fix is not to use parameters. Instead of:
SELECT * FROM YourTable WHERE UserName = @myUserName;
Pass:
SELECT * FROM YourTable WHERE UserName = 'PFranchise'
If SQL Server does not know about parameters, it can't sniff them! SQL Server will recompile the query plan for every query.
Two notes about this approach:
As far as I know there is no "out of the box" way like Option(recompile), however I remember I found a way to fool the optimizer. It seems to sniff only the parameter that you're actually PASSING externally to the query, not all of them. So, if you try to run
SELECT MyField1, MyField2 FROM MyTable WHERE MyOtherField = @MyParm
Parameter sniffing WILL happen, however if you write something like
DECLARE @MyUnsniffableParm varchar(30)
SET @MyUnsinffableParm = @MyParm
SELECT MyField1, MyField2 FROM MyTable WHERE MyOtherField = @MyUnsniffableParm
ad of course pass to your script just the @MyParm parameter, parameter sniffing doesn't seems to happen! Let me know If I recall correctly I have no SQL 2000 instances to try!
EDIT:
Looks like something else is doing the same out here: http://blogs.msdn.com/b/khen1234/archive/2005/06/02/424228.aspx
