Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
PS the SAN admin will always tell you you have IOPS to spare. :-) –  Aaron Bertrand Aug 11 '12 at 18:12
    
Also FWIW I've appealed to my connections to get a more authoritative answer on this one. –  Aaron Bertrand Aug 13 '12 at 13:56
add comment

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

share|improve this answer
1  
+1 on the XE suggestion. It is definitely a change but I think we should all be transitioning to XE as opposed to traces due to deprecation. –  Thomas Stringer Aug 11 '12 at 19:05
    
@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 '12 at 9:40
    
@EdHarper Have you also turned off the default trace? What is SELECT @@VERSION;? –  Aaron Bertrand Aug 13 '12 at 10:34
    
@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 '12 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? :-)) –  Aaron Bertrand Aug 13 '12 at 12:16
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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