Automate SQL Server Profiler Trace Execution

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 12-01-2011

0

SQL profiler is an excellent tool for T-SQL query performance optimization. SQL Server Production DBA’s use Profiler to scan what T-SQL queries are getting executed inside SQL Server and what are the queries that can be filtered for further fine-tuning. SQL profiler has inbuilt scheduler for stopping a trace, but Trace Properties does not provide functionality to automatically schedule a trace and keep it running for a desired frequency.

image

I happen to talk to some proactive production and application DBA’s those are running profiler trace on weekly or even daily basis for a short term with various filters. Running profiler till now was a manual & time consuming, forget ful task but not anymore. Here is the script that you can call from SQL Server Agent job and run profiler on your desired timings and filters. It is created using ‘Script Trace Definition’ from File-Export Menu, I have added profiler options to run it for defined duration, file location and stopping options.

image

Script 1: For tracing SQL Server Deadlock information. You may create a SQL agent job to run it for 24*7 on daily basis.

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
declare @filecount int
-- Set the time limit for trace, 10 hours default in this script
declare @HourstoRunTrace smallint
set @HourstoRunTrace=1439
set @DateTime = DATEADD(mi,@HourstoRunTrace,getdate())
set @maxfilesize = 2048
set @filecount=10
-- Set the file path
declare @filename nvarchar(256)
-- Change the file location from C:\temp to any desired
set @filename= 'C:\temp\Prolifer-DeadLock_Trace-'+convert(varchar, getdate(), 5)+
'-'+ replace(convert(varchar, getdate(),108),':','')
print 'Trace File is being saved at : '+@filename
exec @rc = sp_trace_create @TraceID output, 2, @filename, @maxfilesize, @DateTime, @filecount
if (@rc != 0) goto error
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 148, 11, @on
exec sp_trace_setevent @TraceID, 148, 51, @on
exec sp_trace_setevent @TraceID, 148, 4, @on
exec sp_trace_setevent @TraceID, 148, 12, @on
exec sp_trace_setevent @TraceID, 148, 14, @on
exec sp_trace_setevent @TraceID, 148, 26, @on
exec sp_trace_setevent @TraceID, 148, 60, @on
exec sp_trace_setevent @TraceID, 148, 64, @on
exec sp_trace_setevent @TraceID, 148, 1, @on
exec sp_trace_setevent @TraceID, 148, 41, @on
exec sp_trace_setevent @TraceID, 25, 7, @on
exec sp_trace_setevent @TraceID, 25, 15, @on
exec sp_trace_setevent @TraceID, 25, 55, @on
exec sp_trace_setevent @TraceID, 25, 8, @on
exec sp_trace_setevent @TraceID, 25, 32, @on
exec sp_trace_setevent @TraceID, 25, 56, @on
exec sp_trace_setevent @TraceID, 25, 64, @on
exec sp_trace_setevent @TraceID, 25, 1, @on
exec sp_trace_setevent @TraceID, 25, 9, @on
exec sp_trace_setevent @TraceID, 25, 25, @on
exec sp_trace_setevent @TraceID, 25, 41, @on
exec sp_trace_setevent @TraceID, 25, 49, @on
exec sp_trace_setevent @TraceID, 25, 57, @on
exec sp_trace_setevent @TraceID, 25, 2, @on
exec sp_trace_setevent @TraceID, 25, 10, @on
exec sp_trace_setevent @TraceID, 25, 26, @on
exec sp_trace_setevent @TraceID, 25, 58, @on
exec sp_trace_setevent @TraceID, 25, 3, @on
exec sp_trace_setevent @TraceID, 25, 11, @on
exec sp_trace_setevent @TraceID, 25, 35, @on
exec sp_trace_setevent @TraceID, 25, 51, @on
exec sp_trace_setevent @TraceID, 25, 4, @on
exec sp_trace_setevent @TraceID, 25, 12, @on
exec sp_trace_setevent @TraceID, 25, 52, @on
exec sp_trace_setevent @TraceID, 25, 60, @on
exec sp_trace_setevent @TraceID, 25, 13, @on
exec sp_trace_setevent @TraceID, 25, 6, @on
exec sp_trace_setevent @TraceID, 25, 14, @on
exec sp_trace_setevent @TraceID, 25, 22, @on
exec sp_trace_setevent @TraceID, 59, 55, @on
exec sp_trace_setevent @TraceID, 59, 32, @on
exec sp_trace_setevent @TraceID, 59, 56, @on
exec sp_trace_setevent @TraceID, 59, 64, @on
exec sp_trace_setevent @TraceID, 59, 1, @on
exec sp_trace_setevent @TraceID, 59, 21, @on
exec sp_trace_setevent @TraceID, 59, 25, @on
exec sp_trace_setevent @TraceID, 59, 41, @on
exec sp_trace_setevent @TraceID, 59, 49, @on
exec sp_trace_setevent @TraceID, 59, 57, @on
exec sp_trace_setevent @TraceID, 59, 2, @on
exec sp_trace_setevent @TraceID, 59, 14, @on
exec sp_trace_setevent @TraceID, 59, 22, @on
exec sp_trace_setevent @TraceID, 59, 26, @on
exec sp_trace_setevent @TraceID, 59, 58, @on
exec sp_trace_setevent @TraceID, 59, 3, @on
exec sp_trace_setevent @TraceID, 59, 35, @on
exec sp_trace_setevent @TraceID, 59, 51, @on
exec sp_trace_setevent @TraceID, 59, 4, @on
exec sp_trace_setevent @TraceID, 59, 12, @on
exec sp_trace_setevent @TraceID, 59, 52, @on
exec sp_trace_setevent @TraceID, 59, 60, @on
-- Set the Filters
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler%'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
print 'TraceID started: '+ convert (varchar, @TraceID)
Print 'Waiting for Trace to complete (for specified duration) :'+ convert (varchar,@DateTime)
waitfor time @DateTime
exec sp_trace_setstatus @TraceID, 0
exec sp_trace_setstatus @TraceID, 2
Print 'Trace closed'
error:
if @rc<>0
begin
Print 'Error in the Script, Number: '+ convert(varchar,@rc)
end


Script 2
: For finding T-SQL queries basis different filters. For example, reporting all queries taking over 3 seconds duration but are not from SQL Profiler or SQL Agent. You can enable or modify commented lines or add more to enable or disable filters for CPU, Reads etc.

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
declare @filecount int
-- Set the time limit for trace, 10 hours default in this script
declare @HourstoRunTrace smallint
set @HourstoRunTrace=10
set @DateTime = DATEADD(hh,@HourstoRunTrace,getdate())
set @maxfilesize = 2048
set @filecount=10
-- Set the file path
declare @filename nvarchar(256)
-- Change the file location from C:\temp to any desired
set @filename= 'c:\temp\Prolifer-Performance-Trace-'+convert(varchar, getdate(), 5)+
'-'+ replace(convert(varchar, getdate(),108),':','')
print 'Trace File is being saved at : '+@filename
exec @rc = sp_trace_create @TraceID output, 2, @filename, @maxfilesize, @DateTime,@filecount
if (@rc != 0) goto error
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 35, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler%'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQLAgent%'
-- Set the filter for duration here, example 3 seconds or 3000 milli seconds
set @bigintfilter = 3000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
set @bigintfilter = NULL
exec sp_trace_setfilter @TraceID, 13, 0, 1, @bigintfilter
-- Set the filter for 'Reads' here
-- set @bigintfilter = 200
-- exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter
-- set @bigintfilter = NULL
-- exec sp_trace_setfilter @TraceID, 16, 0, 1, @bigintfilter
-- Set the filter for 'CPU' here
-- set @intfilter = 100
-- exec sp_trace_setfilter @TraceID, 18, 0, 4, @intfilter
-- set @intfilter = NULL
-- exec sp_trace_setfilter @TraceID, 18, 0, 1, @intfilter
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
print 'TraceID started: '+ convert (varchar, @TraceID)
Print 'Waiting for Trace to complete (for specified duration) :'+ convert (varchar,@DateTime)
waitfor time @DateTime
exec sp_trace_setstatus @TraceID, 0
exec sp_trace_setstatus @TraceID, 2
Print 'Trace Stopped, closed'
error:
if @rc<>0
begin
Print 'Error in the Script, Number: '+ convert(varchar,@rc)
end

Write a comment

*

© 2010 SQLKnowledge.com Increase your website traffic with Attracta.com