Sql-server – Is the SQLTRACE_FILE_BUFFER wait type documented? What does it indicate

sql serversql-server-2008-r2wait-types

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.

Best Answer

I don't believe it is documented anywhere (even in Bob Ward's Wait Type Repository - a useful resource in any case), and I suspect you are quite right about the underlying cause.

If this wait type seems to be causing you significant performance issues (and you've isolated the wait as the cause and not a symptom, by observing relieved pressure when you temporarily disable the trace), probably the best thing would be to investigate the trace and make sure:

  • you are filtering adequately to prevent the writing of data you'll never look at.
  • you are capturing only the event types you need.
  • you aren't writing to a severely under-powered or over-utilized disk, to the same local disks where your data/log files are, or over a network connection that is just not capable of handling the load (if writing remotely the wait may not show up as network - especially if the write is going over iSCSI or other remote-disks-that-look-kind-of-local-to-SQL-Server).

For most things your trace is probably collecting, you may be better off converting your trace to use Extended Events. Not everything there is more lightweight than trace, but most of it is. The EE team at Microsoft blogged about how to do this, but if you're planning a migration to SQL Server 2012, you'll want to watch Jonathan Kehayias' blog.

Related Question