sql-server-2008filtertracesql-server-profiler

Is there a way to filter a SQL Profiler trace?


I'm trying to troubleshoot this problem using SQL Profiler (SQL 2008).
After days of running the trace in production, the error happened again and I'm diagnosing it.

The problem is that the trace has 400k rows, 99.9% of which are coming from "Report Server".
I don't even know why it's on, but it seems to be pinging SQL Server every second...


Solution

  • 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.