Plan Cache and Parameter sniffing in SQL Server 2008

I have recently encountered   performance problem with one of my production servers due to parameter sniffing in SQL Server 2008. It actually took us quite a bit time to find out the problem and I thought it would be really useful to share it. So what is parameter sniffing?

“Parameter sniffing is the process whereby SQL Server creates an optimal plan for a stored procedure by using the calling parameters that are passed the first time a stored procedure is executed”.  In Other words the plan is never regenerated / optimized after the first execution which forces the optimizer to use the same plan irrespective of the parameters.

 Though there are many ways to identify the parameter sniffing the simplest behavior is there is a considerable difference in the query execution times when executed using SQL Query Analyzer and actual stored procedure call from the application.

I have given some of the commonly used solutions to solve the “Parameter sniffing”

Trace Flag

The simplest of all the solutions is to switch of the Parameter Sniffing using the trace flag 4136. The DBCC syntax is given below

DBCC TRACEON (4136)

Local Variable Substitution

We can force the SQL optimizer to temporarily suspend the Parameter Sniffing by using local variables substitution for the parameters and use only the local variables in the query as shown below

CREATE PROCEDURE pGetPatientName
(
      @P_PatientID VARCHAR(50),
      @P_Name VARCHAR(50)
)
AS
BEGIN

DECLARE @V_PatientID VARCHAR(50)

DECLARE @V_Name VARCHAR(50)

SET @V_Name = @P_Name

SET @V_PatientID = @P_PatientID

SELECT

ForeName,
SurName
FROM
Patient
WHERE PatientID = @V_PatientID
AND ForeName LIKE @V_Name+'%'

END

Recompile & Query Hints

You can also make the optimizer to force for the plan compilation using the options such as

      1.       Execute the query “With Recompile” option

2.       Alter the Procedure / Drop & Re Create Indexes

3.       Specifying Query Hints