SQL Server Profiler and Trace Analysis

In this lesson, you will learn how to capture and analyze SQL Server workload activity using
SQL Server Profiler and Trace concepts. The goal is not to “collect everything.”
The goal is to collect the right evidence to answer performance questions like:
“Which query is slow?”, “What is causing timeouts?”, “What is running at 9:00 AM?”, and
“Which procedure consumes most CPU/reads?”

Important reality: In modern SQL Server troubleshooting, Query Store and Extended Events
are usually preferred for ongoing monitoring. However, Profiler/Trace knowledge is still valuable because:
(1) you will encounter older systems, (2) you may inherit existing trace scripts, and
(3) you must understand the difference between “useful capture” and “dangerous capture.”

Class Overview (What students will learn today)

You will learn:

What Profiler and Trace are (and what they are not).
How tracing helps you troubleshoot performance and user complaints.
How to choose the right events and filters (so you don’t overload production).
How to interpret trace output: duration vs CPU vs reads vs writes vs wait behavior.
How to correlate trace data with other evidence (DMVs, waits, I/O latency, blocking).
How to produce a simple “trace report” that leads to a real fix.

Recommended Class Duration

120–180 minutes (lecture + demo + trace-reading lab)

Prerequisites

Basic SQL knowledge
Understanding of performance metrics (duration, CPU, reads, blocking)
Optional but helpful: familiarity with Query Store and DMVs

Learning Objectives (By the end of this class, students can…)

Explain when tracing is useful and when it is risky.
Capture a focused workload sample without collecting unnecessary noise.
Apply filters to reduce risk (duration threshold, database, application, login, host).
Read trace results and identify top contributors by duration, CPU, and reads.
Distinguish “slow because of work” vs “slow because of waiting/blocking.”
Write a short root-cause summary and propose next steps (index, query rewrite, schedule, blocking fix).

Part 1 — What Profiler / Trace Actually Captures (10–15 min)

A trace is a stream of recorded events. Each event represents something that happened inside SQL Server
(a batch executed, a stored procedure completed, a login occurred, a deadlock happened, etc.).
Tracing helps you answer: What happened? When? Who did it? and
How expensive was it?

The most useful “cost” columns

Duration: total time for the event (can include waiting and blocking)
CPU: CPU time used (how much compute work)
Reads (logical reads): memory page reads (often the best indicator of query “workload”)
Writes: writes performed (useful for write-heavy operations)
RowCount: rows returned or affected (can explain long work)
TextData / RPC / batch text: what ran (careful: don’t store sensitive data)
DatabaseName / LoginName / HostName / ApplicationName: who/where it came from

Key teaching point: A query can have long duration with low CPU because it is waiting (I/O or locks).
Traces show you the “what,” but you still need DMVs/waits/blocking data for the “why.”

Part 2 — Profiler vs Server-Side Trace (And Why Filtering Matters) (15–25 min)

Profiler is a GUI that can capture events and display them live. “Trace” is the underlying mechanism to capture events.
The problem is not tracing itself—the problem is tracing too much with no filters, which can add overhead.

Rules for safe tracing

Trace as little as possible: start with the minimum events you need.
Always filter: by database, application, login, host, or duration.
Avoid long “live” GUI traces in production: capture short samples with clear time windows.
Capture to file: if possible (reduces risk of UI overhead and preserves evidence).
Do not collect sensitive data: avoid capturing full parameters if they may contain private information.

What “good filters” look like

Duration threshold: capture only events slower than X milliseconds
Target one database: reduce noise and risk
Target one application: isolate a specific API or reporting tool
Target one login/host: reproduce a user complaint
Short window: 1–5 minutes during the incident

Part 3 — Which Events to Capture (The Practical Starter Set) (20–30 min)

Beginners often capture too many events. That creates noise and increases overhead. Start with a small event set that answers:
“What query is slow and how expensive is it?”

Recommended “Starter” event set (common troubleshooting)

RPC:Completed — stored procedure executions (common in apps)
SQL:BatchCompleted — ad-hoc SQL batches
Attention — queries canceled/timeouts (useful for diagnosing timeouts)
Errors and Warnings (selected) — timeouts, severity warnings, etc.
Deadlock graph (if investigating deadlocks)

Columns you typically want

TextData (or the statement text)
Duration, CPU, Reads, Writes, RowCount
DatabaseName
LoginName, HostName, ApplicationName
SPID (session id) if available
StartTime, EndTime

Key teaching point: If your goal is “find slow queries,” capture completion events (Completed),
not every internal step. Completed events let you rank by duration/CPU/reads.

Part 4 — How to Read Trace Data (Turn Events into Decisions) (25–40 min)

Capturing a trace is easy. The real DBA skill is reading the data correctly.
When you look at trace results, you want to answer three questions:
(1) What is the top workload? (2) Is it expensive because of work or waiting?
(3) What is the next action?

Step-by-step trace analysis method

Group by application/database: confirm you captured the correct workload.
Sort by Duration: identify the slowest events (user pain).
Sort by Reads: identify the “biggest workload” queries (often the best tuning targets).
Sort by CPU: identify compute-heavy queries (joins, sorts, functions).
Check RowCount: large row outputs can explain long runtime.
Compare Duration vs CPU: if duration is high but CPU is low, suspect waiting (I/O or locks).
Decide next tool: use DMVs, waits, blocking scripts, or execution plan analysis next.

Interpretation examples (student-friendly)

High Reads + High Duration: likely scanning too much data → index/query rewrite candidate.
High CPU + Moderate Reads: CPU-heavy operations (joins/sorts/UDFs) → execution plan review.
High Duration + Low CPU + Low Reads: likely waiting (blocking or I/O latency) → check blocking/waits.
Many repeated events (high execution count): “death by 1,000 cuts” → optimize frequent small queries.

Part 5 — Practical Use Cases (When Profiler/Trace Helps Most) (20–30 min)

Use Case 1: Identify what is running during a “slow window”

Users report “everything is slow at 9:00 AM.” A short trace during that window can show exactly what workload begins at 9:00:
a report, ETL job, index rebuild, or a single expensive stored procedure.

Use Case 2: Find timeout / canceled queries

Capture Attention events and correlate with slow queries. This helps identify what users are timing out on.

Use Case 3: Detect deadlocks (and capture deadlock graphs)

Deadlocks can be intermittent and hard to reproduce. Capturing deadlock graph events gives you the evidence needed
to fix transaction ordering or indexing issues.

Use Case 4: Validate workload after deployment

After a release, you can capture a small sample and confirm whether new queries are heavier than expected
(CPU/reads increased, new expensive procedures introduced).

Part 6 — Safe “Server-Side Trace” Template (Read-Only Capture Approach) (20–35 min)

Below is a training-only server-side trace template that demonstrates the right mindset:
capture only completion events, filter aggressively, and write to a file. In real production environments, many teams
prefer Extended Events—but you must understand this trace pattern because it exists in many organizations.

Safety note: Do not run tracing on production without approval and a monitoring window.
Always keep the trace short and targeted.

/* ============================================================
   Training Trace Template (Server-side trace)
   - Captures RPC:Completed and SQL:BatchCompleted
   - Uses a duration filter (milliseconds)
   - Writes to a trace file
   - For LAB / TRAINING usage
============================================================ */

-- You will need appropriate permissions to run server-side traces.
-- Replace the file path with a valid path on your server.

DECLARE @TraceId INT;
DECLARE @File NVARCHAR(260) = N'C:\Temp\SQLPT_SlowQueries'; -- no extension; SQL adds .trc
DECLARE @MaxFileSize BIGINT = 50; -- MB
DECLARE @StopTime DATETIME = DATEADD(MINUTE, 5, GETDATE()); -- short capture window

-- Create trace
EXEC sp_trace_create
     @traceid = @TraceId OUTPUT,
     @options = 2,                -- 2 = trace file rollover
     @tracefile = @File,
     @maxfilesize = @MaxFileSize,
     @stoptime = @StopTime;

-- Events:
-- 10 = RPC:Completed
-- 12 = SQL:BatchCompleted

-- Columns (selected):
-- 1=TextData, 11=LoginName, 8=HostName, 10=ApplicationName, 35=DatabaseName
-- 13=Duration, 15=EndTime, 14=StartTime, 16=Reads, 17=Writes, 18=CPU, 48=RowCounts

-- RPC:Completed (10)
EXEC sp_trace_setevent @TraceId, 10, 1, 1;
EXEC sp_trace_setevent @TraceId, 10, 11, 1;
EXEC sp_trace_setevent @TraceId, 10, 8, 1;
EXEC sp_trace_setevent @TraceId, 10, 10, 1;
EXEC sp_trace_setevent @TraceId, 10, 35, 1;
EXEC sp_trace_setevent @TraceId, 10, 13, 1;
EXEC sp_trace_setevent @TraceId, 10, 14, 1;
EXEC sp_trace_setevent @TraceId, 10, 15, 1;
EXEC sp_trace_setevent @TraceId, 10, 16, 1;
EXEC sp_trace_setevent @TraceId, 10, 17, 1;
EXEC sp_trace_setevent @TraceId, 10, 18, 1;
EXEC sp_trace_setevent @TraceId, 10, 48, 1;

-- SQL:BatchCompleted (12)
EXEC sp_trace_setevent @TraceId, 12, 1, 1;
EXEC sp_trace_setevent @TraceId, 12, 11, 1;
EXEC sp_trace_setevent @TraceId, 12, 8, 1;
EXEC sp_trace_setevent @TraceId, 12, 10, 1;
EXEC sp_trace_setevent @TraceId, 12, 35, 1;
EXEC sp_trace_setevent @TraceId, 12, 13, 1;
EXEC sp_trace_setevent @TraceId, 12, 14, 1;
EXEC sp_trace_setevent @TraceId, 12, 15, 1;
EXEC sp_trace_setevent @TraceId, 12, 16, 1;
EXEC sp_trace_setevent @TraceId, 12, 17, 1;
EXEC sp_trace_setevent @TraceId, 12, 18, 1;
EXEC sp_trace_setevent @TraceId, 12, 48, 1;

-- Filter: only capture events longer than 500ms
-- NOTE: Trace Duration is in microseconds, so 500ms = 500,000 microseconds
DECLARE @DurationMicroseconds BIGINT = 500000;
EXEC sp_trace_setfilter @TraceId, 13, 0, 4, @DurationMicroseconds;

-- Start trace
EXEC sp_trace_setstatus @TraceId, 1;

-- To stop early (optional):
-- EXEC sp_trace_setstatus @TraceId, 0;  -- stop
-- EXEC sp_trace_setstatus @TraceId, 2;  -- close and delete definition

What this teaches: Filter by duration, capture only completion events, keep the window short, and write to file.
This is how you reduce overhead and still get valuable evidence.

Part 7 — Turning Trace Output into a “Top Queries” Report (15–25 min)

Your trace results should end with a simple report. For example:
Top 10 by Duration, Top 10 by Reads, and Top 10 by CPU.
Then you decide next actions (execution plan review, indexing, stats, blocking checks).

What students should include in a trace report

Capture window (start/end time)
Filters used (duration threshold, database/application)
Top 5–10 events by Duration, CPU, and Reads
One paragraph explanation for each top event: “work vs waiting”
Next step recommendation (what to analyze next)

Student Lab (45–75 min) — “Focused Trace + Evidence Summary”

Lab Outcome

Students will capture a short workload sample (lab environment) and produce a trace analysis summary with tuning next steps.

Lab Steps

Run a short capture (2–5 minutes) during a time you execute queries in your lab.
Use a duration threshold filter (example: 200ms or 500ms).
Export the trace results (or save output) and identify top events by Duration, CPU, and Reads.
Write a short explanation for each: “expensive because of work” or “expensive because of waiting.”
Suggest one next step per top event (index review, stats review, plan review, blocking check).

Student Submission Template (Copy/Paste)

Capture Window:
Filters used:
Top 5 by Duration:
Top 5 by Reads:
Top 5 by CPU:
For each top event: Work vs Waiting + Next step recommendation
What you would check next using DMVs:

Common Beginner Mistakes (Profiler/Trace)

Capturing too many events and columns (noise + overhead).
Running Profiler live on production for long periods with no filters.
Not filtering by duration or database (captures everything and hides what matters).
Assuming Duration equals “CPU work” (duration often includes waiting/blocking).
Collecting sensitive TextData without approval or masking strategy.
Not correlating trace findings with waits, I/O latency, and blocking evidence.

Class Discussion Prompt

Prompt:
“You captured a trace and found one query with Duration = 20 seconds, CPU = 200 ms, Reads = 500.
What does that pattern suggest? What would you check next to confirm the real cause?”

Homework Assignment

Create a “Trace Plan” for a production incident (fictional): which events, which columns, which filters, how long, and why.
Write a 10–15 sentence policy for safe tracing (overhead risk, privacy risk, short windows, approval).
Explain how you would validate a tuning change using trace data + one additional evidence source (Query Store or DMVs).

Quick Knowledge Check (12 questions)

What is the purpose of tracing in SQL Server troubleshooting?
Why is filtering essential for safe tracing?
Which completion events are commonly used to find slow queries?
Name four “cost” columns that help identify expensive queries.
Why can duration be high while CPU is low?
What is the value of capturing ApplicationName/HostName/LoginName?
What are the risks of running Profiler live on production?
What is a good duration threshold strategy for traces?
Why should traces usually be short?
What is a deadlock graph and when would you capture it?
What should a trace analysis report contain?
Which tools should you combine with trace data to confirm root cause?

Closing (What’s next)

In the next lesson, we will cover Statistics and Cardinality Estimation in SQL Server.
You will learn why the optimizer chooses certain execution plans, how wrong estimates lead to bad plans,
and how statistics influence indexing, memory grants, and overall performance stability.

SQL Server Profiler and Trace Analysis