📊 Reading SQL Profiler Trace Files — And Why You Should Start Using Extended Events

SQL Server troubleshooting and performance tuning often require detailed insight into what’s happening under the hood. Historically, SQL Profiler and trace files were the go-to tools for this — and while they still have a place in the toolbox, there are now better, more modern ways to do deep diagnostics.

In this post, we’ll walk through:

How to work with SQL trace files.
How to analyze traces using T-SQL.
Combining Profiler traces with PerfMon.
And most importantly: why Extended Events are the future of SQL diagnostics.

🧰 Part 1: Working with SQL Profiler Trace Files

SQL Profiler allows you to trace and record database activity like queries, login attempts, lock escalations, etc. You can export this data into a .trc file and analyze it later.

📁 Opening Trace Files

In SQL Profiler:

Go to File → Open → Trace File.
If your trace has multiple rollover files, Profiler will ask to open the next file when it reaches the end.

💡 Pro Tip: You can also replay traces using Replay, which even supports breakpoints to isolate problematic areas.

🧮 Querying Trace Files with T-SQL

You can also read .trc files directly in T-SQL using the system function fn_trace_gettable:

SELECT *
FROM fn_trace_gettable('C:\MyTraceFile.trc', DEFAULT);

First parameter: Full path to the trace file.
Second parameter: Number of rollover files. Use DEFAULT or -1 for all files, or specify 1 to just read the first file.

📋 Importing to a Table

Rather than reading from the file every time (which is slow), import the trace into a table:

SELECT IDENTITY(BIGINT, 1, 1) AS RowNumber, *
INTO MyTraceTable
FROM fn_trace_gettable('C:\MyTraceFile.trc', DEFAULT)
WHERE 1 = 0;  -- Just create the structure

Then populate it:

INSERT INTO MyTraceTable
SELECT *
FROM fn_trace_gettable('C:\MyTraceFile.trc', DEFAULT);

Now you can index, filter, and analyze the data however you like.

📈 Combining SQL Profiler with PerfMon

To correlate system performance (CPU, memory, disk IO) with SQL activity:

Open the .trc file in SQL Profiler.
Go to File → Import Performance Data…
Choose a corresponding .BLG or .CSV PerfMon log file.
Select the counters you care about and view the correlation in the timeline.

📌 This helps you correlate slow SQL activity with spikes in system usage.

🔄 But Times Have Changed: Enter Extended Events (XE)

SQL Profiler and traditional traces are still useful — but Extended Events are the modern, more efficient, and more powerful replacement.

💡 What Are Extended Events?

Extended Events is a lightweight performance monitoring system built into SQL Server (since 2008) that captures:

Query execution
Waits
Deadlocks
Blocking
Errors

Think of it like a smarter, faster SQL Profiler.

✅ Why Use Extended Events Over Traces?

Feature	SQL Profiler / Traces	Extended Events
Performance Impact	High	Low
Filtering	Limited	Very powerful
UI	Legacy (Profiler)	Modern (SSMS UI + XML)
Server Support	Deprecated on Azure	Fully supported
Exporting to files	Yes	Yes
Built-in events	Fewer	More

🏗 How to Get Started with XE

1. Use a Built-in Session

SELECT *
FROM sys.dm_xe_session_targets t
JOIN sys.dm_xe_sessions s
  ON t.event_session_address = s.address;

2. Create Your Own Extended Events Session

CREATE EVENT SESSION LongRunningQueries
ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    WHERE (duration > 1000000)  -- Duration in microseconds
)
ADD TARGET package0.ring_buffer;
GO

ALTER EVENT SESSION LongRunningQueries ON SERVER STATE = START;

Query the XE Output:

SELECT
    event_data.value('(event/@name)[1]', 'VARCHAR(100)') AS EventName,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'BIGINT') AS Duration,
    event_data.value('(event/data[@name="statement"]/value)[1]', 'NVARCHAR(MAX)') AS SQLText
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.dm_xe_sessions AS s
    JOIN sys.dm_xe_session_targets AS t
      ON s.address = t.event_session_address
    WHERE s.name = 'LongRunningQueries'
) AS Data;

🔍 When Should I Still Use SQL Profiler?

Let’s be real — a lot of us still fall back on SQL Profiler. And sometimes that’s okay.

✅ Use Profiler when:

You need quick real-time visibility.
You're working with legacy systems that already use trace files.
You want a visual timeline of events fast.

🚫 But avoid it for production monitoring. Microsoft has deprecated it, and it’s not supported in Azure SQL.

🧠 Summary

Tool	Use For
SQL Profiler	Quick debugging, legacy support
Trace Files	Exporting data for offline investigation
fn_trace_gettable	Importing trace into T-SQL for analysis
PerfMon Integration	Correlating system + SQL performance
Extended Events	Modern, lightweight, detailed diagnostics

🚀 Final Thoughts

While SQL Profiler and trace files still have a role to play, the future lies in Extended Events. They’re faster, more flexible, and scale better — especially in cloud and hybrid environments.

If you’re just getting started with tracing and diagnostics, keep experimenting with both — but start learning XE now. It’ll pay off massively down the line.

Need a downloadable version of the scripts above? Let me know and I can generate a ready-to-use SQL script or XE session template file for you.

Search This Blog