SEO

#1 (**permalink**) 02-28-2008, 08:03 PM

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

#2 (**permalink**) 02-28-2008, 08:03 PM

Robert (stop.spam@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, sommar@algonet.se

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

#3 (**permalink**) 02-28-2008, 08:03 PM

Thanx Erland for your suggestions.

"Erland Sommarskog" <sommar@algonet.se> wrote in message
news:Xns9494F0C2C2386Yazorman@127.0.0.1...
> Robert (stop.spam@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, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

#4 (**permalink**) 02-28-2008, 08:03 PM

Thanx Erland for your suggestions.

"Erland Sommarskog" <sommar@algonet.se> wrote in message
news:Xns9494F0C2C2386Yazorman@127.0.0.1...
> Robert (stop.spam@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, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Profiler Performance trace template