Microsoft SQL Server Community > Tips & Tricks


Categories	Sort by	< PREVIOUS PAGE 1 of 1 NEXT PAGE >

Automating SQL Server Profiler (Very useful)
Author :: Rohit Nayak
Date :: Fri 03/14/2008 @ 04:32

This article details the steps to automate the generation of Sql profiler traces in a Testing environment. The facility is useful when an application is User Acceptance Testing phase where a bunch of test users logon to the Testing site and use the application. The resulting profiler traces can be used for many different things, starting from narrowing down expensive queries to being used as an input for Database Tuning Advisor (DTA)

READ MORE ABOUT THIS POST

Define the Trace

The first step is to define the trace that you want Profiler to run. The way I do this is the define it using Sql profiler’s GUI and then script it out to a file as shown in Listing 1. below

The additional details I add to the file is this line

select @trc_file_name=@trc_file_name+ CAST(year(getdate()) as char(4))+'_'+CAST(month(getdate()) as char(2))+'_'+CAST(day(getdate()) as char(2)) +'_'+ CAST(datepart(hh, getdate()) as char(2))

set @trc_file_name=rtrim(@trc_file_name)

which ensures that a unique timestamp gets associated with each trace that is generated

Another detail that ensures that the trace runs for a 30 minute window is done by

-- Set the trace status to start

exec sp_trace_setstatus @TraceID, 1

WAITFOR DELAY '00:30:00'

exec sp_trace_setstatus @TraceID, 0

exec sp_trace_setstatus @TraceID, 2

Listing 1.

The following screen shots show the process of Job creation and scheduling

Listing 2.

In the Job Step paste the contents of Listing 1 and create a step

Listing 3.

Listing 4.

Listing 5.

Schedule the Job

Next step is to create a schedule to run this job. In my case, most users would be on the system first thing in the morning between 6.30 am and 11.30 am. CST ( to account for all time zones). I scheduled the job to run every hour, and the inbuilt WAITFOR DELAY shall ensure that the trace is captured for 30 minutes each time.

Listing 6.

All and all you end up with a big bunch of trace files that you can feed to DTA or do further analysis on.

Listing 1: Script of trace definition

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

declare @trc_file_name nvarchar(256)

set @maxfilesize = 5

set @trc_file_name='C:\goals_trc\goals_trc_'

select @trc_file_name=@trc_file_name+ CAST(year(getdate()) as char(4))+'_'+CAST(month(getdate()) as char(2))+'_'+CAST(day(getdate()) as char(2)) +'_'+ CAST(datepart(hh, getdate()) as char(2))

set @trc_file_name=rtrim(@trc_file_name)

--select @trc_file_name

exec @rc = sp_trace_create @TraceID output, 0, @trc_file_name, @maxfilesize, NULL

--if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events

declare @on bit

set @on = 1

--exec sp_trace_setevent @TraceID, 97, 1, @on

--exec sp_trace_setevent @TraceID, 97, 3, @on

--exec sp_trace_setevent @TraceID, 97, 10, @on

--exec sp_trace_setevent @TraceID, 97, 13, @on

--exec sp_trace_setevent @TraceID, 97, 16, @on

--exec sp_trace_setevent @TraceID, 97, 17, @on

exec sp_trace_setevent @TraceID, 122, 1, @on

exec sp_trace_setevent @TraceID, 122, 3, @on

exec sp_trace_setevent @TraceID, 122, 10, @on

exec sp_trace_setevent @TraceID, 122, 13, @on

exec sp_trace_setevent @TraceID, 122, 16, @on

exec sp_trace_setevent @TraceID, 122, 17, @on

exec sp_trace_setevent @TraceID, 10, 1, @on

exec sp_trace_setevent @TraceID, 10, 3, @on

exec sp_trace_setevent @TraceID, 10, 10, @on

exec sp_trace_setevent @TraceID, 10, 13, @on

exec sp_trace_setevent @TraceID, 10, 16, @on

exec sp_trace_setevent @TraceID, 10, 17, @on

exec sp_trace_setevent @TraceID, 12, 1, @on

exec sp_trace_setevent @TraceID, 12, 3, @on

exec sp_trace_setevent @TraceID, 12, 10, @on

exec sp_trace_setevent @TraceID, 12, 13, @on

RATING: COMMENTS (0)

< PREVIOUS PAGE

1 of 1

NEXT PAGE >