DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
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

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

Related articles:

http://www.sqlusa.com/articles/query-optimization/

T-SQL Best Practices Parameter Sniffing

Parameter Sniffing

Slow in the Application, Fast in SSMS? Understanding Performance Mysteries

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE