Hello,
I was wondering if anyone had some insight into my problem.
I'm running a trace and I want to generate a report to show some simple
statistics about the duration, reads, and writes for the stored procedures
in my database. I'm having trouble parsing the stored procedure name from
the TextData field. The RPC:Completed event gives me everything I need for
the statistics, but it doesn't give me the stored procedure name or object
id. I does, however, give me full command, but I have to parse this with
string manipulation which is not quick. The SP:Completed event gives me the
object id of the stored procedure, but non of the statistics. My trace
files are very large and I'm looking for an easy way to generate some
statistics.
Thanks!
Hi
I assume that you are loading these into a table for analysis, therefore if
you create a new column for the procedure name (and the procedures have a
recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
textdate, then you can use substring and charindex to update the new column.
When you select the data from the table you can restrict the events
returned.
John
"Oliwa" <abc> wrote in message
news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I was wondering if anyone had some insight into my problem.
> I'm running a trace and I want to generate a report to show some simple
> statistics about the duration, reads, and writes for the stored procedures
> in my database. I'm having trouble parsing the stored procedure name from
> the TextData field. The RPC:Completed event gives me everything I need
> for the statistics, but it doesn't give me the stored procedure name or
> object id. I does, however, give me full command, but I have to parse
> this with string manipulation which is not quick. The SP:Completed event
> gives me the object id of the stored procedure, but non of the statistics.
> My trace files are very large and I'm looking for an easy way to generate
> some statistics.
> Thanks!
>
|||Hey John,
That's an idea, but I'm doing something very similar with string
manipulation. I was looking for methods that didn't require string
manipulation.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Hi
> I assume that you are loading these into a table for analysis, therefore
> if you create a new column for the procedure name (and the procedures have
> a recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
> textdate, then you can use substring and charindex to update the new
> column.
> When you select the data from the table you can restrict the events
> returned.
> John
>
> "Oliwa" <abc> wrote in message
> news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
>
|||Hi
If you are only profiling a single user then you can always check your
object ID from the previous SP:Completed event. Even if not a single user,
then you should be able to match SIDs.
When moving the profile into a table, Profiler will generate a RowNumber
Identity column for you.
John
"Oliwa" <abc> wrote in message news:OGJDUB0JFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hey John,
> That's an idea, but I'm doing something very similar with string
> manipulation. I was looking for methods that didn't require string
> manipulation.
> Thanks
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
>
Showing posts with label profiler. Show all posts
Showing posts with label profiler. Show all posts
Saturday, February 25, 2012
Capturing stored proc names in profiler
Hello,
I was wondering if anyone had some insight into my problem.
I'm running a trace and I want to generate a report to show some simple
statistics about the duration, reads, and writes for the stored procedures
in my database. I'm having trouble parsing the stored procedure name from
the TextData field. The RPC:Completed event gives me everything I need for
the statistics, but it doesn't give me the stored procedure name or object
id. I does, however, give me full command, but I have to parse this with
string manipulation which is not quick. The SP:Completed event gives me the
object id of the stored procedure, but non of the statistics. My trace
files are very large and I'm looking for an easy way to generate some
statistics.
Thanks!Hi
I assume that you are loading these into a table for analysis, therefore if
you create a new column for the procedure name (and the procedures have a
recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
textdate, then you can use substring and charindex to update the new column.
When you select the data from the table you can restrict the events
returned.
John
"Oliwa" <abc> wrote in message
news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I was wondering if anyone had some insight into my problem.
> I'm running a trace and I want to generate a report to show some simple
> statistics about the duration, reads, and writes for the stored procedures
> in my database. I'm having trouble parsing the stored procedure name from
> the TextData field. The RPC:Completed event gives me everything I need
> for the statistics, but it doesn't give me the stored procedure name or
> object id. I does, however, give me full command, but I have to parse
> this with string manipulation which is not quick. The SP:Completed event
> gives me the object id of the stored procedure, but non of the statistics.
> My trace files are very large and I'm looking for an easy way to generate
> some statistics.
> Thanks!
>|||Hey John,
That's an idea, but I'm doing something very similar with string
manipulation. I was looking for methods that didn't require string
manipulation.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Hi
> I assume that you are loading these into a table for analysis, therefore
> if you create a new column for the procedure name (and the procedures have
> a recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
> textdate, then you can use substring and charindex to update the new
> column.
> When you select the data from the table you can restrict the events
> returned.
> John
>
> "Oliwa" <abc> wrote in message
> news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
>|||Hi
If you are only profiling a single user then you can always check your
object ID from the previous SP:Completed event. Even if not a single user,
then you should be able to match SIDs.
When moving the profile into a table, Profiler will generate a RowNumber
Identity column for you.
John
"Oliwa" <abc> wrote in message news:OGJDUB0JFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hey John,
> That's an idea, but I'm doing something very similar with string
> manipulation. I was looking for methods that didn't require string
> manipulation.
> Thanks
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
>
I was wondering if anyone had some insight into my problem.
I'm running a trace and I want to generate a report to show some simple
statistics about the duration, reads, and writes for the stored procedures
in my database. I'm having trouble parsing the stored procedure name from
the TextData field. The RPC:Completed event gives me everything I need for
the statistics, but it doesn't give me the stored procedure name or object
id. I does, however, give me full command, but I have to parse this with
string manipulation which is not quick. The SP:Completed event gives me the
object id of the stored procedure, but non of the statistics. My trace
files are very large and I'm looking for an easy way to generate some
statistics.
Thanks!Hi
I assume that you are loading these into a table for analysis, therefore if
you create a new column for the procedure name (and the procedures have a
recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
textdate, then you can use substring and charindex to update the new column.
When you select the data from the table you can restrict the events
returned.
John
"Oliwa" <abc> wrote in message
news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I was wondering if anyone had some insight into my problem.
> I'm running a trace and I want to generate a report to show some simple
> statistics about the duration, reads, and writes for the stored procedures
> in my database. I'm having trouble parsing the stored procedure name from
> the TextData field. The RPC:Completed event gives me everything I need
> for the statistics, but it doesn't give me the stored procedure name or
> object id. I does, however, give me full command, but I have to parse
> this with string manipulation which is not quick. The SP:Completed event
> gives me the object id of the stored procedure, but non of the statistics.
> My trace files are very large and I'm looking for an easy way to generate
> some statistics.
> Thanks!
>|||Hey John,
That's an idea, but I'm doing something very similar with string
manipulation. I was looking for methods that didn't require string
manipulation.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Hi
> I assume that you are loading these into a table for analysis, therefore
> if you create a new column for the procedure name (and the procedures have
> a recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
> textdate, then you can use substring and charindex to update the new
> column.
> When you select the data from the table you can restrict the events
> returned.
> John
>
> "Oliwa" <abc> wrote in message
> news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
>|||Hi
If you are only profiling a single user then you can always check your
object ID from the previous SP:Completed event. Even if not a single user,
then you should be able to match SIDs.
When moving the profile into a table, Profiler will generate a RowNumber
Identity column for you.
John
"Oliwa" <abc> wrote in message news:OGJDUB0JFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hey John,
> That's an idea, but I'm doing something very similar with string
> manipulation. I was looking for methods that didn't require string
> manipulation.
> Thanks
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
>
Capturing stored proc names in profiler
Hello,
I was wondering if anyone had some insight into my problem.
I'm running a trace and I want to generate a report to show some simple
statistics about the duration, reads, and writes for the stored procedures
in my database. I'm having trouble parsing the stored procedure name from
the TextData field. The RPC:Completed event gives me everything I need for
the statistics, but it doesn't give me the stored procedure name or object
id. I does, however, give me full command, but I have to parse this with
string manipulation which is not quick. The SP:Completed event gives me the
object id of the stored procedure, but non of the statistics. My trace
files are very large and I'm looking for an easy way to generate some
statistics.
Thanks!
Hi
I assume that you are loading these into a table for analysis, therefore if
you create a new column for the procedure name (and the procedures have a
recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
textdate, then you can use substring and charindex to update the new column.
When you select the data from the table you can restrict the events
returned.
John
"Oliwa" <abc> wrote in message
news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I was wondering if anyone had some insight into my problem.
> I'm running a trace and I want to generate a report to show some simple
> statistics about the duration, reads, and writes for the stored procedures
> in my database. I'm having trouble parsing the stored procedure name from
> the TextData field. The RPC:Completed event gives me everything I need
> for the statistics, but it doesn't give me the stored procedure name or
> object id. I does, however, give me full command, but I have to parse
> this with string manipulation which is not quick. The SP:Completed event
> gives me the object id of the stored procedure, but non of the statistics.
> My trace files are very large and I'm looking for an easy way to generate
> some statistics.
> Thanks!
>
|||Hey John,
That's an idea, but I'm doing something very similar with string
manipulation. I was looking for methods that didn't require string
manipulation.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Hi
> I assume that you are loading these into a table for analysis, therefore
> if you create a new column for the procedure name (and the procedures have
> a recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
> textdate, then you can use substring and charindex to update the new
> column.
> When you select the data from the table you can restrict the events
> returned.
> John
>
> "Oliwa" <abc> wrote in message
> news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
>
|||Hi
If you are only profiling a single user then you can always check your
object ID from the previous SP:Completed event. Even if not a single user,
then you should be able to match SIDs.
When moving the profile into a table, Profiler will generate a RowNumber
Identity column for you.
John
"Oliwa" <abc> wrote in message news:OGJDUB0JFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hey John,
> That's an idea, but I'm doing something very similar with string
> manipulation. I was looking for methods that didn't require string
> manipulation.
> Thanks
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
>
I was wondering if anyone had some insight into my problem.
I'm running a trace and I want to generate a report to show some simple
statistics about the duration, reads, and writes for the stored procedures
in my database. I'm having trouble parsing the stored procedure name from
the TextData field. The RPC:Completed event gives me everything I need for
the statistics, but it doesn't give me the stored procedure name or object
id. I does, however, give me full command, but I have to parse this with
string manipulation which is not quick. The SP:Completed event gives me the
object id of the stored procedure, but non of the statistics. My trace
files are very large and I'm looking for an easy way to generate some
statistics.
Thanks!
Hi
I assume that you are loading these into a table for analysis, therefore if
you create a new column for the procedure name (and the procedures have a
recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
textdate, then you can use substring and charindex to update the new column.
When you select the data from the table you can restrict the events
returned.
John
"Oliwa" <abc> wrote in message
news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I was wondering if anyone had some insight into my problem.
> I'm running a trace and I want to generate a report to show some simple
> statistics about the duration, reads, and writes for the stored procedures
> in my database. I'm having trouble parsing the stored procedure name from
> the TextData field. The RPC:Completed event gives me everything I need
> for the statistics, but it doesn't give me the stored procedure name or
> object id. I does, however, give me full command, but I have to parse
> this with string manipulation which is not quick. The SP:Completed event
> gives me the object id of the stored procedure, but non of the statistics.
> My trace files are very large and I'm looking for an easy way to generate
> some statistics.
> Thanks!
>
|||Hey John,
That's an idea, but I'm doing something very similar with string
manipulation. I was looking for methods that didn't require string
manipulation.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Hi
> I assume that you are loading these into a table for analysis, therefore
> if you create a new column for the procedure name (and the procedures have
> a recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
> textdate, then you can use substring and charindex to update the new
> column.
> When you select the data from the table you can restrict the events
> returned.
> John
>
> "Oliwa" <abc> wrote in message
> news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
>
|||Hi
If you are only profiling a single user then you can always check your
object ID from the previous SP:Completed event. Even if not a single user,
then you should be able to match SIDs.
When moving the profile into a table, Profiler will generate a RowNumber
Identity column for you.
John
"Oliwa" <abc> wrote in message news:OGJDUB0JFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hey John,
> That's an idea, but I'm doing something very similar with string
> manipulation. I was looking for methods that didn't require string
> manipulation.
> Thanks
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
>
Capturing stored proc names in profiler
Hello,
I was wondering if anyone had some insight into my problem.
I'm running a trace and I want to generate a report to show some simple
statistics about the duration, reads, and writes for the stored procedures
in my database. I'm having trouble parsing the stored procedure name from
the TextData field. The RPC:Completed event gives me everything I need for
the statistics, but it doesn't give me the stored procedure name or object
id. I does, however, give me full command, but I have to parse this with
string manipulation which is not quick. The SP:Completed event gives me the
object id of the stored procedure, but non of the statistics. My trace
files are very large and I'm looking for an easy way to generate some
statistics.
Thanks!Hi
I assume that you are loading these into a table for analysis, therefore if
you create a new column for the procedure name (and the procedures have a
recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
textdate, then you can use substring and charindex to update the new column.
When you select the data from the table you can restrict the events
returned.
John
"Oliwa" <abc> wrote in message
news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I was wondering if anyone had some insight into my problem.
> I'm running a trace and I want to generate a report to show some simple
> statistics about the duration, reads, and writes for the stored procedures
> in my database. I'm having trouble parsing the stored procedure name from
> the TextData field. The RPC:Completed event gives me everything I need
> for the statistics, but it doesn't give me the stored procedure name or
> object id. I does, however, give me full command, but I have to parse
> this with string manipulation which is not quick. The SP:Completed event
> gives me the object id of the stored procedure, but non of the statistics.
> My trace files are very large and I'm looking for an easy way to generate
> some statistics.
> Thanks!
>|||Hey John,
That's an idea, but I'm doing something very similar with string
manipulation. I was looking for methods that didn't require string
manipulation.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Hi
> I assume that you are loading these into a table for analysis, therefore
> if you create a new column for the procedure name (and the procedures have
> a recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
> textdate, then you can use substring and charindex to update the new
> column.
> When you select the data from the table you can restrict the events
> returned.
> John
>
> "Oliwa" <abc> wrote in message
> news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
>|||Hi
If you are only profiling a single user then you can always check your
object ID from the previous SP:Completed event. Even if not a single user,
then you should be able to match SIDs.
When moving the profile into a table, Profiler will generate a RowNumber
Identity column for you.
John
"Oliwa" <abc> wrote in message news:OGJDUB0JFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hey John,
> That's an idea, but I'm doing something very similar with string
> manipulation. I was looking for methods that didn't require string
> manipulation.
> Thanks
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
>
I was wondering if anyone had some insight into my problem.
I'm running a trace and I want to generate a report to show some simple
statistics about the duration, reads, and writes for the stored procedures
in my database. I'm having trouble parsing the stored procedure name from
the TextData field. The RPC:Completed event gives me everything I need for
the statistics, but it doesn't give me the stored procedure name or object
id. I does, however, give me full command, but I have to parse this with
string manipulation which is not quick. The SP:Completed event gives me the
object id of the stored procedure, but non of the statistics. My trace
files are very large and I'm looking for an easy way to generate some
statistics.
Thanks!Hi
I assume that you are loading these into a table for analysis, therefore if
you create a new column for the procedure name (and the procedures have a
recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
textdate, then you can use substring and charindex to update the new column.
When you select the data from the table you can restrict the events
returned.
John
"Oliwa" <abc> wrote in message
news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I was wondering if anyone had some insight into my problem.
> I'm running a trace and I want to generate a report to show some simple
> statistics about the duration, reads, and writes for the stored procedures
> in my database. I'm having trouble parsing the stored procedure name from
> the TextData field. The RPC:Completed event gives me everything I need
> for the statistics, but it doesn't give me the stored procedure name or
> object id. I does, however, give me full command, but I have to parse
> this with string manipulation which is not quick. The SP:Completed event
> gives me the object id of the stored procedure, but non of the statistics.
> My trace files are very large and I'm looking for an easy way to generate
> some statistics.
> Thanks!
>|||Hey John,
That's an idea, but I'm doing something very similar with string
manipulation. I was looking for methods that didn't require string
manipulation.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Hi
> I assume that you are loading these into a table for analysis, therefore
> if you create a new column for the procedure name (and the procedures have
> a recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
> textdate, then you can use substring and charindex to update the new
> column.
> When you select the data from the table you can restrict the events
> returned.
> John
>
> "Oliwa" <abc> wrote in message
> news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
>|||Hi
If you are only profiling a single user then you can always check your
object ID from the previous SP:Completed event. Even if not a single user,
then you should be able to match SIDs.
When moving the profile into a table, Profiler will generate a RowNumber
Identity column for you.
John
"Oliwa" <abc> wrote in message news:OGJDUB0JFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hey John,
> That's an idea, but I'm doing something very similar with string
> manipulation. I was looking for methods that didn't require string
> manipulation.
> Thanks
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
>
Capturing stored proc names in profiler
Hello,
I was wondering if anyone had some insight into my problem.
I'm running a trace and I want to generate a report to show some simple
statistics about the duration, reads, and writes for the stored procedures
in my database. I'm having trouble parsing the stored procedure name from
the TextData field. The RPC:Completed event gives me everything I need for
the statistics, but it doesn't give me the stored procedure name or object
id. I does, however, give me full command, but I have to parse this with
string manipulation which is not quick. The SP:Completed event gives me the
object id of the stored procedure, but non of the statistics. My trace
files are very large and I'm looking for an easy way to generate some
statistics.
Thanks!Hi
I assume that you are loading these into a table for analysis, therefore if
you create a new column for the procedure name (and the procedures have a
recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
textdate, then you can use substring and charindex to update the new column.
When you select the data from the table you can restrict the events
returned.
John
"Oliwa" <abc> wrote in message
news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I was wondering if anyone had some insight into my problem.
> I'm running a trace and I want to generate a report to show some simple
> statistics about the duration, reads, and writes for the stored procedures
> in my database. I'm having trouble parsing the stored procedure name from
> the TextData field. The RPC:Completed event gives me everything I need
> for the statistics, but it doesn't give me the stored procedure name or
> object id. I does, however, give me full command, but I have to parse
> this with string manipulation which is not quick. The SP:Completed event
> gives me the object id of the stored procedure, but non of the statistics.
> My trace files are very large and I'm looking for an easy way to generate
> some statistics.
> Thanks!
>|||Hey John,
That's an idea, but I'm doing something very similar with string
manipulation. I was looking for methods that didn't require string
manipulation.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Hi
> I assume that you are loading these into a table for analysis, therefore
> if you create a new column for the procedure name (and the procedures have
> a recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
> textdate, then you can use substring and charindex to update the new
> column.
> When you select the data from the table you can restrict the events
> returned.
> John
>
> "Oliwa" <abc> wrote in message
> news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
>> Hello,
>> I was wondering if anyone had some insight into my problem.
>> I'm running a trace and I want to generate a report to show some simple
>> statistics about the duration, reads, and writes for the stored
>> procedures in my database. I'm having trouble parsing the stored
>> procedure name from the TextData field. The RPC:Completed event gives me
>> everything I need for the statistics, but it doesn't give me the stored
>> procedure name or object id. I does, however, give me full command, but
>> I have to parse this with string manipulation which is not quick. The
>> SP:Completed event gives me the object id of the stored procedure, but
>> non of the statistics. My trace files are very large and I'm looking for
>> an easy way to generate some statistics.
>> Thanks!
>|||Hi
If you are only profiling a single user then you can always check your
object ID from the previous SP:Completed event. Even if not a single user,
then you should be able to match SIDs.
When moving the profile into a table, Profiler will generate a RowNumber
Identity column for you.
John
"Oliwa" <abc> wrote in message news:OGJDUB0JFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hey John,
> That's an idea, but I'm doing something very similar with string
> manipulation. I was looking for methods that didn't require string
> manipulation.
> Thanks
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> I assume that you are loading these into a table for analysis, therefore
>> if you create a new column for the procedure name (and the procedures
>> have a recognisable prefix e.g 'prc_ ' in the first 8000 characters of
>> the textdate, then you can use substring and charindex to update the new
>> column.
>> When you select the data from the table you can restrict the events
>> returned.
>> John
>>
>> "Oliwa" <abc> wrote in message
>> news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
>> Hello,
>> I was wondering if anyone had some insight into my problem.
>> I'm running a trace and I want to generate a report to show some simple
>> statistics about the duration, reads, and writes for the stored
>> procedures in my database. I'm having trouble parsing the stored
>> procedure name from the TextData field. The RPC:Completed event gives
>> me everything I need for the statistics, but it doesn't give me the
>> stored procedure name or object id. I does, however, give me full
>> command, but I have to parse this with string manipulation which is not
>> quick. The SP:Completed event gives me the object id of the stored
>> procedure, but non of the statistics. My trace files are very large and
>> I'm looking for an easy way to generate some statistics.
>> Thanks!
>>
>
I was wondering if anyone had some insight into my problem.
I'm running a trace and I want to generate a report to show some simple
statistics about the duration, reads, and writes for the stored procedures
in my database. I'm having trouble parsing the stored procedure name from
the TextData field. The RPC:Completed event gives me everything I need for
the statistics, but it doesn't give me the stored procedure name or object
id. I does, however, give me full command, but I have to parse this with
string manipulation which is not quick. The SP:Completed event gives me the
object id of the stored procedure, but non of the statistics. My trace
files are very large and I'm looking for an easy way to generate some
statistics.
Thanks!Hi
I assume that you are loading these into a table for analysis, therefore if
you create a new column for the procedure name (and the procedures have a
recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
textdate, then you can use substring and charindex to update the new column.
When you select the data from the table you can restrict the events
returned.
John
"Oliwa" <abc> wrote in message
news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I was wondering if anyone had some insight into my problem.
> I'm running a trace and I want to generate a report to show some simple
> statistics about the duration, reads, and writes for the stored procedures
> in my database. I'm having trouble parsing the stored procedure name from
> the TextData field. The RPC:Completed event gives me everything I need
> for the statistics, but it doesn't give me the stored procedure name or
> object id. I does, however, give me full command, but I have to parse
> this with string manipulation which is not quick. The SP:Completed event
> gives me the object id of the stored procedure, but non of the statistics.
> My trace files are very large and I'm looking for an easy way to generate
> some statistics.
> Thanks!
>|||Hey John,
That's an idea, but I'm doing something very similar with string
manipulation. I was looking for methods that didn't require string
manipulation.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Hi
> I assume that you are loading these into a table for analysis, therefore
> if you create a new column for the procedure name (and the procedures have
> a recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
> textdate, then you can use substring and charindex to update the new
> column.
> When you select the data from the table you can restrict the events
> returned.
> John
>
> "Oliwa" <abc> wrote in message
> news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
>> Hello,
>> I was wondering if anyone had some insight into my problem.
>> I'm running a trace and I want to generate a report to show some simple
>> statistics about the duration, reads, and writes for the stored
>> procedures in my database. I'm having trouble parsing the stored
>> procedure name from the TextData field. The RPC:Completed event gives me
>> everything I need for the statistics, but it doesn't give me the stored
>> procedure name or object id. I does, however, give me full command, but
>> I have to parse this with string manipulation which is not quick. The
>> SP:Completed event gives me the object id of the stored procedure, but
>> non of the statistics. My trace files are very large and I'm looking for
>> an easy way to generate some statistics.
>> Thanks!
>|||Hi
If you are only profiling a single user then you can always check your
object ID from the previous SP:Completed event. Even if not a single user,
then you should be able to match SIDs.
When moving the profile into a table, Profiler will generate a RowNumber
Identity column for you.
John
"Oliwa" <abc> wrote in message news:OGJDUB0JFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hey John,
> That's an idea, but I'm doing something very similar with string
> manipulation. I was looking for methods that didn't require string
> manipulation.
> Thanks
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> I assume that you are loading these into a table for analysis, therefore
>> if you create a new column for the procedure name (and the procedures
>> have a recognisable prefix e.g 'prc_ ' in the first 8000 characters of
>> the textdate, then you can use substring and charindex to update the new
>> column.
>> When you select the data from the table you can restrict the events
>> returned.
>> John
>>
>> "Oliwa" <abc> wrote in message
>> news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
>> Hello,
>> I was wondering if anyone had some insight into my problem.
>> I'm running a trace and I want to generate a report to show some simple
>> statistics about the duration, reads, and writes for the stored
>> procedures in my database. I'm having trouble parsing the stored
>> procedure name from the TextData field. The RPC:Completed event gives
>> me everything I need for the statistics, but it doesn't give me the
>> stored procedure name or object id. I does, however, give me full
>> command, but I have to parse this with string manipulation which is not
>> quick. The SP:Completed event gives me the object id of the stored
>> procedure, but non of the statistics. My trace files are very large and
>> I'm looking for an easy way to generate some statistics.
>> Thanks!
>>
>
Friday, February 24, 2012
capture showplan in profiler
I want to capture showplan for all the queries running on the server at a
given time. I wanted to know if i run profiler and say give me all those
queries that run for say greater than 5 secs and include the showplan
events, will it give me showplans for all queries or just for those queries
that ran greater than 5 secs which is what I want.
Afraid to turn it on as I know SQL 2000 was not smart enough and just
started spilling all query plans even with the filter.
Also what sql can i run that probably uses DMV to give me all the query
plans for whats currently running on the server i.e. queries that are
currently executing.. which means that every time i run, the results of the
output would be different depending on the query thats active and running at
the time.
ThanksHassan
Somethimg like that
elect
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan'
"Hassan" <hassan@.test.com> wrote in message
news:OAXYOwIJIHA.5684@.TK2MSFTNGP06.phx.gbl...
>I want to capture showplan for all the queries running on the server at a
>given time. I wanted to know if i run profiler and say give me all those
>queries that run for say greater than 5 secs and include the showplan
>events, will it give me showplans for all queries or just for those queries
>that ran greater than 5 secs which is what I want.
> Afraid to turn it on as I know SQL 2000 was not smart enough and just
> started spilling all query plans even with the filter.
> Also what sql can i run that probably uses DMV to give me all the query
> plans for whats currently running on the server i.e. queries that are
> currently executing.. which means that every time i run, the results of
> the output would be different depending on the query thats active and
> running at the time.
> Thanks
>
given time. I wanted to know if i run profiler and say give me all those
queries that run for say greater than 5 secs and include the showplan
events, will it give me showplans for all queries or just for those queries
that ran greater than 5 secs which is what I want.
Afraid to turn it on as I know SQL 2000 was not smart enough and just
started spilling all query plans even with the filter.
Also what sql can i run that probably uses DMV to give me all the query
plans for whats currently running on the server i.e. queries that are
currently executing.. which means that every time i run, the results of the
output would be different depending on the query thats active and running at
the time.
ThanksHassan
Somethimg like that
elect
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan'
"Hassan" <hassan@.test.com> wrote in message
news:OAXYOwIJIHA.5684@.TK2MSFTNGP06.phx.gbl...
>I want to capture showplan for all the queries running on the server at a
>given time. I wanted to know if i run profiler and say give me all those
>queries that run for say greater than 5 secs and include the showplan
>events, will it give me showplans for all queries or just for those queries
>that ran greater than 5 secs which is what I want.
> Afraid to turn it on as I know SQL 2000 was not smart enough and just
> started spilling all query plans even with the filter.
> Also what sql can i run that probably uses DMV to give me all the query
> plans for whats currently running on the server i.e. queries that are
> currently executing.. which means that every time i run, the results of
> the output would be different depending on the query thats active and
> running at the time.
> Thanks
>
capture showplan in profiler
What other event classes or columns do I need to view the showplan all event
class in profiler? The textdata seems to be empty. Using SQL Server 2000Binary data column. There is also a pretty cool way of extracting the
information if you save/load the trace into a table here
http://www.umachandar.com/technical/SQL2000Scripts/UtilitySPs/Main9.htm
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uW4ukVqcDHA.652@.tk2msftngp13.phx.gbl...
What other event classes or columns do I need to view the showplan all event
class in profiler? The textdata seems to be empty. Using SQL Server 2000
class in profiler? The textdata seems to be empty. Using SQL Server 2000Binary data column. There is also a pretty cool way of extracting the
information if you save/load the trace into a table here
http://www.umachandar.com/technical/SQL2000Scripts/UtilitySPs/Main9.htm
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uW4ukVqcDHA.652@.tk2msftngp13.phx.gbl...
What other event classes or columns do I need to view the showplan all event
class in profiler? The textdata seems to be empty. Using SQL Server 2000
capture showplan in profiler
I want to capture showplan for all the queries running on the server at a
given time. I wanted to know if i run profiler and say give me all those
queries that run for say greater than 5 secs and include the showplan
events, will it give me showplans for all queries or just for those queries
that ran greater than 5 secs which is what I want.
Afraid to turn it on as I know SQL 2000 was not smart enough and just
started spilling all query plans even with the filter.
Also what sql can i run that probably uses DMV to give me all the query
plans for whats currently running on the server i.e. queries that are
currently executing.. which means that every time i run, the results of the
output would be different depending on the query thats active and running at
the time.
ThanksHassan
Somethimg like that
elect
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan'
"Hassan" <hassan@.test.com> wrote in message
news:OAXYOwIJIHA.5684@.TK2MSFTNGP06.phx.gbl...
>I want to capture showplan for all the queries running on the server at a
>given time. I wanted to know if i run profiler and say give me all those
>queries that run for say greater than 5 secs and include the showplan
>events, will it give me showplans for all queries or just for those queries
>that ran greater than 5 secs which is what I want.
> Afraid to turn it on as I know SQL 2000 was not smart enough and just
> started spilling all query plans even with the filter.
> Also what sql can i run that probably uses DMV to give me all the query
> plans for whats currently running on the server i.e. queries that are
> currently executing.. which means that every time i run, the results of
> the output would be different depending on the query thats active and
> running at the time.
> Thanks
>
given time. I wanted to know if i run profiler and say give me all those
queries that run for say greater than 5 secs and include the showplan
events, will it give me showplans for all queries or just for those queries
that ran greater than 5 secs which is what I want.
Afraid to turn it on as I know SQL 2000 was not smart enough and just
started spilling all query plans even with the filter.
Also what sql can i run that probably uses DMV to give me all the query
plans for whats currently running on the server i.e. queries that are
currently executing.. which means that every time i run, the results of the
output would be different depending on the query thats active and running at
the time.
ThanksHassan
Somethimg like that
elect
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan'
"Hassan" <hassan@.test.com> wrote in message
news:OAXYOwIJIHA.5684@.TK2MSFTNGP06.phx.gbl...
>I want to capture showplan for all the queries running on the server at a
>given time. I wanted to know if i run profiler and say give me all those
>queries that run for say greater than 5 secs and include the showplan
>events, will it give me showplans for all queries or just for those queries
>that ran greater than 5 secs which is what I want.
> Afraid to turn it on as I know SQL 2000 was not smart enough and just
> started spilling all query plans even with the filter.
> Also what sql can i run that probably uses DMV to give me all the query
> plans for whats currently running on the server i.e. queries that are
> currently executing.. which means that every time i run, the results of
> the output would be different depending on the query thats active and
> running at the time.
> Thanks
>
capture showplan in profiler
I want to capture showplan for all the queries running on the server at a
given time. I wanted to know if i run profiler and say give me all those
queries that run for say greater than 5 secs and include the showplan
events, will it give me showplans for all queries or just for those queries
that ran greater than 5 secs which is what I want.
Afraid to turn it on as I know SQL 2000 was not smart enough and just
started spilling all query plans even with the filter.
Also what sql can i run that probably uses DMV to give me all the query
plans for whats currently running on the server i.e. queries that are
currently executing.. which means that every time i run, the results of the
output would be different depending on the query thats active and running at
the time.
Thanks
Hassan
Somethimg like that
elect
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan'
"Hassan" <hassan@.test.com> wrote in message
news:OAXYOwIJIHA.5684@.TK2MSFTNGP06.phx.gbl...
>I want to capture showplan for all the queries running on the server at a
>given time. I wanted to know if i run profiler and say give me all those
>queries that run for say greater than 5 secs and include the showplan
>events, will it give me showplans for all queries or just for those queries
>that ran greater than 5 secs which is what I want.
> Afraid to turn it on as I know SQL 2000 was not smart enough and just
> started spilling all query plans even with the filter.
> Also what sql can i run that probably uses DMV to give me all the query
> plans for whats currently running on the server i.e. queries that are
> currently executing.. which means that every time i run, the results of
> the output would be different depending on the query thats active and
> running at the time.
> Thanks
>
given time. I wanted to know if i run profiler and say give me all those
queries that run for say greater than 5 secs and include the showplan
events, will it give me showplans for all queries or just for those queries
that ran greater than 5 secs which is what I want.
Afraid to turn it on as I know SQL 2000 was not smart enough and just
started spilling all query plans even with the filter.
Also what sql can i run that probably uses DMV to give me all the query
plans for whats currently running on the server i.e. queries that are
currently executing.. which means that every time i run, the results of the
output would be different depending on the query thats active and running at
the time.
Thanks
Hassan
Somethimg like that
elect
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan'
"Hassan" <hassan@.test.com> wrote in message
news:OAXYOwIJIHA.5684@.TK2MSFTNGP06.phx.gbl...
>I want to capture showplan for all the queries running on the server at a
>given time. I wanted to know if i run profiler and say give me all those
>queries that run for say greater than 5 secs and include the showplan
>events, will it give me showplans for all queries or just for those queries
>that ran greater than 5 secs which is what I want.
> Afraid to turn it on as I know SQL 2000 was not smart enough and just
> started spilling all query plans even with the filter.
> Also what sql can i run that probably uses DMV to give me all the query
> plans for whats currently running on the server i.e. queries that are
> currently executing.. which means that every time i run, the results of
> the output would be different depending on the query thats active and
> running at the time.
> Thanks
>
Sunday, February 19, 2012
Capture IP Address
Does anyone know how to configure profiler or a way to capture an IP address
of a Failed Login Attempt user to a SQL Server 2000?
Thanks in advance.
JohnJohn,
If you are getting hack in attempts, just go to the command prompt and
type
netstat -n
The SQL attacks will be on 1433 and will be listed as time wait (assuming
you check when you are being hacked).
Russ Stevens|||Keeping up to date with database security can get so stressful makes me sick
to gut.
Thank you very much Russ for your helpful post. I really appreciate it.
-J
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:u3mohmk7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> John,
> If you are getting hack in attempts, just go to the command prompt and
> type
> netstat -n
> The SQL attacks will be on 1433 and will be listed as time wait (assuming
> you check when you are being hacked).
> Russ Stevens
>|||Hmmm...I apologize if this is a novice observation but I just noticed
something interesting that I thought I would share...
I have some MS Access front end databases linked to SQL Server 2000
databases as the backend (tables) on our internal network. I create new SQL
logins for every user that requests access to the database and capture every
user action through SQL Profiler. In analyzing the Profiler trace logs I
noticed some Login Failed attempts to our Master database which really
raised my concern. The interesting part is that the Login Failed attempt
kept saying user 'Admin'. I know that we don't have a specific user name to
any of our databases named 'Admin' especially to our Master. I just did a
test and went to open my linked table through MS Access to SQL Server 2000
and that the Profiler logged the event as a Login Failed attempt to the
Master database even though the linked table is to one of my other defined
databases. Then the dsn odbc login pop up comes up and then I log in with
my valid specified user name and password which is not 'Admin' and am able
to login successfully. All of the Login Failed attempts with 'Admin'
throughout my trace logs have a successful login immediately after with a
valid user name. So it appears that when accessing a SQL Server table
through a link from MS Access it by default tries to access the Master
database with the default user name of 'Admin' and then prompts the user for
the valid login name and password. So maybe these weren't hack attempts
(which I am truly hoping)?
Would be interested if someone could confirm to me if this whole process is
accurate?
Thanks in advance.
-J
"John" <IDontLikeSpam@.Nowhere.com> wrote in message
news:%23pr5wns7FHA.3808@.TK2MSFTNGP10.phx.gbl...
> Keeping up to date with database security can get so stressful makes me
> sick to gut.
> Thank you very much Russ for your helpful post. I really appreciate it.
> -J
> "Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
> news:u3mohmk7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>
of a Failed Login Attempt user to a SQL Server 2000?
Thanks in advance.
JohnJohn,
If you are getting hack in attempts, just go to the command prompt and
type
netstat -n
The SQL attacks will be on 1433 and will be listed as time wait (assuming
you check when you are being hacked).
Russ Stevens|||Keeping up to date with database security can get so stressful makes me sick
to gut.
Thank you very much Russ for your helpful post. I really appreciate it.
-J
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:u3mohmk7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> John,
> If you are getting hack in attempts, just go to the command prompt and
> type
> netstat -n
> The SQL attacks will be on 1433 and will be listed as time wait (assuming
> you check when you are being hacked).
> Russ Stevens
>|||Hmmm...I apologize if this is a novice observation but I just noticed
something interesting that I thought I would share...
I have some MS Access front end databases linked to SQL Server 2000
databases as the backend (tables) on our internal network. I create new SQL
logins for every user that requests access to the database and capture every
user action through SQL Profiler. In analyzing the Profiler trace logs I
noticed some Login Failed attempts to our Master database which really
raised my concern. The interesting part is that the Login Failed attempt
kept saying user 'Admin'. I know that we don't have a specific user name to
any of our databases named 'Admin' especially to our Master. I just did a
test and went to open my linked table through MS Access to SQL Server 2000
and that the Profiler logged the event as a Login Failed attempt to the
Master database even though the linked table is to one of my other defined
databases. Then the dsn odbc login pop up comes up and then I log in with
my valid specified user name and password which is not 'Admin' and am able
to login successfully. All of the Login Failed attempts with 'Admin'
throughout my trace logs have a successful login immediately after with a
valid user name. So it appears that when accessing a SQL Server table
through a link from MS Access it by default tries to access the Master
database with the default user name of 'Admin' and then prompts the user for
the valid login name and password. So maybe these weren't hack attempts
(which I am truly hoping)?
Would be interested if someone could confirm to me if this whole process is
accurate?
Thanks in advance.
-J
"John" <IDontLikeSpam@.Nowhere.com> wrote in message
news:%23pr5wns7FHA.3808@.TK2MSFTNGP10.phx.gbl...
> Keeping up to date with database security can get so stressful makes me
> sick to gut.
> Thank you very much Russ for your helpful post. I really appreciate it.
> -J
> "Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
> news:u3mohmk7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>
capture blocking in Profiler
I was just looking in Profier wanting to capture blocking info and I dont
see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
spid keeps changing? It would seem to me that there would be a way to get
all blocking info on every spid involved?
TIA, ChrisR
Hi Chris,
You can use the script in this article to capture blocking information.
http://support.microsoft.com/default...b;en-us;271509
Another way is to use PSSDIAG to capture blocking as well, which is
essentially running the same script.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
ChrisR wrote:
> I was just looking in Profier wanting to capture blocking info and I dont
> see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
> spid keeps changing? It would seem to me that there would be a way to get
> all blocking info on every spid involved?
> TIA, ChrisR
>
|||Thanks Yih.
"Yih-Yoon Lee" <yihyoon@.gmail.com> wrote in message
news:OYuJiXFbFHA.2520@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi Chris,
> You can use the script in this article to capture blocking information.
> http://support.microsoft.com/default...b;en-us;271509
> Another way is to use PSSDIAG to capture blocking as well, which is
> essentially running the same script.
> Yih-Yoon Lee
> E-mail: yihyoon@.gmail.com
> ChrisR wrote:
see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
spid keeps changing? It would seem to me that there would be a way to get
all blocking info on every spid involved?
TIA, ChrisR
Hi Chris,
You can use the script in this article to capture blocking information.
http://support.microsoft.com/default...b;en-us;271509
Another way is to use PSSDIAG to capture blocking as well, which is
essentially running the same script.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
ChrisR wrote:
> I was just looking in Profier wanting to capture blocking info and I dont
> see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
> spid keeps changing? It would seem to me that there would be a way to get
> all blocking info on every spid involved?
> TIA, ChrisR
>
|||Thanks Yih.
"Yih-Yoon Lee" <yihyoon@.gmail.com> wrote in message
news:OYuJiXFbFHA.2520@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi Chris,
> You can use the script in this article to capture blocking information.
> http://support.microsoft.com/default...b;en-us;271509
> Another way is to use PSSDIAG to capture blocking as well, which is
> essentially running the same script.
> Yih-Yoon Lee
> E-mail: yihyoon@.gmail.com
> ChrisR wrote:
capture blocking in Profiler
I was just looking in Profier wanting to capture blocking info and I dont
see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
spid keeps changing? It would seem to me that there would be a way to get
all blocking info on every spid involved?
TIA, ChrisRHi Chris,
You can use the script in this article to capture blocking information.
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509
Another way is to use PSSDIAG to capture blocking as well, which is
essentially running the same script.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
ChrisR wrote:
> I was just looking in Profier wanting to capture blocking info and I dont
> see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
> spid keeps changing? It would seem to me that there would be a way to get
> all blocking info on every spid involved?
> TIA, ChrisR
>|||Thanks Yih.
"Yih-Yoon Lee" <yihyoon@.gmail.com> wrote in message
news:OYuJiXFbFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi Chris,
> You can use the script in this article to capture blocking information.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;271509
> Another way is to use PSSDIAG to capture blocking as well, which is
> essentially running the same script.
> Yih-Yoon Lee
> E-mail: yihyoon@.gmail.com
> ChrisR wrote:
>> I was just looking in Profier wanting to capture blocking info and I dont
>> see it. sp_who and dbcc inputbuffer are good but, what if the the
>> blocking spid keeps changing? It would seem to me that there would be a
>> way to get all blocking info on every spid involved?
>> TIA, ChrisR
see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
spid keeps changing? It would seem to me that there would be a way to get
all blocking info on every spid involved?
TIA, ChrisRHi Chris,
You can use the script in this article to capture blocking information.
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509
Another way is to use PSSDIAG to capture blocking as well, which is
essentially running the same script.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
ChrisR wrote:
> I was just looking in Profier wanting to capture blocking info and I dont
> see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
> spid keeps changing? It would seem to me that there would be a way to get
> all blocking info on every spid involved?
> TIA, ChrisR
>|||Thanks Yih.
"Yih-Yoon Lee" <yihyoon@.gmail.com> wrote in message
news:OYuJiXFbFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi Chris,
> You can use the script in this article to capture blocking information.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;271509
> Another way is to use PSSDIAG to capture blocking as well, which is
> essentially running the same script.
> Yih-Yoon Lee
> E-mail: yihyoon@.gmail.com
> ChrisR wrote:
>> I was just looking in Profier wanting to capture blocking info and I dont
>> see it. sp_who and dbcc inputbuffer are good but, what if the the
>> blocking spid keeps changing? It would seem to me that there would be a
>> way to get all blocking info on every spid involved?
>> TIA, ChrisR
capture blocking in Profiler
I was just looking in Profier wanting to capture blocking info and I dont
see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
spid keeps changing? It would seem to me that there would be a way to get
all blocking info on every spid involved?
TIA, ChrisRHi Chris,
You can use the script in this article to capture blocking information.
http://support.microsoft.com/defaul...kb;en-us;271509
Another way is to use PSSDIAG to capture blocking as well, which is
essentially running the same script.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
ChrisR wrote:
> I was just looking in Profier wanting to capture blocking info and I dont
> see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
> spid keeps changing? It would seem to me that there would be a way to get
> all blocking info on every spid involved?
> TIA, ChrisR
>|||Thanks Yih.
"Yih-Yoon Lee" <yihyoon@.gmail.com> wrote in message
news:OYuJiXFbFHA.2520@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi Chris,
> You can use the script in this article to capture blocking information.
> http://support.microsoft.com/defaul...kb;en-us;271509
> Another way is to use PSSDIAG to capture blocking as well, which is
> essentially running the same script.
> Yih-Yoon Lee
> E-mail: yihyoon@.gmail.com
> ChrisR wrote:
see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
spid keeps changing? It would seem to me that there would be a way to get
all blocking info on every spid involved?
TIA, ChrisRHi Chris,
You can use the script in this article to capture blocking information.
http://support.microsoft.com/defaul...kb;en-us;271509
Another way is to use PSSDIAG to capture blocking as well, which is
essentially running the same script.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
ChrisR wrote:
> I was just looking in Profier wanting to capture blocking info and I dont
> see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
> spid keeps changing? It would seem to me that there would be a way to get
> all blocking info on every spid involved?
> TIA, ChrisR
>|||Thanks Yih.
"Yih-Yoon Lee" <yihyoon@.gmail.com> wrote in message
news:OYuJiXFbFHA.2520@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi Chris,
> You can use the script in this article to capture blocking information.
> http://support.microsoft.com/defaul...kb;en-us;271509
> Another way is to use PSSDIAG to capture blocking as well, which is
> essentially running the same script.
> Yih-Yoon Lee
> E-mail: yihyoon@.gmail.com
> ChrisR wrote:
Tuesday, February 14, 2012
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!|||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|||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|||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.
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!|||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|||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|||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.
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.
>
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.
>
Subscribe to:
Posts (Atom)