/*
EXEC PerfDB.dbo.TraceExecutionsDelete
EXEC PerfDB.dbo.TraceExecutionsStart @Minutes = 60, @Path = N'B:\Traces\BaseLine' 
EXEC PerfDB.dbo.TraceExecutionsLoad @Path = N'B:\Traces\BaseLine.trc' 
EXEC PerfDB.dbo.TraceExecutionsManipulateData @Rows = 200
EXEC PerfDB.dbo.TraceExecutionsReport @StartDate = '20090125', @EndDate = '20090127 23:59', @Rows = 200

--DELETE FROM [BaseLine] WHERE InsertTime BETWEEN '20090928' AND '20090930'
*/


USE PerfDB; 

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TraceExecutionsManipulateData]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[TraceExecutionsManipulateData] ;
GO

CREATE PROCEDURE TraceExecutionsManipulateData 
(
	@Rows int = 200
)
/*
	Yaniv Etrogi
	http://www.sqlserverutilities.com	
	Manipulates the raw trace data loaded from the trace files to to table BaseLineFull and inserts 
	the manipulated data to table BaseLine making it available for retreival.
*/
AS
SET NOCOUNT ON; 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


--EXEC PerfDB.dbo.TraceExecutionsManipulateData @Rows = 100


-- Get the time period of the trace data that was captured.
DECLARE @StartTime smalldatetime, @EndTime smalldatetime;
SELECT @StartTime = MIN(StartTime), @EndTime = MAX(StartTime) FROM dbo.BaseLineFull;


-- If the table does not exist ctreate it.
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BaseLine]') AND type in (N'U'))
BEGIN;
SET QUOTED_IDENTIFIER ON; SET ANSI_PADDING ON;
CREATE TABLE [dbo].[BaseLine]
(
	[InsertTime] [smalldatetime] CONSTRAINT [DF_BaseLine_InsertTime]  DEFAULT (getdate()) ,
	[StartTime] [smalldatetime],
	[EndTime] [smalldatetime],	
	[Database] [varchar](128) NULL,
	[EventClass] [varchar](5) NULL,
	[Statment] [varchar](max) NULL,
	[Executions] [int] NULL,
	[Total_Duration_ss] [bigint] NULL,
	[AVG_Duration_ss] [decimal](10, 3) NULL,
	[Total_Reads] [bigint] NULL,
	[AVG_Reads] [int] NULL,
	[Total_Writes] [bigint] NULL,
	[AVG_Writes] [int] NULL,
	[Total_CPU_ss] [bigint] NULL,
	[AVG_CPU_ss] [decimal](10, 3) NULL,
	[Total_RowCount] [bigint] NULL,
	[AVG_RowCount] [int] NULL
) ON [PRIMARY];
SET ANSI_PADDING OFF;
END;


INSERT [dbo].[BaseLine]	
(
	 StartTime
	,EndTime
	,[Database]
	,EventClass
	,Statment
	,Executions
	,Total_Duration_ss
	,AVG_Duration_ss
	,Total_Reads
	,AVG_Reads
	,Total_Writes
	,AVG_Writes
	,Total_CPU_ss
	,AVG_CPU_ss
	,Total_RowCount
	,AVG_RowCount			
)
SELECT 
	 @StartTime
	,@EndTime
	,[Database]
	,EventClass
	,Statment
	,Executions
	,Total_Duration_ss
	,AVG_Duration_ss
	,Total_Reads
	,AVG_Reads
	,Total_Writes
	,AVG_Writes
	,Total_CPU_ss
	,AVG_CPU_ss
	,Total_RowCount
	,AVG_RowCount			
 FROM 
(
	SELECT 
		 RANK() OVER (PARTITION BY Statment ORDER BY Total_Duration_ss DESC,EventClass) AS [Rank]
		,[Database]
		,EventClass
		,Statment
		,Executions
		,Total_Duration_ss
		,AVG_Duration_ss
		,Total_Reads
		,AVG_Reads
		,Total_Writes
		,AVG_Writes
		,Total_CPU_ss
		,AVG_CPU_ss
		,Total_RowCount
		,AVG_RowCount					
			FROM 
			(
				SELECT TOP (@Rows)
					 [Database]
					,CASE WHEN EventClass = 10 THEN 'RPC' WHEN EventClass = 12 THEN 'BATCH' WHEN EventClass = 43 THEN 'SP' END AS EventClass 
					,CASE WHEN BaseLineFull.TextData IS NULL THEN [Object] ELSE dbo.sqlsig(BaseLineFull.TextData) END AS Statment
					,COUNT(*)					AS Executions
					,SUM(Duration) /1000000 AS Total_Duration_ss
					,CAST ((SUM(Duration) /1000000) / CAST(COUNT(*) AS decimal) AS decimal(10,3)) AS AVG_Duration_ss						
					,SUM(ISNULL(Reads, 0))								AS Total_Reads
					,SUM(ISNULL(Reads, 0)) / COUNT(*)			AS AVG_Reads
					,SUM(ISNULL(Writes, 0))								AS Total_Writes
					,SUM(ISNULL(Writes, 0))	/ COUNT(*)		AS AVG_Writes
					,SUM(ISNULL(CPU, 0)) /1000						AS Total_CPU_ss								
					,CAST((SUM(ISNULL(CPU, 0)) /1000) / CAST(COUNT(*)	AS decimal)	AS decimal(10,3))		AS AVG_CPU_ss										
					,SUM(ISNULL(RowCounts, 0)) 						AS Total_RowCount
					,SUM(ISNULL(RowCounts, 0)) / COUNT(*)	AS AVG_RowCount
			FROM dbo.BaseLineFull 
			GROUP BY 
				 CASE WHEN EventClass = 10 THEN 'RPC' WHEN EventClass = 12 THEN 'BATCH' WHEN EventClass = 43 THEN 'SP' END
				,[Database]
				,CASE WHEN BaseLineFull.TextData IS NULL THEN [Object] ELSE dbo.sqlsig(BaseLineFull.TextData) END
			ORDER BY Total_Duration_ss DESC
		)Derived2
)Derived1
WHERE [Rank] = 1
ORDER BY Total_Duration_ss DESC ;
GO