RE: Stored Procedure occasionally running 10x slower???

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Cowboy (Gregory A. Beamer) - MVP (NoSpamMgbworld_at_comcast.netNoSpamM)
Date: 08/31/04 

Date: Tue, 31 Aug 2004 12:21:06 -0700

Consider loading the query in question in Query Analyzer with params
assigned. This will give you a decent idea of normal plan. The issue here is
you may have instances where you are losing statistics, or other queries are
altering statistics where they go against this particular query. If so, you
will need to tweak SQL Server a bit to optimize.

The fact that it is happening intermitently suggests that it might be stats
that are the problem. You might also be able to mitigate the issue through an
indexing strategy that essentially throws stats out of the mix. 

-- 
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Cosmin Bicheru" wrote:
> Hello there,
> 
> We are using SQL Server 2000, SP 3, Enterprise Edition as production 
> database server. There is a stored procedure that normally is executed in 
> less than a second:
> - 60% of the times it takes 300ms for execution
> - 30% - between 300ms and 1s 
> - 29% - between 1s and 5s
> 
> But there are some "anomalies" happening when this stored procedure is 
> executed in more than 10s. These anomalies occur completely random but 
> related to the database usage.
> 
> Using SQL Profiler (filtered to trace the stored procedure events), I was 
> able to find out what's causing the problem: there is one SQL query inside 
> that stored procedure running at least 10 times slower. 
> The number of reads and the CPU usage is the same at all times (for the 
> anomalies and normal execution). 
> There weren't any SP:CacheMiss or SP:Recompile events.
> 
> I cannot reproduce the anomaly in our QA environment.
> 
> I tried to "capture" the execution plan for that particular query, but if I 
> select the Show Plan All (or Show Plan Statistics) I will get this event for 
> the whole database activity not only for the stored procedure I'm interested 
> in. I couldn't filter the Show Plan All event only for that particular stored 
> procedure.
> 
> Is there a way to capture the runtime execution plan for ONLY that 
> particular query using SQL Profiler (or any other way)?
> Any other thoughts on why these anomalies keep happening?
> 
> Thank you very much,
> 
> Cosmin

Relevant Pages

  • Re: Differences in execution times for a stored procedure
    ... When a SP is executed for the first time, SQL Server compiles a query plan ... >I am trying to execute a time-consuming stored procedure in my application. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Execution plan different between the two SQL server with the same query
    ... Well that’s another reason to use stored procs:). ... Well you can still use sp_recompile and specify the name of the table or view that the query uses. ... Or look into using a PLAN GUIDE for the current call. ... not a stored procedure, I can't use recomply. ...
    (microsoft.public.sqlserver.server)
  • Re: Store procedure vs Direct statement ???
    ... A stored procedure generates an actual execution plan, but dynamic sql doesn't (at least last time I checked it didn't --- checking from within query analyzer) or at least it is very basic. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Strange behaviour of Cramer query
    ... A stored procedure is executed which executes a couple of queries and ... After cancelling the next execution is quick again. ... The explain plan is 228 lines long so I will not copy in the complete ... query on the one value that is 80% of the data ...
    (comp.databases.oracle.server)
  • RE: Stored Procedure occasionally running 10x slower???
    ... I have load and executed the query in Query Analyser, ... The problem is that I cannot trace the execution plan for the ... >> anomalies and normal execution). ... >> the whole database activity not only for the stored procedure I'm interested ...
    (microsoft.public.sqlserver.programming)