EXEC helpers.dbo.StartPerfTrace;
GO
DECLARE @bb int;
SELECT top 100000 @bb=ROW_NUMBER() OVER(ORDER BY @@SPID)
FROM syscolumns,syscolumns a
GO 10
EXEC helpers.dbo.StopPerfTrace;

USE [helpers]
GO
/****** Object:  StoredProcedure [dbo].[StartPerfTrace]    Script Date: 02/04/2011 11:59:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[StartPerfTrace] 
AS
DECLARE @TraceID int 
DECLARE @RC int, @on BIT
DECLARE  @Name nvarchar(245)
SELECT
	@Name = 'D:\Dump\' + REPLACE (REPLACE ('PerfTrace' + convert (VARCHAR(10), @@spid) + ' ' + convert (VARCHAR(30), getdate (), 6) + ' ' + convert (VARCHAR(30), getdate (), 8), ' ', ''), ':', '')

EXEC @rc = sp_trace_create @TraceID output, 0, @Name

-- Save the trace ID for later - used in the StopPerfTrace Proc.
IF OBJECT_ID('helpers.dbo.traces') IS NULL
	EXEC('USE helpers; CREATE TABLE traces (spid int not null,traceid int not null);');

INSERT
	helpers.dbo.traces (spid,
						traceid)
VALUES
	(@@SPID,
	 @TraceID);

-- Set the events and data columns you need to capture.
SELECT
	@on = 1;

-- 41 is SQL:StmtCompleted event. 1 is TextData column. 
EXEC sp_trace_setevent @TraceID, 41, 1, @on;  -- TextData
EXEC sp_trace_setevent @TraceID, 41, 13, @on; -- Duration
EXEC sp_trace_setevent @TraceID, 41, 16, @on; -- Reads
EXEC sp_trace_setevent @TraceID, 41, 17, @on; -- Writes
EXEC sp_trace_setevent @TraceID, 41, 18, @on; -- Cpu

EXEC sp_trace_setevent @TraceID, 162, 1, @on; -- User Errors - Can then use RAISERROR to put messages in the trace

DECLARE @pid INT = CONVERT(INT,@@SPID);

-- Set filter to include the current SPID 
EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @pid;
-- Set filter to exclude RAISERROR statements 
-- I use these to add messages to the trace so don't want to see the command being called
EXEC sp_trace_setfilter @TraceID, 1, 0, 7, N'RAISERROR%';

-- Start Trace (status 1 = start)
EXEC @RC = sp_trace_setstatus @TraceID, 1;

USE [helpers]
GO
/****** Object:  StoredProcedure [dbo].[StopPerfTrace]    Script Date: 02/04/2011 12:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[StopPerfTrace]
AS
DECLARE  @TraceID int;

SELECT
	@TraceID = traceid
FROM
	helpers.dbo.traces
WHERE
	spid = @@SPID;

DELETE helpers.dbo.traces
WHERE
	spid = @@SPID;

DECLARE @file nvarchar(247)

-- Populate a variable with the trace_id of the current trace
SELECT
	@file = [path]
FROM
	sys.traces
WHERE
	id = @TraceID

-- First stop the trace. 
EXEC sp_trace_setstatus @TraceID, 0

-- Close and then delete its definition from SQL Server. 
EXEC sp_trace_setstatus @TraceID, 2

-- Save to #tracestats table
SELECT
	row_number () OVER (ORDER BY @@spid) AS RowNumber,
	TextData,
	convert (NUMERIC(14, 3), Duration / 1000) [Duration(millisec)],
	Cpu,
	Reads,
	Writes,
	avg (convert (NUMERIC(14, 3), Duration / 1000)) OVER (PARTITION BY convert (VARCHAR(MAX), TextData)) AS Duration_Avg,
	avg (Cpu) OVER (PARTITION BY convert (VARCHAR(MAX), TextData)) AS Cpu_Avg,
	avg (Reads) OVER (PARTITION BY convert (VARCHAR(MAX), TextData)) AS Reads_Avg,
	avg (Writes) OVER (PARTITION BY convert (VARCHAR(MAX), TextData)) AS Writes_Avg
FROM
	fn_trace_gettable (@file, DEFAULT)
WHERE
	TextData IS NOT NULL;