Finding Problem Queries with SQL Profiler - Professional SQL Server 2005 Performance Tuning - page 129

Figure 5-30
QueryingTraceDataDirectly
If it is not necessary to save the trace data to a database table, you can directly query against a set of
rollover trace files. With the NTEXT datatype issue, you can use a T-SQL CAST function to convert the
TextData data from NTEXT to NVARCHAR . The following query uses the fn_trace_gettable function to
return data directly from the trace output file. Figure 5-31 shows the query result.
SELECT
COUNT(*) AS NumberOfExecutions,
CAST (TextData as nvarchar(max)) AS TextData,
SUM(Duration)/1000 AS Total_Duration_In_MilliSeconds,
SUM(CPU) AS Total_CPU_Time_In_MilliSeconds,
SUM(Reads) AS Total_Logical_Reads,
SUM(Writes) AS Total_Writes
FROM ::fn_trace_gettable('c: \ temp \ myServerSideTraceOutput_CostlyQueries
.trc', default)
WHERE TextData IS NOT NULL
GROUP BY
CAST (TextData as nvarchar(max))
ORDER BY Total_Duration_In_MilliSeconds DESC