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.

http://www.practicalsqldba.com/2012/06/sql-server-parameter-sniffing.html
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

????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????

Advertisements