Is there a way to filter a SQL Profiler trace?

Question

I'm trying to troubleshoot this problem using SQL Profiler (SQL 2008)

After a few days running the trace in production, finally the error happened again, and now i'm trying to diagnose the cause. The problem is that the trace has 400k rows, 99.9% of which are coming from "Report Server", which I don't even know why it's on, but it seems to be pinging SQL Server every second...

Is there any way to filter out some records from the trace, to be able to look at the rest?
Can I do this with the current .trc file, or will I have to run the trace again?
Are there other applications to look at the .trc file that can give me this functionality?

Answer 1

You can load a captured trace into SQL Server Profiler: Viewing and Analyzing Traces with SQL Server Profiler.

Or you can load into a tool like ClearTrace (free version) to perform workload analysis.

You can load into a SQL Server table, like so:

SELECT * INTO TraceTable
FROM ::fn_trace_gettable('C:\location of your trace output.trc', default)

Then you can run a query to aggregate the data such as this one:

SELECT 
  COUNT(*) AS TotalExecutions,     
  EventClass, 
  CAST(TextData as nvarchar(2000)) ,
  SUM(Duration) AS DurationTotal ,
  SUM(CPU) AS CPUTotal ,
  SUM(Reads) AS ReadsTotal ,
  SUM(Writes) AS WritesTotal
FROM 
  TraceTable
GROUP BY 
  EventClass, 
  CAST(TextData as nvarchar(2000))
ORDER BY 
  ReadsTotal DESC

Also see: MS SQL Server 2008 - How Can I Log and Find the Most Expensive Queries?

It is also common to set up filters for the captured trace before starting it. For example, a commonly used filter is to limit to only events which require more than a certain number of reads, say 5000.