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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
why sql server only update some statistics in the database ? 3 40
tempdb log keep growing 7 43
asp classic find word in string and get its value 7 32
ASP get fieds value 2 20
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

685 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