A week ago, DBA ping me because of a high reads stored procedure in production and showed me the before VS after reads after they tune the SP —– You know the funny thing was i cant get the same reads in the exactly same environment. After some investigation, DBA told me was because of Parameter Sniffing.
Looks interesting so i spent some time to read through this article, looks like parameter sniffing is an expected behavior in SQL Server.
The solution how we solved the performance issue:
1. OPTION (OPTIMIZE FOR UNKNOWN)
– We tried this but this method is not supported by SQL Server 2005, so…. out!
2. Local variables, this one works for us. Take the example from the link above:
ALTER PROCEDURE Get_SalesOrderHeader ( @FromCustid INT, @ToCustid INT )AS
DECLARE DECLARE @L_FromCustid INT
DECLARE @L_ToCustid INT
SET @L_FromCustid =@FromCustidSET @L_ToCustid =@ToCustid
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN @L_FromCustid AND @L_ToCustid