TRACEWRITE Wait Type in SQL Server

---

TRACEWRITE is recorded by the SQL Trace rowset provider while it waits for a free trace buffer, or for a buffer containing events to hand to the consumer. The rowset provider is the path used when a client consumes trace events live, which in practice means SQL Server Profiler or an application reading a trace over the wire.

The wait grows when trace events are produced faster than the consumer takes them away, so it is effectively a meter of tracing overhead.

Is It a Problem?

Yes, when it is significant, because the rowset provider can throttle the server to avoid losing events: sessions generating traced events slow down while buffers are full. The classic incident is someone running Profiler against a busy production server with broad event selections, and the whole workload getting sluggish until the Profiler window closes.

Small amounts from the default trace are normal and harmless.

Common Causes

• A live Profiler session capturing high-frequency events (statement-level events especially) on a busy server.
• A slow trace consumer, Profiler over a WAN link, or an application reading trace data lazily.
• Server-side traces with wide event coverage on high-throughput systems.
• Forgotten traces left running long after the investigation ended.

What To Do

1. See what traces exist: SELECT * FROM sys.traces; shows every running trace, its target, and whether it is the rowset (live consumer) kind.
2. Stop or narrow the offender: sp_trace_setstatus stops a trace by id; if tracing must continue, capture fewer events with tighter filters, to a fast local file rather than a live rowset.
3. Move to Extended Events. XE is far cheaper for the same visibility and has been the recommended path since SQL Server 2012; our XE session scripts in the repo cover the common capture scenarios.
4. Make trace hygiene routine: check sys.traces during health checks so abandoned traces do not linger.

How To See It

Rank it against everything else with Get-WaitStatistics. A TRACEWRITE spike lines up in time with whoever pressed “Run” in Profiler; sys.traces names the session.

---