Why straight SQL query run faster than stored procedure?

Question

I am facing-out a very surprising problem in SQL Server.

The problem is when the query run in SSMS window it will return result in ~1 sec and when the query run through a stored procedure it will execute in ~57 sec.

For example :

DECLARE @ip_RequestId As Int
SET @ip_RequestId = 3287
SELECT 
 Tbl1.SurveyResponseId, 
 Tbl1.ReportLabelID, 
 Tbl1.EntityId AS FeedbackByEntityId, 
 (SELECT Candidate + ' ' + LastName 
 FROM Tbl2 
 WHERE Tbl2.ProposalId = Tbl1.EntityId) AS FeedbackByName, 
 (SELECT ISNULL((SELECT MRReportTitle FROM Tbl3 WHERE MultiRaterId = @ip_RequestId), '')) As ReportTitle
FROM Tbl1

When I run the above query in SSMS as a query, it execute in ~1 seconds.

But after creating the stored procedure:

CREATE PROCEDURE [dbo].[spGetResponse] 
 @ip_RequestId As Int 
AS 
BEGIN
 SELECT 
 Tbl1.SurveyResponseId, 
 Tbl1.ReportLabelID, 
 Tbl1.EntityId AS FeedbackByEntityId, 
 (SELECT Candidate + ' ' + LastName 
 FROM Tbl2 
 WHERE Tbl2.ProposalId = Tbl1.EntityId) AS FeedbackByName, 
 (SELECT ISNULL((SELECT MRReportTitle FROM Tbl3 
 WHERE MultiRaterId = @ip_RequestId), '')) As ReportTitle
 FROM Tbl1
END 

When I execute the above stored procedure with same parameter value (i.e @ip_RequestId = 3287) in SSMS window (exec spGetResponse 3287), it executes in ~57 seconds.

After some googling, I found it's because of "parameter sniffing". But I am not really understand the "parameter sniffing".

**Updated: One more thing **

When the Sp run with search query, i will also fast for me. Means if I convert the SP following format, this will also return result in ~1 sec:

CREATE PROCEDURE [dbo].[spGetResponse] 
 @ip_RequestId As Int 
AS 
BEGIN 
 DECLARE @strQuery As VARCHAR(MAX)
 SET @strQuery = '(SELECT Tbl1.SurveyResponseId, Tbl1.ReportLabelID, Tbl1.EntityId AS FeedbackByEntityId, (SELECT Candidate + '' '' + LastName FROM Tbl2 WHERE Tbl2.ProposalId = Tbl1.EntityId) AS FeedbackByName, (SELECT ISNULL((SELECT MRReportTitle FROM Tbl3 WHERE MultiRaterId = ' + @ip_RequestId + '), '''')) As ReportTitle FROM Tbl1)'
 EXECUTE (@strQuery) 
END

When I execute the above stored procedure with same parameter value (i.e @ip_RequestId = 3287) in SSMS window (exec spGetResponse 3287), it executes in ~1 seconds.

Is there any solution exist to overcome the "parameter sniffing" problem?

• Mihai: Use OPTION(RECOMPILE) to generate a new execution plan each time it is run.On average slower but the huge difference should disappear.
• user507: Multiple questions on this forum about this topic and tons of good articles can be found through Google. one question that has some good info.
• Kris Gruttemeyer: Good points above, is it ONLY the first run of the SP that is slow?
• Kin Shah: @IshanJain you should thoroughly read Erland Sommarskog's article Slow in the Application, Fast in SSMS?
• Ishan Jain: No, every times when the SP execute. Although with same parameter value.

Answer 1

Assign the value of parameter to the local variable in the procedure then used that variable.

• Tara Kizer: That is a very old way of fixing a parameter sniffing issue. That's what we did in 2005 or older as we didn't have many options back then. But in 2008 or newer, we have a lot more options, such as plan guides, OPTION (RECOMPILE), OPTIMIZE FOR value, etc. I would not be recommending using a local variable to work around a "bad" parameter sniffing issue these days.
• Shane Estelle: It may be old, but I don't see anything wrong with it... although OPTIMIZE FOR is probably cleaner. I can't imagine forcing a recompile of the procedure on every execution is a better option.

Answer 2

You can use the hint WITH (RECOMPILE) while creating / executing your stored procedure.

CREATE PROCEDURE sp_GetRespond
WITH RECOMPILE
OR
EXECUTE sp_GetRespond WITH RECOMPILE

Every times, the sp is run SQL Engine re-compiles it and generate the most optimal execution plan.

• Kris Gruttemeyer: Word of caution here as many people get in the habit of just adding this to slow running SPs. When using RECOMPILE hints, you are telling the engine to NOT store the plan in cache. It will be recompiled every time. 'recompile' makes it sound like it just stored the new plan i cache each time, it actually doesn't. I recommend adding OPTION RECOMPILE to the queries within the SP instead of a global SP change.
• Dung Dinh: I agree with you that we don't use the hint global RECOMPILE in all cases. OPTION RECOMPILE is the one of solutions to avoid parameter sniffing. Especially, we need to get a large amount of data.
• Ishan Jain: I am not directly execute SP in SSMS. The Sp executed by code (C#). How could i execute the SP with RECOMPILE option through C# code?
• Dung Dinh: When you create store procedure, add OPTION (RECOMPILE) as @Kris recommended or you use CREATE PROCEDURE sp_GetRespond WITH RECOMPILE. After that, you can call the sp normally, you don't have to add more code C# any more.

Answer 3

I had the similar issue. Added the OPTIMIZE FOR UNKNOWN at the end of the SP to resolve the problem.