Re: stored procedure occassionally running 10x slower???

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

From: oj (nospam_ojngo_at_home.com)
Date: 08/31/04 

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

You want to take a look at this article:
http://support.microsoft.com/?id=243586

"Cosmin Bicheru" <CosminBicheru@discussions.microsoft.com> wrote in message
news:FA55C80A-C8A9-44BD-9B9F-A88ADCC444F9@microsoft.com...
> 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

  • Session_End event, System.NullReferenceException
    ... database table that is used to log user sessions. ... The stored procedure executes successfully, and I can see the updated data in ... an exception is being generated by the code that I'ved added to ... through an execution of the code that led to the exception. ...
    (microsoft.public.dotnet.framework.aspnet)
  • stored procedure occassionally running 10x slower???
    ... But there are some "anomalies" happening when this stored procedure is ... Using SQL Profiler, ... anomalies and normal execution). ... I tried to "capture" the execution plan for that particular query, ...
    (microsoft.public.sqlserver.programming)
  • Stored Procedure occasionally running 10x slower???
    ... But there are some "anomalies" happening when this stored procedure is ... Using SQL Profiler, ... anomalies and normal execution). ... I tried to "capture" the execution plan for that particular query, ...
    (microsoft.public.sqlserver.programming)
  • 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)
  • Strange behaviour of Cramer query
    ... Application: Cramer see http://en.wikipedia.org/wiki/Cramer_Systems ... 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 ...
    (comp.databases.oracle.server)