Profiler Performance trace template

[Robert]

Greetings,

I have been attempting to develop a useful and functional template for
database tracing/profiling that will enable me to collect metrics for
performance tuning. The database is used as an OLTP database as well as
running reports. Below is a list of my trace properties and data columns.
I would be interested to see other examples and strategies for the Profiler.
thanx

Performance
Execution plan
Security
Audit Login
Audit Logout
Sessions
Existing Connection
Stored Procedures
RPC: Completed
TSQL
SQL:Batch completed
DATA COLUMNS
Event class
textdata
application name
NTUsername
LoginNames
CPU
read
write
duration
client proc id
SPID
Starttime

[Erland Sommarskog]

Robert (st*******@boeing.com) writes:

I have been attempting to develop a useful and functional template
for
database tracing/profiling that will enable me to collect metrics for
performance tuning. The database is used as an OLTP database as well as
running reports. Below is a list of my trace properties and data
columns. I would be interested to see other examples and strategies for
the Profiler.

There too many ways to profile with Profiler to list them all. I do
have a performance template, and is quite difficult from yours. I have
all SP events, but SP:ExecContextHit and SP:StmtStarting and SP:SPStarting.
The only other event is SQL:BatchCompleted. As for the columns I
have EventData, TextData, CPU, Read, Write, Duration, Spid, StartTime
and EndTime. I typically add a filter for a certain spid, since I use
this template for tracking troublesome function.

But as the trace goes on, I might add Execution Plan, together with
some filter, to only give the interesting plans. I might add EventSubClass
to find out abour reasons for recompiles. I might add a Duration
filter to remove 0-ms events. (In which case also CacheMiss and other
timeless events might be dropped.)
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Robert]

Thanx Erland for your suggestions.
"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...

Robert (st*******@boeing.com) writes:

I have been attempting to develop a useful and functional template
for
database tracing/profiling that will enable me to collect metrics for
performance tuning. The database is used as an OLTP database as well as
running reports. Below is a list of my trace properties and data
columns. I would be interested to see other examples and strategies for
the Profiler.
There too many ways to profile with Profiler to list them all. I do
have a performance template, and is quite difficult from yours. I have
all SP events, but SP:ExecContextHit and SP:StmtStarting and

SP:SPStarting. The only other event is SQL:BatchCompleted. As for the columns I
have EventData, TextData, CPU, Read, Write, Duration, Spid, StartTime
and EndTime. I typically add a filter for a certain spid, since I use
this template for tracking troublesome function.

But as the trace goes on, I might add Execution Plan, together with
some filter, to only give the interesting plans. I might add EventSubClass
to find out abour reasons for recompiles. I might add a Duration
filter to remove 0-ms events. (In which case also CacheMiss and other
timeless events might be dropped.)
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Robert]

Thanx Erland for your suggestions.
"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...

Robert (st*******@boeing.com) writes:

I have been attempting to develop a useful and functional template
for
database tracing/profiling that will enable me to collect metrics for
performance tuning. The database is used as an OLTP database as well as
running reports. Below is a list of my trace properties and data
columns. I would be interested to see other examples and strategies for
the Profiler.
There too many ways to profile with Profiler to list them all. I do
have a performance template, and is quite difficult from yours. I have
all SP events, but SP:ExecContextHit and SP:StmtStarting and

SP:SPStarting. The only other event is SQL:BatchCompleted. As for the columns I
have EventData, TextData, CPU, Read, Write, Duration, Spid, StartTime
and EndTime. I typically add a filter for a certain spid, since I use
this template for tracking troublesome function.

But as the trace goes on, I might add Execution Plan, together with
some filter, to only give the interesting plans. I might add EventSubClass
to find out abour reasons for recompiles. I might add a Duration
filter to remove 0-ms events. (In which case also CacheMiss and other
timeless events might be dropped.)
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp