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
- See what traces exist:
SELECT * FROM sys.traces;shows every running trace, its target, and whether it is the rowset (live consumer) kind. - Stop or narrow the offender:
sp_trace_setstatusstops a trace by id; if tracing must continue, capture fewer events with tighter filters, to a fast local file rather than a live rowset. - 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.
- Make trace hygiene routine: check
sys.tracesduring 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.
Part of the SQL Server Wait Types Library.
Related deep dive: ASYNC_NETWORK_IO Wait Type.
Leave a Reply