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?


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).


Disabling the default trace removes the wait. We are still investigating the underlying cause of the performance problem.


1 Answer 1


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.

  • @Aaron - thanks for your thoughts. We decided to try disabling the trace entirely, which had no effect - the same wait type continues to appear in the monitoring table.
    – Ed Harper
    Aug 13, 2012 at 9:40
  • @Aaron - it's SQL 2008 R2 RTM (10.50.1600) Enterprise for X64. We haven't turned off the default trace; I will get that done.
    – Ed Harper
    Aug 13, 2012 at 12:12
  • @EdHarper so a couple of things I might suggest: (1) see if the default trace is getting pounded with events (2) see if moving off of RTM has any impact (you know there have been two service packs right? :-)) Aug 13, 2012 at 12:16
  • @Aaron - the default trace wasn't as heavily used as on other boxes in our environment; one average two or three objects being created per second, almost exlusively in tempdb - but disabling it made the wait disappear. We're going to get up to the latest service pack and see if anything changes.
    – Ed Harper
    Aug 14, 2012 at 10:43
  • @Ed so the machine where these waits were accumulating had 2-3 objects per second being created in tempdb, or another machine had this behavior? Not sure if I'm interpreting your comment correctly. It would be interesting if these waits were caused by tracing a lot of temp table creation (and why your tempdb activity is so high). Aug 14, 2012 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.