Performance - 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 responsibility1'.  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.


1 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))
  



ċ
Automated Profiler Trace 01 - Create Central Trace Table.sql
(2k)
Andy Hughes,
Aug 1, 2012, 7:53 AM
ċ
Automated Profiler Trace 02 - Create Schedule Table.sql
(6k)
Andy Hughes,
Aug 1, 2012, 7:53 AM
ċ
Automated Profiler Trace 03 - Create Summary Trace Table.sql
(1k)
Andy Hughes,
Aug 1, 2012, 7:53 AM
ċ
Automated Profiler Trace 04 - Create SP to check trace schedule.sql
(16k)
Andy Hughes,
Aug 1, 2012, 7:53 AM
ċ
Automated Profiler Trace 05 - Create SP to execute trace.sql
(19k)
Andy Hughes,
Aug 1, 2012, 7:53 AM
ċ
Automated Profiler Trace 06 - Load Multiple Trace Files into Multiple Tables.sql
(4k)
Andy Hughes,
Aug 1, 2012, 7:53 AM
ċ
Automated Profiler Trace 07 - Load Multiple Trace Tables into Central Table.sql
(3k)
Andy Hughes,
Aug 1, 2012, 7:53 AM
ċ
Automated Profiler Trace 08 - Load Summary Table from Central Trace Tables into Central Table.sql
(2k)
Andy Hughes,
Aug 1, 2012, 7:53 AM
Comments