--------------------------------------------------------------------------------
--  File        :   Demo 03_Summarize a workload,2005+.sql
--
--  Purpose     :   This script summarizes a workload (when stored procedures 
--                  are used) via the RPC:Completed event class: 
--                      "RPC:Completed Event Class" <http://msdn.microsoft.com/en-us/library/ms175543.aspx>
--
--  SQL Server  :   SQL Server 2005 (9.0)
--                  SQL Server 2008 (10.0)
--                  SQL Server 2008 R2 (10.5)
--
--  Version     :   1.0005.0090.0000 (2010-02-02)
--------------------------------------------------------------------------------

USE [tempdb];

GO

SET NOCOUNT ON;

--  An arbitrary date used for rounding the start time values.
DECLARE @ReferenceDate DateTime;

--  The path to the trace file whose events should be summarized.
DECLARE @TraceFilePath NVarChar(260);

SET @ReferenceDate = '1900-01-01';

--  The following value uses SQLCmd variable substitution.  Alternately, you 
--  can:
--      1.  Comment out line 34
--      2.  Uncomment line 35
--      3.  Replace the string value with the appropriate file path.
SET @TraceFilePath = N'$(TraceFilePath)';
--SET @TraceFilePath = N'C:\Temp\YourTraceFile.trc';

--  Summarize the RPC:Completed events within the specified trace file.
SELECT DateAdd(hour, DateDiff(hour, @ReferenceDate, [StartTime]), @ReferenceDate) AS [RoundedEventTime],
    [_Events].[DatabaseID],
    [_Events].[DatabaseName],
    [_Events].[ObjectID],
    [_Events].[ObjectName],
    Count(*) AS [ExecutionCount],
    Sum([Duration]) AS [Duration_Total],
    Min([Duration]) AS [Duration_Minimum],
    Avg([Duration]) AS [Duration_Average],
    Max([Duration]) AS [Duration_Maximum],
    Sum([CPU]) AS [CPU_Total],
    Min([CPU]) AS [CPU_Minimum],
    Avg([CPU]) AS [CPU_Average],
    Max([CPU]) AS [CPU_Maximum],
    Sum([Reads]) AS [Reads_Total],
    Min([Reads]) AS [Reads_Minimum],
    Avg([Reads]) AS [Reads_Average],
    Max([Reads]) AS [Reads_Maximum],
    Sum([Writes]) AS [Writes_Total],
    Min([Writes]) AS [Writes_Minimum],
    Avg([Writes]) AS [Writes_Average],
    Max([Writes]) AS [Writes_Maximum],
    Sum([RowCounts]) AS [RowCounts_Total],
    Min([RowCounts]) AS [RowCounts_Minimum],
    Avg([RowCounts]) AS [RowCounts_Average],
    Max([RowCounts]) AS [RowCounts_Maximum]
FROM [sys].[fn_trace_gettable](
    @TraceFilePath,
    DEFAULT
) AS [_Events]
WHERE (
    ([_Events].[EventClass] = 10)                           --  RPC:Completed
    AND
    ([_Events].[DatabaseID] BETWEEN 2 AND 32766)            --  All but the 
                                                            --  [master] and 
                                                            --  [mssqlsystemresource] 
                                                            --  system databases.
)
GROUP BY DateAdd(hour, DateDiff(hour, @ReferenceDate, [StartTime]), @ReferenceDate),
    [_Events].[DatabaseID],
    [_Events].[DatabaseName],
    [_Events].[ObjectID],
    [_Events].[ObjectName]
ORDER BY [RoundedEventTime] ASC,
    [CPU_Total] DESC,
    [ExecutionCount] DESC;

GO
