Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Slow running query that works perfectly after running a sp_recompile

Posted on 2011-09-29
9
Medium Priority
?
364 Views
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?
0
Comment
Question by:ddcdba
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 2000 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.

http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

Greg

0
 
LVL 50

Expert Comment

by:Lowfatspread
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.
0
 

Author Comment

by:ddcdba
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
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 50

Expert Comment

by:Lowfatspread
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.
0
 
LVL 50

Expert Comment

by:Lowfatspread
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...

e.g.
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.


 
0
 
LVL 21

Expert Comment

by:JestersGrind
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.

Greg

0
 

Author Comment

by:ddcdba
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.

0
 
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.
0
 

Author Closing Comment

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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

885 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