sqlsql-serversql-server-2000parameter-sniffing

How can I cure parameter sniffing on SQL Server 2000?

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.

Solution

  • 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: