Sqlserver-dba.com

SQL WAIT STAT - TRACEWRITE and how to reduce it

SQL Server Books online defines the TRACEWRITE sql wait stat type as “Occurs when the SQL Trace rowset trace provider waits for either a free buffer or a buffer with events to process”

The trace rowset provider drops events from the internal buffer , if it needs the space. 

If the buffer is full and data is consumed slowly or not at all, the rowset provider allows a  delay. If the buffer is still full – the rowset provider drops events to allow progress. SQL Server Profiler creates an error message.

The TRACEWRITE sql wait stat type indicates if the internal buffer pressure is increasing – prior to dropping events. TRACEWRITE increases as more worker threads waitv for free buffers

A few  issues to consider and approaches to minimizing TRACEWRITE sql wait stat type

 1)Check for intense long running sql queries.

2)Are there other sql server traces running?

 The first query returns a recordset  of the number of traces running .

The second query returns greater trace detail.

 SELECT count(*) FROM :: fn_trace_getinfo(default) WHERE property = 5 and value = 1

GO

 SELECT * FROM :: fn_trace_getinfo(default)

 3)Check disk IO performance   


Author: Jack Vamvas (http://www.sqlserver-dba.com)

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact