USE master
GO
IF DB_ID('TraceAnalysis') IS NOT NULL
BEGIN
	DROP DATABASE TraceAnalysis
END
GO

CREATE DATABASE TraceAnalysis
GO
USE TraceAnalysis
GO
CREATE TABLE RawTraceData
(Rowid int identity primary key,
 TextData nvarchar(max),
 Duration bigint,
 Reads bigint,
 Writes bigint,
 CPU bigint)
GO
CREATE TABLE ParameterizedTraceData
(Rowid int identity primary key,
 TextData nvarchar(max),
 Duration bigint,
 Reads bigint,
 Writes bigint,
 CPU bigint,
 QueryTemplate nvarchar(max),
 Parameters nvarchar(max)
 )
GO

CREATE PROCEDURE ProcessRawTraceData
AS
BEGIN

DECLARE @SQLText nvarchar(max)
DECLARE @Duration bigint
DECLARE @Reads bigint
DECLARE @Writes bigint
DECLARE @CPU bigint
DECLARE @templatetext nvarchar(max)
DECLARE @parameters nvarchar(max)


DECLARE Process_Cursor CURSOR FOR 
SELECT TextData, Duration, Reads, Writes, CPU
FROM RawTraceData
WHERE TextData IS NOT NULL

OPEN Process_Cursor

FETCH NEXT FROM Process_Cursor 
INTO @SQLText, @Duration, @Reads, @Writes, @CPU

WHILE @@FETCH_STATUS = 0
BEGIN
	
	BEGIN TRY

	EXEC sp_get_query_template 
		@SQLText,
		@templatetext OUTPUT,
		@parameters OUTPUT;
		
	INSERT INTO ParameterizedTraceData (TextData, Duration, Reads, Writes, CPU, QueryTemplate, Parameters)
	VALUES (@SQLText, @Duration, @Reads, @Writes, @CPU, @templatetext, @parameters)
   
   END TRY
   BEGIN CATCH
   END CATCH
   
   -- Get the next statement
	FETCH NEXT FROM Process_Cursor 
	INTO @SQLText, @Duration, @Reads, @Writes, @CPU

END

CLOSE Process_Cursor
DEALLOCATE Process_Cursor

END

GO

INSERT INTO RawTraceData (TextData, Duration, Reads, Writes, CPU)
SELECT 
	CAST(TextData AS NVARCHAR(MAX)) AS TextData, 
	Duration, 
	Reads, 
	Writes, 
	CPU
FROM sys.fn_trace_gettable('C:\SQLDiagTuning\Output\W2K3-SQL2K8_SQLDIAG__sp_trace.trc', 1)
WHERE EventClass IN (41, 45) --SQL:StmtCompleted, SP:StmtCompleted
GO
EXEC ProcessRawTraceData
GO

SELECT 
	QueryTemplate, 
	SUM(Duration) AS Duration, 
	SUM(READS) AS READS, 
	SUM(Writes) AS Writes, 
	SUM(CPU) AS CPU,
	COUNT(*) AS Executions
FROM ParameterizedTraceData
GROUP BY QueryTemplate
ORDER BY Duration DESC
GO

SELECT 
	TextData, 
	SUM(Duration) AS Duration, 
	SUM(READS) AS READS, 
	SUM(Writes) AS Writes, 
	SUM(CPU) AS CPU,
	COUNT(*) AS Executions
FROM ParameterizedTraceData
GROUP BY TextData
ORDER BY Duration DESC
/*
USE master
GO
DROP DATABASE TraceAnalysis

*/

