Is there a way to filter a SQL Profiler trace?

Question

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

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

Ramiroramjet

Great question with some great answers. Thank you, – Stercoricolous

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.

Phosphor

hey Mitch where did you get functions like this. can you suggest me the source for this and similar functions? – Debouch

various sources including first link above, blogs and books. Many of the SQL Server MVPs' blog contain useful info. – Phosphor

If you get access denied: copy the trc file to this folder "c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\", it helped me. – Tellurian

The link no longer work, but this might be what you need: scalesql.com/cleartrace/download – Yamen

link updated... – Phosphor

Answer 2

Load the .trc locally and then Use save to database to local db and then query to your hearts content.

Dellinger

Answer 3

These suggestions are great for an existing trace - if you want to filter the trace as it occurs, you can set up event filters on the trace before you start it.

The most useful filter in my experience is application name - to do this you have to ensure that every connection string used to connect to your database has an appropriate Application Name value in it, ie:

"...Server=MYDB1;Integrated Authentication=SSPI;Application Name=MyPortal;..."

Then in the trace properties for a new trace, select the Events Selection tab, then click Column Filters...

Select the ApplicationName filter, and add values to LIKE to include only the connections you have indicated, ie using MyPortal in the LIKE field will only include events for connections that have that application name.

This will stop you from collecting all the crud that Reporting Services generates, for example, and make subsequent analysis a lot faster.

There are a lot of other filters available as well, so if you know what you are looking for, such as long execution (Duration) or large IO (Reads, Writes) then you can filter on that as well.

Dumyat

Answer 4

Since SQL Server 2005, you can filter a .trc file content, directly from SQL Profiler; without importing it to a SQL table. Just follow the procedure suggested here:

http://msdn.microsoft.com/en-us/library/ms189247(v=sql.90).aspx

An additional hint: you can use '%' as a filter wildcard. For instance, if you want to filter by HOSTNAME like SRV, then you can use SRV%.

Rixdollar

Answer 5

Here you can find a complete script to query the default trace with the complete list of events you can filter:

http://zaboilab.com/sql-server-toolbox/anayze-sql-default-trace-to-investigate-instance-events

You have to query sys.fn_trace_gettable(@TraceFileName,default) joining sys.trace_events to decode events numbers.

Alphonsealphonsine