Read MSSQL 2005 trace Table

while working on a major project, I was assigned to track database activity. Objective was to find long running queries. It seen easy but i was not so proficient in MSSQL profiler :(

I have created a template and applied dbname and userName filter as required.
Performance

• Performance Statistics
• ShowPlanAll
• ShowPlanXML

TSQL

• SQL batch completed
• Sql batchStarting
• Sql StmtCompleted
• Sql StmtStarting

After running trace,Now I have data in my table ,next step is to query that data.I found it is difficult for new user to extract data.So I thought why not to share it with others.


--Definitation of column used in query
--CPU:     Amount of CPU time (in milliseconds) used by the event.But when store to table/file it will be
--10^3
--Duration:Amount of time (in milliseconds) taken by the event.But when store to table/file it will be
--micro second 10^6
--Writes:Number of physical disk writes performed by the server on behalf of the event.
--Reads:Number of logical disk reads performed by the server on behalf of the event.
--Event Class:Type of event class captured.
--TextData:Text value dependent on the event class captured in the trace. However, if you are tracing a parametrized query, the variables will not be displayed with data values in the TextData column.

--Query to read trace table
SELECT
        COUNT(*) AS TotalExecutions ,
        EventClass ,
        CAST(TextData AS NVARCHAR(4000)) AS query ,
        SUM(Duration) / 1000000 AS DurationTotal_Sec ,
        --SUM(Duration) / 1000000/COUNT(*) AS DurationTotal_per_call_Sec ,
        SUM(CPU)/ 1000 AS CPUTotal_Sec ,
        SUM(Reads) AS ReadsTotal ,
        SUM(Writes) AS WritesTotal
FROM    tmptracetable  -- table that contains the trace results
 --WHERE   eventclass IN ( 12 ) --batch completed
--WHERE   eventclass IN ( 41 ) --stmt completed
GROUP BY EventClass ,
        CAST(TextData AS NVARCHAR(4000))
--un comment required order clause as per your needs
ORDER BY DurationTotal_sec DESC
--Order by ReadsTotal desc
--ORDER BY WritesTotal DESC
--ORDER BY CPUTotal_Sec DESC
--ORDER BY DurationTotal_per_call_Sec DESC

In trace table you will have event class id, if you want to know event name to filter your trace search, use the
following query
-- Query to view event class name
SELECT   DISTINCT
        TE.name ,
        t.eventclass
FROM    tmptracetable T -- table that contains the trace results
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
         --WHERE t.eventclass IN (12,41)
ORDER BY t.eventclass

/*
name    eventclass
SQL:BatchCompleted    12
SQL:BatchStarting    13
SQL:StmtStarting    40
SQL:StmtCompleted    41
Showplan All    97
Showplan XML    122
Performance statistics    165
*/

and rest is ongoing :)