/*
-- The following script is mentioned in the "Modifying a Workload in a Trace Table for Special Needs" section
-- of Chapter 10. The following example demonstrates an idea of calculating and inserting time delay events
-- into a trace table.
--
-- Steps:
-- 1. As a preparation, a SQL Profiler trace has been collected and loaded into a data table named
-- [myTraceData].[dbo].Tracedata_from_SQL2005_Profiler_001.
-- To use the script, it is required to collect a trace file and load it into the table. You may want to use
-- the following statement to load your trace file into the table of [myTraceData] database.
-- SELECT *
-- INTO dbo.Tracedata_from_SQL2005_Profiler_001
-- FROM ::fn_trace_gettable('c:\temp\myTraceData.trc', default)
--
-- 2. Create a new table to store the above trace data with time delay events. In this example, we named the
-- new trace table as [dbo].Tracedata_from_SQL2005_Profiler_001_with_Inverval.
--
-- 3. Loop through each of event in the [myTraceData].[dbo].Tracedata_from_SQL2005_Profiler_001 table and do
-- two things:
-- a. Insert each event to the table [dbo].Tracedata_from_SQL2005_Profiler_001_with_Inverval.
-- b. Evaluate if there is a time delay between two event. If there is a delay, then insert a calculated
-- time delay event to the table [dbo].Tracedata_from_SQL2005_Profiler_001_with_Inverval.
--
-- As a result, the table [dbo].Tracedata_from_SQL2005_Profiler_001_with_Inverval will contain original trace
-- event data with additional time delay events. Using Profiler to reply the newly created trace table, all trace
-- events will be replayed with realistic timing as the original trace events were collected.
--
-- Notes:
-- 1. Modified the RowNumber column to a non identity column to allow inserting entries with time delay
-- 2. Use the script to store trace data from SQL 2005 Profiler only. It is not compatible with SQL 2000 Profiler.
--
*/
CREATE TABLE [dbo].Tracedata_from_SQL2005_Profiler_001_with_Inverval(
[RowNumber] [int] NOT NULL,
[EventClass] [int] NULL,
[EventSequence] [int] NULL,
[TextData] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Duration] [bigint] NULL,
[CPU] [int] NULL,
[Reads] [bigint] NULL,
[Writes] [bigint] NULL,
[ApplicationName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LoginName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DatabaseName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DatabaseID] [int] NULL,
[ClientProcessID] [int] NULL,
[HostName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BinaryData] [image] NULL,
[SPID] [int] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[IsSystem] [int] NULL,
[NTDomainName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NTUserName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Error] [int] NULL,
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-- 1. Assuming the delay interval is less then 23:59:59:997 (hh:mm:ss:ms)
-- 2. Modify table names to meet your spefic need
-- 3. The script will not work with SQL 2000 Profiler trace table data without modifications
DECLARE @PreviousRow_RowNumber int
DECLARE @PreviousRow_StartTime datetime
DECLARE @PreviousRow_EndTime datetime
DECLARE @RowNumber int
DECLARE @EventClass int
DECLARE @EventSequence int
DECLARE @TextData nvarchar(max)
DECLARE @Duration bigint
DECLARE @CPU int
DECLARE @Reads bigint
DECLARE @Writes bigint
DECLARE @ApplicationName nvarchar (128)
DECLARE @LoginName nvarchar (128)
DECLARE @DatabaseName nvarchar (128)
DECLARE @DatabaseID int
DECLARE @ClientProcessID int
DECLARE @HostName nvarchar (128)
DECLARE @ServerName nvarchar (128)
DECLARE @BinaryData varbinary(max)
DECLARE @SPID int
DECLARE @StartTime datetime
DECLARE @EndTime datetime
DECLARE @IsSystem int
DECLARE @NTDomainName nvarchar (128)
DECLARE @NTUserName nvarchar (128)
DECLARE @Error int
DECLARE @NewEventSequence int
DECLARE @NewRowNumber int
DECLARE @EventDelayLoginName nvarchar (128)
DECLARE @EventDelayLoginDefaultDB nvarchar (128)
DECLARE @DelayCommandStr nvarchar (128)
SET NOCOUNT ON
SET @NewRowNumber = 0
SET @NewEventSequence = 1
SET @EventDelayLoginName = 'ds2000' -- Change it to an existing login name
SET @EventDelayLoginDefaultDB = 'DS2' -- Change it to the default DB of the @EventDelayLoginName
DECLARE TraceEvent_cursor CURSOR FOR
SELECT
[RowNumber]
,[EventClass]
,[EventSequence]
,[TextData]
,[Duration]
,[CPU]
,[Reads]
,[Writes]
,[ApplicationName]
,[LoginName]
,[DatabaseName]
,[DatabaseID]
,[ClientProcessID]
,[HostName]
,[ServerName]
,[BinaryData]
,[SPID]
,[StartTime]
,[EndTime]
,[IsSystem]
,[NTDomainName]
,[NTUserName]
,[Error]
FROM [myTraceData].[dbo].Tracedata_from_SQL2005_Profiler_001 -- Change the original trace data table
OPEN TraceEvent_cursor
FETCH NEXT FROM TraceEvent_cursor INTO
@RowNumber,
@EventClass,
@EventSequence,
@TextData,
@Duration,
@CPU,
@Reads,
@Writes,
@ApplicationName,
@LoginName,
@DatabaseName,
@DatabaseID,
@ClientProcessID,
@HostName,
@ServerName,
@BinaryData,
@SPID,
@StartTime,
@EndTime,
@IsSystem,
@NTDomainName,
@NTUserName,
@Error
WHILE @@FETCH_STATUS = 0
BEGIN
--SELECT @EventSequence
--SELECT @PreviousRow_EndTime AS PreviousRow_EndTime
--SELECT @StartTime AS StartTime
--SELECT DATEDIFF (MS, @PreviousRow_EndTime, @StartTime)
IF ( (DATEDIFF (MS, @PreviousRow_EndTime, @StartTime) > 0) AND
(@EventSequence is not NULL) ) BEGIN
SELECT @RowNumber
--PRINT '..............delay.................' +
--CAST (DATEDIFF (MS, @PreviousRow_EndTime, @StartTime) AS VARCHAR) + ' Milisecond.'
-- WAITFOR DELAY requires having a time in datetime format, since the WAITFOR DELAY has
-- a limitation on 24 hours, the easiest way to calculate the delta time is use subtraction.
SELECT @DelayCommandStr = 'WAITFOR DELAY ' + '''' +
CONVERT (varchar, (@StartTime - @PreviousRow_EndTime),114) + ''''
INSERT INTO Tracedata_from_SQL2005_Profiler_001_with_Inverval ( -- Change the new trace data table
[RowNumber]
,[EventClass]
,[EventSequence]
,[TextData]
,[Duration]
,[CPU]
,[Reads]
,[Writes]
,[ApplicationName]
,[LoginName]
,[DatabaseName]
,[DatabaseID]
,[ClientProcessID]
,[HostName]
,[ServerName]
,[BinaryData]
,[SPID]
,[StartTime]
,[EndTime]
,[IsSystem]
,[NTDomainName]
,[NTUserName]
,[Error]
)
VALUES (
@NewRowNumber,
13,
CASE WHEN (@EventSequence is NULL) THEN @EventSequence
ELSE @NewEventSequence
END, -- New Sequence Number
@DelayCommandStr,
NULL,
NULL,
NULL,
NULL,
NULL,
@EventDelayLoginName,
@EventDelayLoginDefaultDB,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
)
SELECT @NewEventSequence = @NewEventSequence + 1
SELECT @NewRowNumber = @NewRowNumber + 1
END
SELECT --@PreviousRow_RowNumber = @RowNumber,
@PreviousRow_StartTime = @StartTime,
@PreviousRow_EndTime = @EndTime
INSERT INTO Tracedata_from_SQL2005_Profiler_001_with_Inverval ( -- Change the new trace data table
[RowNumber]
,[EventClass]
,[EventSequence]
,[TextData]
,[Duration]
,[CPU]
,[Reads]
,[Writes]
,[ApplicationName]
,[LoginName]
,[DatabaseName]
,[DatabaseID]
,[ClientProcessID]
,[HostName]
,[ServerName]
,[BinaryData]
,[SPID]
,[StartTime]
,[EndTime]
,[IsSystem]
,[NTDomainName]
,[NTUserName]
,[Error]
)
VALUES (
@NewRowNumber,
@EventClass,
CASE WHEN (@EventSequence is NULL) THEN @EventSequence
ELSE @NewEventSequence
END, -- New Sequence Number
@TextData,
@Duration,
@CPU,
@Reads,
@Writes,
@ApplicationName,
@LoginName,
@DatabaseName,
@DatabaseID,
@ClientProcessID,
@HostName,
@ServerName,
@BinaryData,
@SPID,
@StartTime,
@EndTime,
@IsSystem,
@NTDomainName,
@NTUserName,
@Error
)
SELECT @NewEventSequence = @NewEventSequence + 1
SELECT @NewRowNumber = @NewRowNumber + 1
FETCH NEXT FROM TraceEvent_cursor INTO
@RowNumber,
@EventClass,
@EventSequence,
@TextData,
@Duration,
@CPU,
@Reads,
@Writes,
@ApplicationName,
@LoginName,
@DatabaseName,
@DatabaseID,
@ClientProcessID,
@HostName,
@ServerName,
@BinaryData,
@SPID,
@StartTime,
@EndTime,
@IsSystem,
@NTDomainName,
@NTUserName,
@Error
END
CLOSE TraceEvent_cursor
DEALLOCATE TraceEvent_cursor
-- The following script is mentioned in the "Modifying a Workload in a Trace Table for Special Needs" section
-- of Chapter 10. The following example demonstrates an idea of calculating and inserting time delay events
-- into a trace table.
--
-- Steps:
-- 1. As a preparation, a SQL Profiler trace has been collected and loaded into a data table named
-- [myTraceData].[dbo].Tracedata_from_SQL2005_Profiler_001.
-- To use the script, it is required to collect a trace file and load it into the table. You may want to use
-- the following statement to load your trace file into the table of [myTraceData] database.
-- SELECT *
-- INTO dbo.Tracedata_from_SQL2005_Profiler_001
-- FROM ::fn_trace_gettable('c:\temp\myTraceData.trc', default)
--
-- 2. Create a new table to store the above trace data with time delay events. In this example, we named the
-- new trace table as [dbo].Tracedata_from_SQL2005_Profiler_001_with_Inverval.
--
-- 3. Loop through each of event in the [myTraceData].[dbo].Tracedata_from_SQL2005_Profiler_001 table and do
-- two things:
-- a. Insert each event to the table [dbo].Tracedata_from_SQL2005_Profiler_001_with_Inverval.
-- b. Evaluate if there is a time delay between two event. If there is a delay, then insert a calculated
-- time delay event to the table [dbo].Tracedata_from_SQL2005_Profiler_001_with_Inverval.
--
-- As a result, the table [dbo].Tracedata_from_SQL2005_Profiler_001_with_Inverval will contain original trace
-- event data with additional time delay events. Using Profiler to reply the newly created trace table, all trace
-- events will be replayed with realistic timing as the original trace events were collected.
--
-- Notes:
-- 1. Modified the RowNumber column to a non identity column to allow inserting entries with time delay
-- 2. Use the script to store trace data from SQL 2005 Profiler only. It is not compatible with SQL 2000 Profiler.
--
*/
CREATE TABLE [dbo].Tracedata_from_SQL2005_Profiler_001_with_Inverval(
[RowNumber] [int] NOT NULL,
[EventClass] [int] NULL,
[EventSequence] [int] NULL,
[TextData] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Duration] [bigint] NULL,
[CPU] [int] NULL,
[Reads] [bigint] NULL,
[Writes] [bigint] NULL,
[ApplicationName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LoginName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DatabaseName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DatabaseID] [int] NULL,
[ClientProcessID] [int] NULL,
[HostName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BinaryData] [image] NULL,
[SPID] [int] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[IsSystem] [int] NULL,
[NTDomainName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NTUserName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Error] [int] NULL,
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-- 1. Assuming the delay interval is less then 23:59:59:997 (hh:mm:ss:ms)
-- 2. Modify table names to meet your spefic need
-- 3. The script will not work with SQL 2000 Profiler trace table data without modifications
DECLARE @PreviousRow_RowNumber int
DECLARE @PreviousRow_StartTime datetime
DECLARE @PreviousRow_EndTime datetime
DECLARE @RowNumber int
DECLARE @EventClass int
DECLARE @EventSequence int
DECLARE @TextData nvarchar(max)
DECLARE @Duration bigint
DECLARE @CPU int
DECLARE @Reads bigint
DECLARE @Writes bigint
DECLARE @ApplicationName nvarchar (128)
DECLARE @LoginName nvarchar (128)
DECLARE @DatabaseName nvarchar (128)
DECLARE @DatabaseID int
DECLARE @ClientProcessID int
DECLARE @HostName nvarchar (128)
DECLARE @ServerName nvarchar (128)
DECLARE @BinaryData varbinary(max)
DECLARE @SPID int
DECLARE @StartTime datetime
DECLARE @EndTime datetime
DECLARE @IsSystem int
DECLARE @NTDomainName nvarchar (128)
DECLARE @NTUserName nvarchar (128)
DECLARE @Error int
DECLARE @NewEventSequence int
DECLARE @NewRowNumber int
DECLARE @EventDelayLoginName nvarchar (128)
DECLARE @EventDelayLoginDefaultDB nvarchar (128)
DECLARE @DelayCommandStr nvarchar (128)
SET NOCOUNT ON
SET @NewRowNumber = 0
SET @NewEventSequence = 1
SET @EventDelayLoginName = 'ds2000' -- Change it to an existing login name
SET @EventDelayLoginDefaultDB = 'DS2' -- Change it to the default DB of the @EventDelayLoginName
DECLARE TraceEvent_cursor CURSOR FOR
SELECT
[RowNumber]
,[EventClass]
,[EventSequence]
,[TextData]
,[Duration]
,[CPU]
,[Reads]
,[Writes]
,[ApplicationName]
,[LoginName]
,[DatabaseName]
,[DatabaseID]
,[ClientProcessID]
,[HostName]
,[ServerName]
,[BinaryData]
,[SPID]
,[StartTime]
,[EndTime]
,[IsSystem]
,[NTDomainName]
,[NTUserName]
,[Error]
FROM [myTraceData].[dbo].Tracedata_from_SQL2005_Profiler_001 -- Change the original trace data table
OPEN TraceEvent_cursor
FETCH NEXT FROM TraceEvent_cursor INTO
@RowNumber,
@EventClass,
@EventSequence,
@TextData,
@Duration,
@CPU,
@Reads,
@Writes,
@ApplicationName,
@LoginName,
@DatabaseName,
@DatabaseID,
@ClientProcessID,
@HostName,
@ServerName,
@BinaryData,
@SPID,
@StartTime,
@EndTime,
@IsSystem,
@NTDomainName,
@NTUserName,
@Error
WHILE @@FETCH_STATUS = 0
BEGIN
--SELECT @EventSequence
--SELECT @PreviousRow_EndTime AS PreviousRow_EndTime
--SELECT @StartTime AS StartTime
--SELECT DATEDIFF (MS, @PreviousRow_EndTime, @StartTime)
IF ( (DATEDIFF (MS, @PreviousRow_EndTime, @StartTime) > 0) AND
(@EventSequence is not NULL) ) BEGIN
SELECT @RowNumber
--PRINT '..............delay.................' +
--CAST (DATEDIFF (MS, @PreviousRow_EndTime, @StartTime) AS VARCHAR) + ' Milisecond.'
-- WAITFOR DELAY requires having a time in datetime format, since the WAITFOR DELAY has
-- a limitation on 24 hours, the easiest way to calculate the delta time is use subtraction.
SELECT @DelayCommandStr = 'WAITFOR DELAY ' + '''' +
CONVERT (varchar, (@StartTime - @PreviousRow_EndTime),114) + ''''
INSERT INTO Tracedata_from_SQL2005_Profiler_001_with_Inverval ( -- Change the new trace data table
[RowNumber]
,[EventClass]
,[EventSequence]
,[TextData]
,[Duration]
,[CPU]
,[Reads]
,[Writes]
,[ApplicationName]
,[LoginName]
,[DatabaseName]
,[DatabaseID]
,[ClientProcessID]
,[HostName]
,[ServerName]
,[BinaryData]
,[SPID]
,[StartTime]
,[EndTime]
,[IsSystem]
,[NTDomainName]
,[NTUserName]
,[Error]
)
VALUES (
@NewRowNumber,
13,
CASE WHEN (@EventSequence is NULL) THEN @EventSequence
ELSE @NewEventSequence
END, -- New Sequence Number
@DelayCommandStr,
NULL,
NULL,
NULL,
NULL,
NULL,
@EventDelayLoginName,
@EventDelayLoginDefaultDB,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
)
SELECT @NewEventSequence = @NewEventSequence + 1
SELECT @NewRowNumber = @NewRowNumber + 1
END
SELECT --@PreviousRow_RowNumber = @RowNumber,
@PreviousRow_StartTime = @StartTime,
@PreviousRow_EndTime = @EndTime
INSERT INTO Tracedata_from_SQL2005_Profiler_001_with_Inverval ( -- Change the new trace data table
[RowNumber]
,[EventClass]
,[EventSequence]
,[TextData]
,[Duration]
,[CPU]
,[Reads]
,[Writes]
,[ApplicationName]
,[LoginName]
,[DatabaseName]
,[DatabaseID]
,[ClientProcessID]
,[HostName]
,[ServerName]
,[BinaryData]
,[SPID]
,[StartTime]
,[EndTime]
,[IsSystem]
,[NTDomainName]
,[NTUserName]
,[Error]
)
VALUES (
@NewRowNumber,
@EventClass,
CASE WHEN (@EventSequence is NULL) THEN @EventSequence
ELSE @NewEventSequence
END, -- New Sequence Number
@TextData,
@Duration,
@CPU,
@Reads,
@Writes,
@ApplicationName,
@LoginName,
@DatabaseName,
@DatabaseID,
@ClientProcessID,
@HostName,
@ServerName,
@BinaryData,
@SPID,
@StartTime,
@EndTime,
@IsSystem,
@NTDomainName,
@NTUserName,
@Error
)
SELECT @NewEventSequence = @NewEventSequence + 1
SELECT @NewRowNumber = @NewRowNumber + 1
FETCH NEXT FROM TraceEvent_cursor INTO
@RowNumber,
@EventClass,
@EventSequence,
@TextData,
@Duration,
@CPU,
@Reads,
@Writes,
@ApplicationName,
@LoginName,
@DatabaseName,
@DatabaseID,
@ClientProcessID,
@HostName,
@ServerName,
@BinaryData,
@SPID,
@StartTime,
@EndTime,
@IsSystem,
@NTDomainName,
@NTUserName,
@Error
END
CLOSE TraceEvent_cursor
DEALLOCATE TraceEvent_cursor