469,076 Members | 1,357 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,076 developers. It's quick & easy.

Profiler Performance trace template

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

Jul 20 '05 #1
3 5799
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
Jul 20 '05 #2
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

Jul 20 '05 #3
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

Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by jw56578 | last post: by
8 posts views Thread by patrickshroads | last post: by
2 posts views Thread by Celine & Dave | last post: by
8 posts views Thread by Bryan | last post: by
1 post views Thread by Thyagu | last post: by
1 post views Thread by ofirmgr | last post: by
3 posts views Thread by Sam Samson | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.