1

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?

  • Use OPTION(RECOMPILE) to generate a new execution plan each time it is run.On average slower but the huge difference should disappear. – Mihai Aug 18 '14 at 6:04
  • 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. – user507 Aug 18 '14 at 6:48
  • Good points above, is it ONLY the first run of the SP that is slow? – Kris Gruttemeyer Aug 18 '14 at 13:52
  • 3
    @IshanJain you should thoroughly read Erland Sommarskog's article Slow in the Application, Fast in SSMS? – Kin Shah Aug 18 '14 at 13:57
  • No, every times when the SP execute. Although with same parameter value. – Ishan Jain Aug 18 '14 at 13:57
2

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

| improve this answer | |
  • 2
    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. – Tara Kizer Aug 29 '16 at 16:21
  • 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. – Shane Estelle Aug 29 '16 at 17:56
1

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.

| improve this answer | |
  • 1
    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. – Kris Gruttemeyer Aug 18 '14 at 13:51
  • 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. – Dung Dinh Aug 18 '14 at 13:59
  • 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? – Ishan Jain Aug 18 '14 at 14:17
  • 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. – Dung Dinh Aug 19 '14 at 1:54
-1

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.