We run a background task on one of our servers which polls sys.dm_exec_requests
for long-running queries, gathering diagnostic information when it finds them, including wait type.
I'm seeing a number of queries with the SQLTRACE_FILE_BUFFER
wait. This isn't documented anywhere I've looked - I wondered if anyone has any further information about this wait type?
The name suggests that there's a delay writing trace data to disk; we have a server-side trace running on the instance which writes to a file for audit and performance monitoring purposes.
The SAN admin tells me we have IOPS to spare, so I don't think this is a simple I/O issue. What else should I check?
UPDATE
We disabled the server-side trace and continued to see the same issue; the latest thinking is that the SQLTRACE_FILE_BUFFER
wait is a symptom of memory pressure (there's a different documented wait type used when the trace buffer is being written to disk - SQLTRACE_BUFFER_FLUSH
- which doesn't appear).
UPDATE 2
Disabling the default trace removes the wait. We are still investigating the underlying cause of the performance problem.