How to prevent parameter sniffing?

When the database engine compiles a stored procedure, it may use the actual parameters supplied to prepare an execution plan. If the parameters are atypical, the plan may be slow for typical parameters. For consistent stored procedure (sproc) performance parameter sniffing should be eliminated.

A telltale sign of parameter sniffing when suddenly a stored procedure executes in 7 minutes, as an example, instead of the usual 15 seconds.

Technet article: Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

 -- SOLUTION 1 - Remap input parameters to local variables

USE AdventureWorks; 
GO 
 
CREATE PROC uspSuppliersByLocation 
          @pStateProvinceName NVARCHAR(50) 
AS 
  BEGIN 
    /**** REMAP parameter to prevent Parameter Sniffing ****/ 
    DECLARE  @StateProvinceName NVARCHAR(50) 
    SET @StateProvinceName = @pStateProvinceName 
    /**** END OF REMAP                                  ****/ 
    SELECT V.VendorID, 
           V.Name  AS Vendor, 
           A.AddressLine1, 
           A.AddressLine2, 
           A.City, 
           SP.Name AS State, 
           CR.Name AS Country 
    FROM     Purchasing.Vendor AS V 
             INNER JOIN Purchasing.VendorAddress AS VA 
               ON VA.VendorID = V.VendorID 
             INNER JOIN Person.Address AS A 
               ON A.AddressID = VA.AddressID 
             INNER JOIN Person.StateProvince AS SP 
               ON SP.StateProvinceID = A.StateProvinceID 
             INNER JOIN Person.CountryRegion AS CR 
               ON CR.CountryRegionCode = SP.CountryRegionCode 
    WHERE    SP.Name = @StateProvinceName 
    GROUP BY V.VendorID, 
             V.Name, 
             A.AddressLine1, 
             A.AddressLine2, 
             A.City, 
             SP.Name, 
             CR.Name 
    ORDER BY V.VendorID; 
  END

GO

-- Execute stored procedure

EXEC uspSuppliersByLocation 'California'

VendorID	Vendor	AddressLine1	AddressLine2	City	State	Country
4	Comfort Road Bicycles	7651 Smiling Tree Court	Space 55	Los Angeles	California	United States
8	Continental Pro Cycles	2 Lion Circle	NULL	Long Beach	California	United States
10	Trey Research	1874 Valley Blvd.	NULL	Palo Alto	California	United States
11	Anderson's Custom Bikes	9 Guadalupe Dr.	NULL	Burbank	California	United States
14	Light Speed	298 Sunnybrook Drive	NULL	Spring Valley	California	United States
15	SUPERSALES INC.	9443 Oaxaca	NULL	Lakewood	California	United States

------------

 -- SOLUTION 2 - OPTIMIZE FOR query hint

-- Create stored procedure with OPTIMIZE FOR query hint
-- Washington state is typical based on the distribution of states 
CREATE PROC sprocSuppliersByLocation 
          @StateProvinceName NVARCHAR(50) 
AS 
  BEGIN 
    SELECT V.VendorID, 
           V.Name  AS Vendor, 
           A.AddressLine1, 
           A.AddressLine2, 
           A.City, 
           SP.Name AS State, 
           CR.Name AS Country 
    FROM     Purchasing.Vendor AS V 
             INNER JOIN Purchasing.VendorAddress AS VA 
               ON VA.VendorID = V.VendorID 
             INNER JOIN Person.Address AS A 
               ON A.AddressID = VA.AddressID 
             INNER JOIN Person.StateProvince AS SP 
               ON SP.StateProvinceID = A.StateProvinceID 
             INNER JOIN Person.CountryRegion AS CR 
               ON CR.CountryRegionCode = SP.CountryRegionCode 
    WHERE    SP.Name = @StateProvinceName 
    GROUP BY V.VendorID, 
             V.Name, 
             A.AddressLine1, 
             A.AddressLine2, 
             A.City, 
             SP.Name, 
             CR.Name 
    ORDER BY V.VendorID
  OPTION (OPTIMIZE FOR(@StateProvinceName = 'Washington')); ; 
  END 
GO
-- Execute stored procedure
EXEC sprocSuppliersByLocation 'Colorado'
/* Partial results
Vendor                  AddressLine1            City        State
Green Lake Bike Company 2342 Peachwillow        Denver      Colorado

*/

-- SOLUTION 3 - RECOMPILE each execution

-- Create stored procedure WITH RECOMPILE OPTION
-- Downside: recompile time added to execution time
CREATE PROC sprocSuppliersByLocation 
          @StateProvinceName NVARCHAR(50) 
WITH RECOMPILE
AS 
  BEGIN 
    SELECT V.VendorID, 
           V.Name  AS Vendor, 
           A.AddressLine1, 
           A.AddressLine2, 
           A.City, 
           SP.Name AS State, 
           CR.Name AS Country 
    FROM     Purchasing.Vendor AS V 
             INNER JOIN Purchasing.VendorAddress AS VA 
               ON VA.VendorID = V.VendorID 
             INNER JOIN Person.Address AS A 
               ON A.AddressID = VA.AddressID 
             INNER JOIN Person.StateProvince AS SP 
               ON SP.StateProvinceID = A.StateProvinceID 
             INNER JOIN Person.CountryRegion AS CR 
               ON CR.CountryRegionCode = SP.CountryRegionCode 
    WHERE    SP.Name = @StateProvinceName 
    GROUP BY V.VendorID, 
             V.Name, 
             A.AddressLine1, 
             A.AddressLine2, 
             A.City, 
             SP.Name, 
             CR.Name 
    ORDER BY V.VendorID
  OPTION (OPTIMIZE FOR(@StateProvinceName = 'Washington')); ; 
  END 
GO

------------