/*
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].[TraceExecutionsReport]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[TraceExecutionsReport] ;
GO


CREATE PROCEDURE TraceExecutionsReport   
(  
  @Rows int = 200  
 ,@StartDate smalldatetime  
 ,@EndDate smalldatetime  
)  
/*
	Yaniv Etrogi
	http://www.sqlserverutilities.com	
	Retrieve the pre aggregated trace data.
*/
AS  
SET NOCOUNT ON;   
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  


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

  

-- 	Retrieve the pre aggregated trace data.
SELECT TOP(@Rows)   
  [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 [dbo].[BaseLine]   
WHERE InsertTime BETWEEN @StartDate AND @EndDate  
ORDER BY Total_Duration_ss DESC;  
GO