Four waits cover SQL Trace’s file-writing machinery, and they share one story, so this page covers the set:
SQLTRACE_FILE_BUFFER: waiting for exclusive access to a trace buffer in preparation for writing it to a trace file.SQLTRACE_FILE_WRITE_IO_COMPLETION: waiting for a write to a trace file to complete.SQLTRACE_FILE_READ_IO_COMPLETION: waiting for a read from a trace file to complete, typically someone querying a trace file withfn_trace_gettable.SQLTRACE_PENDING_BUFFER_WRITERS: waiting for all pending trace buffer writers to finish, part of trace file rollover and shutdown coordination.
Since the default trace runs on practically every instance, small amounts of the family are universal.
Are They a Problem?
Rarely; none has been a noticeable contention point. The family scales with trace activity: the default trace’s light event stream produces traces of these waits, and heavy server-side traces to file produce more. File traces write asynchronously and cheaply, which is exactly why file targets are preferred over the live rowset (TRACEWRITE) when tracing must run on busy systems.
If these waits ever loom large, the trace file’s destination disk is slow, or someone is querying huge trace files with fn_trace_gettable (the read variant).
What To Do
- Filter the family in routine analysis.
- Keep trace files on reasonable local storage, not a stressed data volume or a slow share.
- Migrate tracing needs to Extended Events, which supersedes SQL Trace with lower overhead and better targets; our XE session scripts cover the common cases.
How To See It
Rank waits with Get-WaitStatistics; these names track trace activity, with sys.traces listing what is running.
Part of the SQL Server Wait Types Library.
Related deep dive: ASYNC_NETWORK_IO Wait Type.
Leave a Reply