Slow running query that works perfectly after running a sp_recompile

Posted on 2011-09-29
Last Modified: 2012-05-12
We have a stored procedure that returns a result set of around 200 rows, it works pretty well most of the time, but for some reason the Engine is not updating the execution plan of this procedure or is changing it because it chooses to do full index scan instead of an index seek. Causing that query that executes in 30 ms takes around 5 minutes to complete.

The USP is being called from and ASP

The auto update statistics and the auto update statistics asynchronously are set to ON. The index fragmentation is always less than 5%.

Have you seen something like this before?
Question by:ddcdba
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
LVL 21

Accepted Solution

JestersGrind earned 500 total points
ID: 36816004
If your input parameters vary widely, it could be a parameter sniffing issue.  In your stored procedure, create duplicate parameters for all of your input parameters, set the values to the input parameter values and replace all references to the input parameters to the new parameters.  Here is an article that explains it in much greater detail.


LVL 50

Expert Comment

ID: 36816071
i agree with jester , parameter sniffing is the likely cause...

depending on the actual frequency of the problem you could add the with recompile option into the store procedure to cause it to always compile itself to optimise its plan/usage.

post your procedure code and well see if we can make any further suggestions.

Author Comment

ID: 36816662
Hi Sage

Thanks for your quick answer, nothing has sound more likely to what we experience with this procedure, I have a question, in your article when you try to disable the parameter sniffing you create a second set of parameters to store the input, you left the WITH RECOMPILE clause in the procedure, if I leave this clause in our code it will take around 5000 ms to execute every call, without the recompile clause it takes around 30 ms, this procedure is called very often. What would be a better approach to avoid the extra load to the server and to improve the time used for the execution
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

LVL 50

Expert Comment

ID: 36816752
remove the with recompile from the stored procedure,,,

and possibly schedule a recompile for it every 15-30 minutes...
or monitor it in the client app(?) and execute it next time with  "external" with recompile option  if performance degrades
to a degree...

the auto scheduled recompile may be the "better" approach.
LVL 50

Expert Comment

ID: 36816797
sql 2008 tries to improve its detection of invalid plans and perform "on the fly" re-compilation of statements...

another strategy is perhaps to write a couple of copies of the procedure ... if you can identify the patterns of
parameter which cause problems and invoke specific stored procedures for each pattern...

existing proc PROCA

make 2 copies call then ProcA_1 and ProcA_2

write a new shell proc call it ProcA with the same parameters

the job of the new procA is to identify between the input parameters "patterns" can call either ProcA_1 or ProcA_2
as seems appropiate....

however at this stage you probably get into the realm of re-writting the base procedure to more specifically address the
patern of parameter usage which cause the problem...
e.g. you have 1 select which either results in a direct select by a reference from the table or could perform a name like search... in general it would be better to have this as two separate select statements rather than a single combined one.

LVL 21

Expert Comment

ID: 36817006
If you are doing parameter substitution, you shouldn't need WITH RECOMPILE.  Typically one or the other is done.  Not both.  I have had better performance from disabling parameter sniffing through parameter substitution than from recompiling the stored procedure every time.



Author Comment

ID: 36817604
Thanks for all your suggestions, We're actually trying to remove a job that recompile the procedure every 15 minutes, and that is causing some blocking issues in the database.

LVL 75

Expert Comment

by:Anthony Perkins
ID: 36895263
Yes I agree, this definitely smells like parameter sniffing, if the Stored Procedure is not overly complicated, consider posting it here.  Perhaps a re-write will be a better approach.

Author Closing Comment

ID: 36903808
I've deployed the solution and the issue seems to be disappeared. Thanks

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Weighted Randomizing 6 38
ASP Classic, check if Querystring is UCase or LCase 10 35
Using this function 4 54
Display field if column exists 7 58
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: and…
In an interesting question ( here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question