performance trace

Does anyone know how to track the actual execution plan the server uses? I
have one query designed for a frequently run report that normally takes about
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CPU
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture the
execution plan at runtime, I might be able to explain why the performance can
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
--
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:
> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorrow.
> If you need to specify a hint it is possible that the query can be rewritten
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> > Does anyone know how to track the actual execution plan the server uses? I
> > have one query designed for a frequently run report that normally takes
> > about
> > 3 seconds to return. However, the Profiler logs show me that in certain
> > cases, it took more than 3 minutes to return and it cost a huge amount of
> > CPU
> > time. However, when I test it manually, it always returns in less than 3
> > seconds and nothing wrong in the execution plan. I am wondering that at
> > runtime, the server might decide to choose a different plan, especially
> > when
> > the report is called by multiple users at the same time. If I can capture
> > the
> > execution plan at runtime, I might be able to explain why the performance
> > can
> > vary so dramatically, and find a way to tune it.
> >
> > Another thing, not sure if it is related or not. I am using table hint to
> > direct the server to use one specific index in order to speed it up.
> > Because
> > I noticed if I don't, server sometimes choose a cluster index scan which
> > is
> > too slow and costly. So, if I use table hint in the query and several
> > instances of it are called concurrently, is there any performance
> > concerns?
> >
> >
>
>|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com