How can I cure parameter sniffing on SQL Server 2000?

Question

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.

Answer 1

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:

• Be careful about SQL Injection
• In later versions of SQL Server, the server option "forced parameterization" can sniff even queries without parameters. It's turned off by default. But it's something to keep in mind when you upgrade SQL Server.