SQL Server Profiler - Scheduled Server-Side Recording and Analysis

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 31 Jul 2012

Description

Over the years, I have grown to appreciate the power of SQL Server Profiler when it comes to tracking down rogue or poor performing TSQL.

It can give an insight into the workings of 3rd party applications, even if their code is partially encrypted, and has assisted in everything

from tracking down deadlock participants to identifying the source of lock escalation as well as the traditional

identification of resource-intensive processes. I have even successfully identified a pattern that pointed to an excessively broad transaction that enclosed a

batch process and caused excessive blocking.

However, 'with great power comes great responsibility¹'. Profiler, can itself be resource intensive and have an effect on the system being measured.

If the tool is run and monitored through the GUI, the very process of writing everything to the screen can cause performance problems on the monitored server.

I am always very careful when running on a server for the first time to add filters that restrict the displayed items (eg high reads, writes or CPU), but even these

can have an adverse effect if too many are applied.

Ideally, profiler shouldn't be run against procduction data, but I definitely don't work in an ideal world and most of my customers don't have the time/money/resource to

allow a test environment with data, performance and workload that is representative of production systems, so very often I must track down the initial causes of problems on a

production server.

One aspect of Profiler that I have come to value is the ability to run server-side traces i.e. run using the API and without the need for the GUI

This vastly reduces the resource impact of running Profiler.

However, the API is not intuitive and documentation can be a little tricky to find to help in the setup of anything but the most basic trace.

I also usually want to automate traces rather than have to manually invoke them.

I have therefore developed a solution using several tables stored procedures that allow traces to be scheduled.

Initially, I was just going to publish the basic code, but as I got into the flow of cleaning up my code for publication, it seemed silly not to include the added-value items that I also built to enable quick collation and analysis of the data

The SPs provided are heavily commented throughout the code, and all parameters are explained, but I have summarize the main points below. All code is assumed to be created in the MSDB database, but can easily be relocated to any database on the server to be monitored.

The code is comprised of the following items:

1. Creation of a Central Trace Table

The server-side trace has one main limitation - its storage target can only be a file. It is possible to have a rollover file set, but these must form part of a contiguous monitoring session. If you want to monitor and analyse data across multiple separate sessions, then each file(set) must be loaded into a separate Profiler session. This is not ideal, so I decided to invest in some code to load multiple files into a central table.

SQL Profiler will always output the same set of columns for a specific setup. Column sets differ between trace events. This code is set to record RPC:Completed and SP:Completed events, which should cover most needs, but if you want to add new events, then these tables and SPs will need to be altered.

Documentation on which columns are output for which events is a little hard to come by, so I ran a trial trace and load cycle and used the dynamically created intermediate table as a template.

This Central trace table was the result.

2. Creation of a Trace Schedule Table

This is the table that contains the schedule metadata that allows profiler traces to be set up ahead of schedule. The job polling SP (#) can then be easily set up in a scheduled job to invoke the traces.

The metadata includes the following key items:

• • Trace Duration

  • Max Trace File Size

  • Trace Folder Path

  • Filter Metadata (all optional)

    • Text (will wildcard match against the query text)

    • For Reads, Writes, CPU, Duration

      • Comparison Operator ( >, <, <>, >=, <=, =)

      • Comparison Value (Int/Bigint)

3. Creation of Trace Summary Table

This contains per-minute aggregate data (MIN, MAX, AVG) values for the following measures:

• • Reads

  • Writes

  • Duration

  • CPU

4. SP to construct and execute a server-side Profiler trace

This is the core SP of the solution and constructs and executes a server-side trace using metadata from the trace schedule table (#2). It is invoked from the polling SP (#4)

5. SP to poll the trace schedule table and invoke #4

This SP can easily be scheduled in a SQLAgent job. It checks the trace schedule table (#2) and will instantiate a trace using the trace execute SP (#3) for the next due (or overdue) trace in sequence.

I ran this job for 2 months every minute to check and implement a 7-per day trace schedule on a busy production server with no problems

6. SP to load multiple trace files into a Central Trace Table (#1) via Staging Trace Tables

This SP performs the following steps:

• 1. Iterate through a folder containing only SQL Profiler trace files (with the .trc extension)

     1. Loads each file into an intermediate table

     2. Invokes the SP #7 to load the central table from the intermediate table

     3. Drops the intermediate table

  2. Decodes the EventClass and EventSubClass IDs and adds the text top the table

  3. Summarizes the data just loaded into #3

7. SP to load a single Staging Trace Table from a Single Trace File (invoked by #6)

Loads a single intermediate trace table into the central trace table

8. SP to summarize Central Trace Table into trace Summary Table (invoked by #6)

Creates per-minute summaries for the following measures:

• • Reads

  • Writes

  • Duration

  • CPU

The data summarizing process is incremental by date and will only load data later than the most recent summary.

¹ A quote from Voltaire, Stan Lee or Roosevelt, depending on who you believe :-)

Code

1. Creation of a Central Trace Table

USE [msdb]

GO

DROP TABLE [dbo].[ProfilerTrace_CentralStorage]

GO

CREATE TABLE [dbo].[ProfilerTrace_CentralStorage](

[TextData] [ntext] NULL,

[BinaryData] [image] NULL,

[DatabaseID] [int] NULL,

[TransactionID] [bigint] NULL,

[LineNumber] [int] NULL,

[NTUserName] [nvarchar](256) NULL,

[NTDomainName] [nvarchar](256) NULL,

[HostName] [nvarchar](256) NULL,

[ClientProcessID] [int] NULL,

[ApplicationName] [nvarchar](256) NULL,

[LoginName] [nvarchar](256) NULL,

[SPID] [int] NULL,

[Duration] [bigint] NULL,

[StartTime] [datetime] NULL,

[EndTime] [datetime] NULL,

[Reads] [bigint] NULL,

[Writes] [bigint] NULL,

[CPU] [int] NULL,

[Permissions] [bigint] NULL,

[Severity] [int] NULL,

[EventSubClass] [int] NULL,

[ObjectID] [int] NULL,

[Success] [int] NULL,

[IndexID] [int] NULL,

[IntegerData] [int] NULL,

[ServerName] [nvarchar](256) NULL,

[EventClass] [int] NULL,

[ObjectType] [int] NULL,

[NestLevel] [int] NULL,

[State] [int] NULL,

[Error] [int] NULL,

[Mode] [int] NULL,

[Handle] [int] NULL,

[ObjectName] [nvarchar](256) NULL,

[DatabaseName] [nvarchar](256) NULL,

[FileName] [nvarchar](256) NULL,

[OwnerName] [nvarchar](256) NULL,

[RoleName] [nvarchar](256) NULL,

[TargetUserName] [nvarchar](256) NULL,

[DBUserName] [nvarchar](256) NULL,

[LoginSid] [image] NULL,

[TargetLoginName] [nvarchar](256) NULL,

[TargetLoginSid] [image] NULL,

[ColumnPermissions] [int] NULL,

[LinkedServerName] [nvarchar](256) NULL,

[ProviderName] [nvarchar](256) NULL,

[MethodName] [nvarchar](256) NULL,

[RowCounts] [bigint] NULL,

[RequestID] [int] NULL,

[XactSequence] [bigint] NULL,

[EventSequence] [bigint] NULL,

[BigintData1] [bigint] NULL,

[BigintData2] [bigint] NULL,

[GUID] [uniqueidentifier] NULL,

[IntegerData2] [int] NULL,

[ObjectID2] [bigint] NULL,

[Type] [int] NULL,

[OwnerID] [int] NULL,

[ParentName] [nvarchar](256) NULL,

[IsSystem] [int] NULL,

[Offset] [int] NULL,

[SourceDatabaseID] [int] NULL,

[SqlHandle] [image] NULL,

[SessionLoginName] [nvarchar](256) NULL,

[PlanHandle] [image] NULL,

[OriginalFileName] [nvarchar](500) NULL,

[EventClassText] [varchar](255) NULL,

[EventSubClassText] [varchar](255) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

2. Creation of a Trace Schedule Table

USE [msdb]

GO

DROP TABLE ProfilerServerSideTrace_ScheduleDates

GO

CREATE TABLE [dbo].[ProfilerServerSideTrace_ScheduleDates]

(

Schedule_ID INT IDENTITY(1,1) NOT NULL, -- unique, auto-incrementing id

Schedule_Date DATETIME NOT NULL, -- date/time that the job is scheduled (with time set to midnight)

Job_Name VARCHAR(100) NOT NULL , -- name of the job that is scheduled. This applies to multiple instances of a specific trace type

-- e.g. 'Initial Performance Investigation Trace'

Schedule_Active BIT NOT NULL DEFAULT 0, -- is the schedule active or disabled?

Trace_Purpose VARCHAR(100) NULL, -- just to help us remember why we did it.

-- also forms part of the file name (and table name if loaded into DB)

Send_Email BIT NOT NULL DEFAULT 0, -- determines if the invocation of a trace will cause an email to be sent.

Trace_Duration BIGINT NOT NULL DEFAULT 1, -- #minutes that the trace will run for

Trace_MaxFileSize BIGINT NOT NULL DEFAULT 20,-- #MB that the trace file will be allowed to grow to.

-- Trace will stiop at this point regadless of the duration setting

Filter_Text_Value NVARCHAR(100) NULL, -- the text to be wildcard matched

Filter_CPU_Operator VARCHAR(2) NULL, -- the comparison operator -- '>', '<', '<>' or '='

Filter_CPU_Value BIGINT NULL, -- the CPU limit to be applied

Filter_Reads_Operator VARCHAR(2) NULL, -- the comparison operator -- '>', '<', '<>' or '='

Filter_Reads_Value BIGINT NULL, -- the Reads limit to be applied

Filter_Writes_Operator VARCHAR(2) NULL, -- the comparison operator -- '>', '<', '<>' or '='

Filter_Writes_Value BIGINT NULL, -- the Writes limit to be applied

Filter_Duration_Operator VARCHAR(2) NULL, -- the comparison operator -- '>', '<', '<>' or '='

Filter_Duration_Value BIGINT NULL, -- the Duration limit to be applied

-- NOTE: This is in NANOSECONDS for a file trace rather than MS for a GUI trace. Ask Microsoft :-)

Completion_Date DATETIME NULL --datetime that the scheduled job was successfully completed

) ON [PRIMARY]

INSERT ProfilerServerSideTrace_ScheduleDates

(

Schedule_Date,

Job_Name,

Schedule_Active,

Trace_Purpose,

Trace_Duration,

Trace_MaxFileSize,

Filter_Text_Value,

Filter_CPU_Operator,

Filter_CPU_Value,

Filter_Reads_Operator,

Filter_Reads_Value,

Filter_Writes_Operator,

Filter_Writes_Value,

Filter_Duration_Operator,

Filter_Duration_Value

)

SELECT

DATEADD(mi, 0, GETDATE()) AS Schedule_Date,

'Test Automated Trace' AS Job_Name,

1 AS Schedule_Active,

'Automated Trace - Filter on Text' AS Trace_Purpose,

1 AS Trace_Duration,

20 AS Trace_MaxFileSize,

'sp_help' AS Filter_Text_Value,

NULL AS Filter_CPU_Operator,

NULL AS Filter_CPU_Value,

NULL AS Filter_Reads_Operator,

NULL AS Filter_Reads_Value,

NULL AS Filter_Writes_Operator,

NULL AS Filter_Writes_Value,

NULL AS Filter_Duration_Operator,

NULL AS Filter_Duration_Value

UNION

SELECT

DATEADD(mi, 0, GETDATE()) AS Schedule_Date,

'Test Automated Trace' AS Job_Name,

1 AS Schedule_Active,

'Automated Trace - Filter on CPU' AS Trace_Purpose,

1 AS Trace_Duration,

20 AS Trace_MaxFileSize,

NULL AS Filter_Text_Value,

'<' AS Filter_CPU_Operator,

100 AS Filter_CPU_Value,

NULL AS Filter_Reads_Operator,

NULL AS Filter_Reads_Value,

NULL AS Filter_Writes_Operator,

NULL AS Filter_Writes_Value,

NULL AS Filter_Duration_Operator,

NULL AS Filter_Duration_Value

UNION

SELECT

DATEADD(mi, 0, GETDATE()) AS Schedule_Date,

'Test Automated Trace' AS Job_Name,

1 AS Schedule_Active,

'Automated Trace - Filter on Reads' AS Trace_Purpose,

1 AS Trace_Duration,

20 AS Trace_MaxFileSize,

NULL AS Filter_Text_Value,

NULL AS Filter_CPU_Operator,

NULL AS Filter_CPU_Value,

'>' AS Filter_Reads_Operator,

1 AS Filter_Reads_Value,

NULL AS Filter_Writes_Operator,

NULL AS Filter_Writes_Value,

NULL AS Filter_Duration_Operator,

NULL AS Filter_Duration_Value

UNION

SELECT

DATEADD(mi, 0, GETDATE()) AS Schedule_Date,

'Test Automated Trace' AS Job_Name,

1 AS Schedule_Active,

'Automated Trace - Filter on Writes' AS Trace_Purpose,

1 AS Trace_Duration,

20 AS Trace_MaxFileSize,

NULL AS Filter_Text_Value,

NULL AS Filter_CPU_Operator,

NULL AS Filter_CPU_Value,

NULL AS Filter_Reads_Operator,

NULL AS Filter_Reads_Value,

'>=' AS Filter_Writes_Operator,

1 AS Filter_Writes_Value,

NULL AS Filter_Duration_Operator,

NULL AS Filter_Duration_Value

UNION

SELECT

DATEADD(mi, 0, GETDATE()) AS Schedule_Date,

'Test Automated Trace' AS Job_Name,

1 AS Schedule_Active,

'Automated Trace - Filter on Duration' AS Trace_Purpose,

1 AS Trace_Duration,

20 AS Trace_MaxFileSize,

NULL AS Filter_Text_Value,

NULL AS Filter_CPU_Operator,

NULL AS Filter_CPU_Value,

NULL AS Filter_Reads_Operator,

NULL AS Filter_Reads_Value,

NULL AS Filter_Writes_Operator,

NULL AS Filter_Writes_Value,

'>' AS Filter_Duration_Operator,

1000 AS Filter_Duration_Value

3. Creation of Trace Summary Table

USE msdb

GO

DROP TABLE [dbo].[ProfilerTrace_Summary]

GO

CREATE TABLE [dbo].[ProfilerTrace_Summary](

[StartTime] [datetime] NULL,

[DatabaseName] [nvarchar](256) NULL,

[No_Of_Transactions] [int] NULL,

[Rate_Of_Transactions] [int] NULL,

[Min_Duration] [decimal](18, 2) NULL,

[Max_Duration] [decimal](18, 2) NULL,

[Avg_Duration] [decimal](18, 2) NULL,

[Min_CPU] [int] NULL,

[Max_CPU] [int] NULL,

[Avg_CPU] [int] NULL,

[Min_Reads] [bigint] NULL,

[Max_Reads] [bigint] NULL,

[Avg_Reads] [bigint] NULL,

[Min_Writes] [bigint] NULL,

[Max_Writes] [bigint] NULL,

[Avg_Writes] [bigint] NULL

) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX IX_StartTime ON ProfilerTrace_Summary (StartTime)

GO

4. SP to construct and execute a server-side Profiler trace

USE msdb

GO

DROP PROC [dbo].[usp_ProfilerServerSideTrace_CheckSchedule]

GO

CREATE PROC [dbo].[usp_ProfilerServerSideTrace_CheckSchedule]

@Job_Name VARCHAR(100), -- name of the set of trace definitions

@Folder NVARCHAR(500) -- name of the output folder

AS

/***************************************************************************************

Purpose: Checks the server side trace schedule and runs a trace if one is due.

Intended for unattended profiler trace runs.

Lower load than running through GUI

Scope of this SP:

1. Operates at Server level

2. Utilises msdb..ProfilerServerSideTrace_ScheduleDates table

for schedule dates and trace parameters

3. Invokes msdb..usp_ProfilerServerSideTrace_ExecuteSchedule SP

4. Records completion in msdb..ProfilerServerSideTrace_ScheduleDates table

NOTE: It is assumed that if email is required, then there will generally be an email profile in place

for administrative use. The email profile details are therefore hard-coded into the SP. The default setting

for email in the metadata is 0 (no send), so if there is no profile, the metadata can just be ignored

Author: ChillyDBA

History: 30 Jul 2012

***************************************************************************************/

DECLARE

@Now DATETIME, -- Used to store current date with time set to midnight

@Command NVARCHAR(MAX), -- Contains the constructed command string.

-- Allows the SP to reside in master db along with schedule table

@MailCommand1 NVARCHAR(MAX), -- Contains the starting part of constructed command string for sending the completion email.

@MailCommand NVARCHAR(MAX), -- Contains the completed constructed command string for sending the completion email.

@Schedule_ID INT, -- contains the schedule id of the job to be run

@MailProfile NVARCHAR(100), -- name of the SQLMAil profile to be used to send the maikl

@MailRecipients NVARCHAR(500), -- list of email addresses separated by semi-colon

@Trace_Purpose VARCHAR(100), -- just to help us remember why we did it.

-- also forms part of the file name (and table name if loaded into DB)

@Send_Email BIT, -- determines if the invocation of a trace will cause an email to be sent.

@Trace_Duration BIGINT, -- #minutes that the trace will run for

@Trace_MaxFileSize BIGINT, -- #MB that the trace file will be allowed to grow to.

-- Trace will stiop at this point regadless of the duration setting

@Filter_Text_Value NVARCHAR(100), -- the text to be wildcard matched

@Filter_CPU_Operator VARCHAR(2), -- the comparison operator -- '>', '<', '<>' or '='

@Filter_CPU_Value BIGINT, -- the CPU limit to be applied

@Filter_Reads_Operator VARCHAR(2), -- the comparison operator -- '>', '<', '<>' or '='

@Filter_Reads_Value BIGINT, -- the Reads limit to be applied

@Filter_Writes_Operator VARCHAR(2), -- the comparison operator -- '>', '<', '<>' or '='

@Filter_Writes_Value BIGINT, -- the Writes limit to be applied

@Filter_Duration_Operator VARCHAR(2), -- the comparison operator -- '>', '<', '<>' or '='

@Filter_Duration_Value BIGINT -- the Duration limit to be applied

-- initialise variables

SELECT

@Now = GETDATE(),

@Command = '',

@MailCommand1 = '',

@MailCommand = '',

@MailProfile = 'MyMailProfile',

@MailRecipients = 'MyEmailAddresses'

SELECT @MailCommand1 =

'EXECUTE msdb.dbo.sp_send_dbmail '

+ CHAR(13) + CHAR(10)

+ '@profile_name = ' + @MailProfile + ','

+ CHAR(13) + CHAR(10)

+ '@recipients = ' + @MailRecipients + ','

+ CHAR(13) + CHAR(10)

+ '@subject = N''SQL Server Job System: ~~' + @Job_Name + '~~ ran on ' + @@SERVERNAME + ''','

+ CHAR(13) + CHAR(10)

+ '@body = N'' The Job' -- add the string ' Faiiled ''' or ' Succeeded '''

-- check if there are any jobs that have become due but have not yet run

IF EXISTS ( SELECT 1 FROM ProfilerServerSideTrace_ScheduleDates

WHERE Job_Name = @Job_Name

AND Schedule_Date < @Now

AND Completion_Date IS NULL

AND Schedule_Active = 1 )

BEGIN

-- get the first job in sequence that is due to run

SELECT @Schedule_ID = MIN(Schedule_ID)

FROM ProfilerServerSideTrace_ScheduleDates

WHERE Job_Name = @Job_Name

AND Schedule_Date < @Now

AND Completion_Date IS NULL

AND Schedule_Active = 1

SELECT

@Trace_Purpose = Trace_Purpose,

@Send_Email = Send_Email,

@Trace_Duration = Trace_Duration,

@Trace_MaxFileSize = Trace_MaxFileSize,

@Trace_Duration = Trace_Duration,

@Trace_MaxFileSize = Trace_MaxFileSize,

@Filter_Text_Value = Filter_Text_Value,

@Filter_CPU_Operator = Filter_CPU_Operator,

@Filter_CPU_Value = Filter_CPU_Value,

@Filter_Reads_Operator = Filter_Reads_Operator,

@Filter_Reads_Value = Filter_Reads_Value,

@Filter_Writes_Operator = Filter_Writes_Operator,

@Filter_Writes_Value = Filter_Writes_Value,

@Filter_Duration_Operator = Filter_Duration_Operator,

@Filter_Duration_Value = Filter_Duration_Value

FROM ProfilerServerSideTrace_ScheduleDates (NOLOCK)

WHERE Schedule_ID = @Schedule_ID

SELECT @Command = @Command

+ 'USE msdb'

+ CHAR(13) + CHAR(10)

+ CHAR(13) + CHAR(10)

+ 'EXEC usp_ProfilerServerSideTrace_Execute '

+ '@Folder = ''' + @Folder + ''', '

+ '@Job_Name = ''' + @Job_Name + ''', '

+ '@Trace_Purpose = ''' + @Trace_Purpose + ''', '

+ '@Trace_Duration = ' + CAST(@Trace_Duration AS VARCHAR(10)) + ', '

+ '@Trace_MaxFileSize = ' + CAST(@Trace_MaxFileSize AS VARCHAR(10)) + ', '

+ '@Filter_Text_Value = ' + COALESCE('''' + @Filter_Text_Value + ''', ', 'NULL, ')

+ '@Filter_CPU_Operator = ' + COALESCE('''' + @Filter_CPU_Operator + ''', ', 'NULL, ')

+ '@Filter_CPU_Value = ' + COALESCE('' + CAST(@Filter_CPU_Value AS VARCHAR(10)) + ', ', 'NULL, ')

+ '@Filter_Reads_Operator = ' + COALESCE('''' + @Filter_Reads_Operator + ''', ', 'NULL, ')

+ '@Filter_Reads_Value = ' + COALESCE('' + CAST(@Filter_Reads_Value AS VARCHAR(10)) + ', ', 'NULL, ')

+ '@Filter_Writes_Operator = ' + COALESCE('''' + @Filter_Writes_Operator + ''', ', 'NULL, ')

+ '@Filter_Writes_Value = ' + COALESCE('' + CAST(@Filter_Writes_Value AS VARCHAR(10)) + ', ', 'NULL, ')

+ '@Filter_Duration_Operator = ' + COALESCE('''' + @Filter_Duration_Operator + ''', ', 'NULL, ')

+ '@Filter_Duration_Value = ' + COALESCE('' + CAST(@Filter_Duration_Value AS VARCHAR(10)) + ' ', 'NULL ')

+ CHAR(13) + CHAR(10)

+ CHAR(13) + CHAR(10)

BEGIN TRY

--SELECT @command

-- run the archiving command

EXEC (@Command)

-- update the scheduled dates table to show that the archiving has run

UPDATE ProfilerServerSideTrace_ScheduleDates

SET Completion_Date = GETDATE()

WHERE Schedule_ID = @Schedule_ID

-- send the success email

SELECT @MailCommand = @MailCommand1 + ' Succeeded '''

IF @Send_Email = 1

EXEC (@MailCommand)

END TRY

BEGIN CATCH

--send the failure email

SELECT @MailCommand = @MailCommand1 + ' Failed '''

IF @Send_Email = 1

EXEC (@MailCommand)

END CATCH

END

ELSE

BEGIN

PRINT 'Job has already run or is not scheduled for now'

END

5. SP to poll the trace schedule table and invoke #4

USE msdb

GO

/****** Object: StoredProcedure [dbo].[usp_ProfilerServerSideTrace_Execute] Script Date: 06/29/2012 09:42:22 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

DROP PROCEDURE [dbo].[usp_ProfilerServerSideTrace_Execute]

GO

CREATE PROCEDURE [dbo].[usp_ProfilerServerSideTrace_Execute]

@Folder NVARCHAR(500), -- name of the output folder

@Job_Name VARCHAR(100), -- name of the job that is scheduled

@Trace_Purpose VARCHAR(100), -- just to help us remember why we did it.

--also forms part of the file name (and table name if loaded into DB)

@Trace_Duration BIGINT, --#minutes that the trace will run for

@Trace_MaxFileSize BIGINT, --#MB that the trace file will be allowed to grow to.

--Trace will stiop at this point regadless of the duration setting

-- ** NOTE ** -- data types for the filter values must match teh data type of the trace column being filtered.

-- These can be looked up in SQL BOL

@Filter_Text_Value NVARCHAR(100), -- the text to be wildcard matched

@Filter_CPU_Operator VARCHAR(2), -- the comparison operator -- '>', '<', '<>' or '='

@Filter_CPU_Value INT, -- the CPU limit to be applied

@Filter_Reads_Operator VARCHAR(2), -- the comparison operator -- '>', '<', '<>' or '='

@Filter_Reads_Value BIGINT, -- the Reads limit to be applied

@Filter_Writes_Operator VARCHAR(2), -- the comparison operator -- '>', '<', '<>' or '='

@Filter_Writes_Value BIGINT, -- the Writes limit to be applied

@Filter_Duration_Operator VARCHAR(2), -- the comparison operator -- '>', '<', '<>' or '='

@Filter_Duration_Value BIGINT -- the Duration limit to be applied

AS

/***************************************************************************************

Purpose: Checks constructs server side trace metadata and runs a trace it.

Intended for unattended profiler trace runs.

Lower load than running through GUI

Scope of this SP:

1. Operates at Server level

2. Invoked by msdb..usp_ProfilerServerSideTrace_CheckSchedule SP which passes in

trace parameters

3. Creates trace instance

4. Adds trace counters (a fixed set)

5. Adds trace filters (determined by parameters)

6. Starts the trace

Author: ChillyDBA

History: 30 Jul 2012

***************************************************************************************/

SET NOCOUNT ON

-- Declare local variables

DECLARE

@StopTime DATETIME,

@StartDateString VARCHAR(13),

@On BIT,

@ComparisonOperatorID INT,

@Servername SYSNAME,

@TraceID INT,

@TraceFile NVARCHAR(1000),

@TraceName NVARCHAR(1000),

@intfilter INT

--initialise local variables

SELECT

@StopTime = DATEADD(mi,@Trace_Duration,GETDATE()), -- add the trace duration offset to current datetime

@StartDateString = CONVERT(CHAR(8),GETDATE(),112) -- generate a datetime string for use in the file name --['YYYYMMDD_HHMM']

+ '_'

+ CAST(REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),':','') AS CHAR(4)),

@On = 1, -- used to strart the trace

@ServerName = REPLACE(@@SERVERNAME, '\','_') + '_' -- create a server name string for the file name - replace '\' with '_' for instance names

-- add a '\' to the end of the folder string if it doesn't already have one.

IF RIGHT(@Folder,1)<>'\'

SELECT @Folder = @Folder + '\'

-- now create the trace name and trace filename strings from their component parts

SELECT @TraceName = 'Trace_' + @ServerName + @StartDateString + '_' + @Trace_Purpose

SELECT @TraceFile = @Folder + @TraceName

-- create the trace instance and record the ID in @TraceID

EXEC sp_trace_create

@TraceID = @TraceID OUTPUT,

@options = 0,

@tracefile = @TraceFile,

@maxfilesize = @Trace_MaxFileSize,

@stoptime = @StopTime

-- if the trace didn't create correctly, then quit.

IF (ISNULL(@TraceID, 1) < 1) RETURN(-1)

BEGIN

-- Set the trace events and columns

/*

These are a subset of events and columns and are ones that I have found to be the most

commonly used.

Events

10 - RPC:Completed;

12 - SQL:BatchCompleted

Column IDs

1 TextData;

3 DatabaseID

6 NTUserName;

9 ClientProcessID;

10 ApplicationName;

11 LoginName;

12 SPID;

13 Duration;

14 StartTime;

15 EndTime;

16 Reads;

17 Writes;

18 CPU;

21 EventSubClass;

27 EventClass;

35 DatabaseName;

*/

-- add trace columns for RPC: Completed Events

EXEC sp_trace_setevent @TraceID, 10, 1, @On

EXEC sp_trace_setevent @TraceID, 10, 3, @On

EXEC sp_trace_setevent @TraceID, 10, 6, @On

EXEC sp_trace_setevent @TraceID, 10, 9, @On

EXEC sp_trace_setevent @TraceID, 10, 10, @On

EXEC sp_trace_setevent @TraceID, 10, 11, @On

EXEC sp_trace_setevent @TraceID, 10, 12, @On

EXEC sp_trace_setevent @TraceID, 10, 13, @On

EXEC sp_trace_setevent @TraceID, 10, 14, @On

EXEC sp_trace_setevent @TraceID, 10, 15, @On

EXEC sp_trace_setevent @TraceID, 10, 16, @On

EXEC sp_trace_setevent @TraceID, 10, 17, @On

EXEC sp_trace_setevent @TraceID, 10, 18, @On

EXEC sp_trace_setevent @TraceID, 10, 21, @On

EXEC sp_trace_setevent @TraceID, 10, 27, @On

EXEC sp_trace_setevent @TraceID, 10, 35, @On

-- add trace columns for SQL:BatchCompleted Events

EXEC sp_trace_setevent @TraceID, 12, 1, @On

EXEC sp_trace_setevent @TraceID, 12, 3, @On

EXEC sp_trace_setevent @TraceID, 12, 6, @On

EXEC sp_trace_setevent @TraceID, 12, 9, @On

EXEC sp_trace_setevent @TraceID, 12, 10, @On

EXEC sp_trace_setevent @TraceID, 12, 11, @On

EXEC sp_trace_setevent @TraceID, 12, 12, @On

EXEC sp_trace_setevent @TraceID, 12, 13, @On

EXEC sp_trace_setevent @TraceID, 12, 14, @On

EXEC sp_trace_setevent @TraceID, 12, 15, @On

EXEC sp_trace_setevent @TraceID, 12, 16, @On

EXEC sp_trace_setevent @TraceID, 12, 17, @On

EXEC sp_trace_setevent @TraceID, 12, 18, @On

EXEC sp_trace_setevent @TraceID, 12, 21, @On

EXEC sp_trace_setevent @TraceID, 12, 27, @On

EXEC sp_trace_setevent @TraceID, 12, 35, @On

-- Set the trace Filters

-- NOTE: variables are used for the BIGINT filter values as this SP does not seem to accept

-- values with or without or CAST statements

-- use sp_trace_setfilter <TraceID>, <ColumnID>, <Logical Operator>, <Comparison Operator>, <Comparison Value>

-- <Logical Operator>: 0 = AND, 1 == OR. AND will always be used here

-- standard exclustion for Application Name to exlude profiler

EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler '

END

-- now set the filters defined by the values stored agains the trace definition and passed as parameters

-- but only if they are not null and valid

IF (@Filter_Text_Value IS NOT NULL)

BEGIN

SELECT

@ComparisonOperatorID = 6, -- this will always be a LIKE operation

@Filter_Text_Value = N'%' + @Filter_Text_Value + N'%'

EXEC sp_trace_setfilter @TraceID, 1, 0, @ComparisonOperatorID, @Filter_Text_Value

END

-- set up the CPU filter if defined

IF (@Filter_CPU_Operator IS NOT NULL)

AND (@Filter_CPU_Value IS NOT NULL)

AND (@Filter_CPU_Operator IN ('>', '<', '<>', '>=', '<=', '='))

BEGIN

SELECT @ComparisonOperatorID = NULL -- reset it

SELECT @ComparisonOperatorID = CASE @Filter_CPU_Operator

WHEN '=' THEN 0

WHEN '<>' THEN 1

WHEN '>' THEN 2

WHEN '<' THEN 3

WHEN '>=' THEN 4

WHEN '<=' THEN 5

END

EXEC sp_trace_setfilter @TraceID, 18, 0, @ComparisonOperatorID, @Filter_CPU_Value

END

-- set up the Reads filter if defined

IF (@Filter_Reads_Operator IS NOT NULL)

AND (@Filter_Reads_Value IS NOT NULL)

AND (@Filter_Reads_Operator IN ('>', '<', '<>', '>=', '<=', '='))

BEGIN

SELECT @ComparisonOperatorID = NULL -- reset it

SELECT @ComparisonOperatorID = CASE @Filter_Reads_Operator

WHEN '=' THEN 0

WHEN '<>' THEN 1

WHEN '>' THEN 2

WHEN '<' THEN 3

WHEN '>=' THEN 4

WHEN '<=' THEN 5

END

EXEC sp_trace_setfilter @TraceID, 16, 0, @ComparisonOperatorID, @Filter_Reads_Value

END

-- set up the Writes filter if defined

IF (@Filter_Writes_Operator IS NOT NULL)

AND (@Filter_Writes_Value IS NOT NULL)

AND (@Filter_Writes_Operator IN ('>', '<', '<>', '>=', '<=', '='))

BEGIN

SELECT @ComparisonOperatorID = NULL -- reset it

SELECT @ComparisonOperatorID = CASE @Filter_Writes_Operator

WHEN '=' THEN 0

WHEN '<>' THEN 1

WHEN '>' THEN 2

WHEN '<' THEN 3

WHEN '>=' THEN 4

WHEN '<=' THEN 5

END

EXEC sp_trace_setfilter @TraceID, 17, 0, @ComparisonOperatorID, @Filter_Writes_Value

END

-- set up the Duration filter if defined (in NANOSECONDS)

IF (@Filter_Duration_Operator IS NOT NULL)

AND (@Filter_Duration_Value IS NOT NULL)

AND (@Filter_Duration_Operator IN ('>', '<', '<>', '>=', '<=', '='))

BEGIN

SELECT @ComparisonOperatorID = NULL -- reset it

SELECT @ComparisonOperatorID = CASE @Filter_Duration_Operator

WHEN '=' THEN 0

WHEN '<>' THEN 1

WHEN '>' THEN 2

WHEN '<' THEN 3

WHEN '>=' THEN 4

WHEN '<=' THEN 5

END

EXEC sp_trace_setfilter @TraceID, 13, 0, @ComparisonOperatorID, @Filter_Duration_Value

END

-- Set the trace status to start

EXEC sp_trace_setstatus @TraceID, 1

RETURN;

SET NOCOUNT OFF

6. SP to load multiple trace files into a Central Trace Table (#1) via Staging Trace Tables

USE msdb

GO

DROP PROC [dbo].[usp_ProfilerServerSideTrace_LoadMultipleTraceFiles]

GO

CREATE PROC [dbo].[usp_ProfilerServerSideTrace_LoadMultipleTraceFiles]

@FilePath NVARCHAR(500) -- name of the output folder

AS

/***************************************************************************************

Purpose: Loads multiple SQL Server Profiler trace files into tables with names

that match the file names.

This utilizes a system function fn_trace_gettable(), which dynamically

creates the destination table with the corect structure

Scope of this SP:

1. Requires a full file patch (no trailing '\') to a folder containing only .trc files

and with no subfolders

2. Utilises system function fn_trace_gettable(), which dynamically

creates the destination table with the correct structure

Author: ChillyDBA

History: 31 Jul 2012

***************************************************************************************/

SET NOCOUNT ON

DECLARE

@FileName NVARCHAR(1000),

@TableName SYSNAME,

@ID INT,

@Cmd NVARCHAR(1000),

@FullFileNameAndPath VARCHAR(1000)

-- create a table variable to contain the list of files from the supplied Folder Path

DECLARE @DirTree TABLE

(

ID INT IDENTITY(1,1), -- just used for iteration

fname NVARCHAR(255), -- the file name

depth INT, -- not used

isfile INT -- not used

)

-- Initialize variables

SELECT

@Cmd = ''

-- Load the file listing

INSERT INTO @DirTree(fname, depth, isfile)

EXEC MASTER.sys.xp_dirtree @FilePath,1,1

-- iterate through the file listing

SELECT @ID = MIN(ID)

FROM @DirTree

WHILE @ID IS NOT NULL

BEGIN

SELECT @Cmd = ''

-- get the file name

SELECT @FileName = fname

FROM @DirTree

WHERE ID = @ID

-- clean up the file name and construct the table name

SELECT @TableName = REPLACE(REVERSE(SUBSTRING(REVERSE(@FileName), CHARINDEX('.',REVERSE(@FileName)) + 1, 1000)), '-', '_')

SELECT @TableName = REPLACE(@TableName, ' ', '_')

SELECT @TableName = REPLACE(@TableName, '__', '_')

-- construct the full file path and name

SELECT @FullFileNameAndPath = @FilePath + '\' + @FileName

-- construct the function command string

SELECT @Cmd = @Cmd

+ 'SELECT * INTO '

+ @TableName

+ ' FROM fn_trace_gettable('''

+ @FullFileNameAndPath

+''', default)'

--SELECT @ID, @FullFileNameAndPath, @Cmd

-- execute the command string

EXEC (@Cmd)

-- load the table into the central storage

EXEC usp_ProfilerServerSideTrace_CollateMultipleTraceTables @TableName , @FileName

-- now clean up (drop) the intermediate table

SELECT @Cmd = 'DROP TABLE ' + @TableName

EXEC (@Cmd)

-- get next file

SELECT @ID = MIN(ID)

FROM @DirTree

WHERE ID > @ID

END

-- translate the event class IDs into text where possible

-- these are added to save having to keep looking them up during analysis

UPDATE t

SET

EventClassText = e.Name,

EventSubClassText = v.SubClass_Name

FROM ProfilerTrace_CentralStorage t

LEFT OUTER JOIN sys.trace_events e ON e.trace_event_id = t.EventClass

LEFT OUTER JOIN sys.trace_subclass_values v ON v.trace_event_id = e.trace_event_id AND v.subclass_value = t.EventSubClass

WHERE t.EventClass IS NOT NULL

UPDATE STATISTICS ProfilerTrace_CentralStorage

-- summarize the data just loaded

EXEC usp_ProfilerServerSideTrace_SummarizeTraceData

SET NOCOUNT OFF

7. SP to load a single Staging Trace Table from a Single Trace File (invoked by #6)

USE msdb

GO

DROP PROC [dbo].[usp_ProfilerServerSideTrace_CollateMultipleTraceTables]

GO

CREATE PROC [dbo].[usp_ProfilerServerSideTrace_CollateMultipleTraceTables]

@TableName NVARCHAR(500), -- name of the table to be loaded

@FileName NVARCHAR(500) -- name original file (to add to the central data store as a new column)

AS

/***************************************************************************************

Purpose: Loads data from an intermediate trace table previously loaded by the

usp_ProfilerServerSideTrace_LoadMultipleTraceFiles SP into

a central trace table.

This is normally invoked by that same SP

Scope of this SP:

1. Loads only one table at a time

2. Appends the original file name to the trace data

Author: ChillyDBA

History: 31 Jul 2012

***************************************************************************************/

SET NOCOUNT ON

DECLARE

@Cmd VARCHAR(MAX),

@CRLF VARCHAR(10)

-- initialize the variables

SELECT

@CRLF = CHAR(13),

@Cmd = ''

SELECT @Cmd =

@Cmd

+ 'INSERT ProfilerTrace_CentralStorage' + @CRLF

+ '(' + @CRLF

+ ' TextData, BinaryData, DatabaseID, TransactionID, LineNumber,' + @CRLF

+ ' NTUserName, NTDomainName, HostName, ClientProcessID, ApplicationName,' + @CRLF

+ ' LoginName, SPID, Duration, StartTime, EndTime,' + @CRLF

+ ' Reads, Writes, CPU, Permissions, Severity,' + @CRLF

+ ' EventSubClass, ObjectID, Success, IndexID, IntegerData,' + @CRLF

+ ' ServerName, EventClass, ObjectType, NestLevel, State,' + @CRLF

+ ' Error, Mode, Handle, ObjectName, DatabaseName,' + @CRLF

+ ' FileName, OwnerName, RoleName, TargetUserName, DBUserName,' + @CRLF

+ ' LoginSid, TargetLoginName, TargetLoginSid, ColumnPermissions, LinkedServerName,' + @CRLF

+ ' ProviderName, MethodName, RowCounts, RequestID, XactSequence,' + @CRLF

+ ' EventSequence, BigintData1, BigintData2, GUID, IntegerData2,' + @CRLF

+ ' ObjectID2, Type, OwnerID, ParentName, IsSystem,' + @CRLF

+ ' Offset, SourceDatabaseID, SqlHandle, SessionLoginName, PlanHandle,' + @CRLF

+ ' OriginalFileName' + @CRLF

+ ')' + @CRLF

+ 'SELECT' + @CRLF

+ ' TextData, BinaryData, DatabaseID, TransactionID, LineNumber,' + @CRLF

+ ' NTUserName, NTDomainName, HostName, ClientProcessID, ApplicationName,' + @CRLF

+ ' LoginName, SPID, Duration, StartTime, EndTime,' + @CRLF

+ ' Reads, Writes, CPU, Permissions, Severity,' + @CRLF

+ ' EventSubClass, ObjectID, Success, IndexID, IntegerData,' + @CRLF

+ ' ServerName, EventClass, ObjectType, NestLevel, State,' + @CRLF

+ ' Error, Mode, Handle, ObjectName, DatabaseName,' + @CRLF

+ ' FileName, OwnerName, RoleName, TargetUserName, DBUserName,' + @CRLF

+ ' LoginSid, TargetLoginName, TargetLoginSid, ColumnPermissions, LinkedServerName,' + @CRLF

+ ' ProviderName, MethodName, RowCounts, RequestID, XactSequence,' + @CRLF

+ ' EventSequence, BigintData1, BigintData2, GUID, IntegerData2,' + @CRLF

+ ' ObjectID2, Type, OwnerID, ParentName, IsSystem,' + @CRLF

+ ' Offset, SourceDatabaseID, SqlHandle, SessionLoginName, PlanHandle,' + @CRLF

+ ' ''' + @FileName + '''' + @CRLF

+ 'FROM ' + @TableName + '(NOLOCK)' + @CRLF

+ 'ORDER BY LineNumber'+ @CRLF + @CRLF

--select @Cmd

EXECUTE (@Cmd)

SET NOCOUNT OFF

8. SP to summarize Central Trace Table into trace Summary Table (invoked by #6)

USE msdb

GO

DROP PROC [dbo].[usp_ProfilerServerSideTrace_SummarizeTraceData]

GO

CREATE PROC [dbo].[usp_ProfilerServerSideTrace_SummarizeTraceData]

AS

/***************************************************************************************

Purpose: Summarizes data from the central trace table previously loaded by the

usp_ProfilerServerSideTrace_CollateMultipleTraceTables SP into

a summary trace table.

This is normally invoked by the usp_ProfilerServerSideTrace_LoadMultipleTraceFiles SP

Scope of this SP:

1. Determines the last-loaded date

2. Reads, summarizes and loads data from that date onwards

Author: ChillyDBA

History: 31 Jul 2012

***************************************************************************************/

DECLARE @MaxStartTime DATETIME

SELECT @MaxStartTime = ISNULL(MAX(StartTime), '1 Jan 1900') FROM ProfilerTrace_Summary (NOLOCK)

INSERT ProfilerTrace_Summary

(

StartTime,

DatabaseName,

No_Of_Transactions,

Rate_Of_Transactions,

Min_Duration,

Max_Duration,

Avg_Duration,

Min_CPU,

Max_CPU,

Avg_CPU,

Min_Reads,

Max_Reads,

Avg_Reads,

Min_Writes,

Max_Writes,

Avg_Writes

)

SELECT

CONVERT(DATETIME, CONVERT(VARCHAR(17), StartTime, 113)) AS StartTime,

DatabaseName AS DatabaseName,

COUNT(*) AS No_Of_Transactions,

COUNT(*) /

CASE WHEN DATEDIFF(ss, MIN(StartTime), MAX(StartTime)) = 0 THEN 1

ELSE DATEDIFF(ss, MIN(StartTime), MAX(StartTime))

END AS Rate_Of_Transactions,

MIN(Duration)/1000 AS Min_Duration,

MAX(Duration)/1000 AS Max_Duration,

AVG(Duration)/1000 AS Avg_Duration,

MIN(CPU) AS Min_CPU,

MAX(CPU) AS Max_CPU,

AVG(CPU) AS Avg_CPU,

MIN(Reads) AS Min_Reads,

MAX(Reads) AS Max_Reads,

AVG(Reads) AS Avg_Reads,

MIN(Writes) AS Min_Writes,

MAX(Writes) AS Max_Writes,

AVG(Writes) AS Avg_Writes

FROM ProfilerTrace_CentralStorage (NOLOCK)

WHERE DatabaseName IS NOT NULL

AND StartTime >= DATEADD(mi, 1, @MaxStartTime)

GROUP BY

CONVERT(DATETIME, CONVERT(VARCHAR(17), StartTime, 113)),

DatabaseName

ORDER BY DatabaseName, CONVERT(DATETIME, CONVERT(VARCHAR(17), StartTime, 113))