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
___________________________________


<pshro***@gmail.com> wrote in message
Show quoteHide quote

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.
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.
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

<pshro***@gmail.com> wrote in message
Show quoteHide quote

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

<pshro***@gmail.com> wrote in message
Show quoteHide quote

Font in SQL pane in 2005
I need to extract .pdf/.doc/.xls documents from an sql 2000 table.
Copy Database Wizard - what would you improve?
Transact Log truncate
Edit/Modify Stored Procedure in Management Console 2005