Solved

Slow running query that works perfectly after running a sp_recompile

Posted on 2011-09-29
9
352 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 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.

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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