Can't use Profiler to get execution plan for a particular query

I am trying to troubleshoot some performance problems and I am trying
to set up Profiler so that I can look at the execution plan of a
particular stored procedure. I am using the ExecutionPlan event class
in the Performance event category. I am also setting up a filter on
ObjectID so that only activity related to this particular store
procedure is returned.
However Profiler is capturing ALL the activity on the server. Filtering
by ObjectID doesn't seem to make any difference even though according
to BOL the ObjectID is a valid data column for the ExecutionPlan event.
Am I missing something or is this a bug in SQL Server/error in the BOL
documentation?
Thanks!Are you saying that you are also capturing System IDs , if you put ObjectID
greater than or equal to 100 - this will disclude System Objects.
This may not be complteley relevant to your problem , but you could also use
the DatabaseID / DatabaseName filter
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
<pshroads@.gmail.com> wrote in message
news:1150241832.487422.17030@.y43g2000cwc.googlegroups.com...
> I am trying to troubleshoot some performance problems and I am trying
> to set up Profiler so that I can look at the execution plan of a
> particular stored procedure. I am using the ExecutionPlan event class
> in the Performance event category. I am also setting up a filter on
> ObjectID so that only activity related to this particular store
> procedure is returned.
> However Profiler is capturing ALL the activity on the server. Filtering
> by ObjectID doesn't seem to make any difference even though according
> to BOL the ObjectID is a valid data column for the ExecutionPlan event.
> Am I missing something or is this a bug in SQL Server/error in the BOL
> documentation?
> Thanks!
>|||The ExecutionPlan event class doesn't populate the object id unfortunately.
So, we cannot filter on it in SQL Server 2000 Profiler. But it does populate
the database id, so you can filter on that to reduce the amount of data
being captured by Profiler. You can also filter on the hostname, if you know
from which machine the request is coming in.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<pshroads@.gmail.com> wrote in message
news:1150241832.487422.17030@.y43g2000cwc.googlegroups.com...
I am trying to troubleshoot some performance problems and I am trying
to set up Profiler so that I can look at the execution plan of a
particular stored procedure. I am using the ExecutionPlan event class
in the Performance event category. I am also setting up a filter on
ObjectID so that only activity related to this particular store
procedure is returned.
However Profiler is capturing ALL the activity on the server. Filtering
by ObjectID doesn't seem to make any difference even though according
to BOL the ObjectID is a valid data column for the ExecutionPlan event.
Am I missing something or is this a bug in SQL Server/error in the BOL
documentation?
Thanks!|||Thanks for your reply. Do you know of any way to get the execution plan
for a query that is already running? Our database is too active to run
a profiler for an extended period of time to catch this intermittent
problem but when it's occuring I'd like to be able to see the query
plan that it's using.
Thanks|||I am not aware of a way of getting execution plan of a query while it is
running, without using Profiler.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<pshroads@.gmail.com> wrote in message
news:1150323592.065493.262700@.f6g2000cwb.googlegroups.com...
Thanks for your reply. Do you know of any way to get the execution plan
for a query that is already running? Our database is too active to run
a profiler for an extended period of time to catch this intermittent
problem but when it's occuring I'd like to be able to see the query
plan that it's using.
Thanks|||Sorry I should have clarified - even Profiler won't give me the
execution plan for a query that is already running when I start the
trace, correct? I would have to already have the trace running first.|||One way of doing this is to inject some sp_trace_create script into the
stored proc from which you want to capture exec plans, using @.@.spid as a
filter. Start the trace at the beginning of the proc & close it at the end.
If the proc is a high repitition proc, you might need some logic to limit
how many instances actually get traced, but this isn't too hard to work
out..
Regards,
Greg Linwood
SQL Server MVP
<pshroads@.gmail.com> wrote in message
news:1150387360.417932.118480@.h76g2000cwa.googlegroups.com...
> Sorry I should have clarified - even Profiler won't give me the
> execution plan for a query that is already running when I start the
> trace, correct? I would have to already have the trace running first.
>|||That's a great idea! I will try that.
Thanks.|||Remember to capture Showplan ALL, so you can pick apart the execution plan
subtree cost & focus your tuning work similarly to how you would if you were
using a graphical plan (based on node %). Post back if you have any
difficulty analysing the text output as that can also be tricky sometimes..
Regards,
Greg Linwood
SQL Server MVP
<pshroads@.gmail.com> wrote in message
news:1150465481.039455.258630@.u72g2000cwu.googlegroups.com...
> That's a great idea! I will try that.
> Thanks.
>