Solved

Slow running query that works perfectly after running a sp_recompile

Posted on 2011-09-29
9
346 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I've deployed the solution and the issue seems to be disappeared. Thanks
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now