Trace waits in SQLServer (SQLTRACE_BUFFER_FLUSH,TRACEWRITE,SQLTRACE_WAIT_ENTRIES,SQLTRACE_LOCK)

When you run Profiler trace from client systems or on server with large number of events you will see below wait types.

SQLTRACE_WAIT_ENTRIES
SQLTRACE_LOCK
SQLTRACE_BUFFER_FLUSH
TRACEWRITE

There is no way to completely avoid this wait type without stopping all the traces. We can reduce this waitypes by configuring server side trace instead of client side trace.

select * from sys.traces will give you information about all the traces. ( Status column 0 stopped and 1 active)

For the traces collected using profiler you will find a NULL Path. Profiler traces can cause large number of above waittypes.

Thread which raises the trace event is responsible to Get buffers to write event and write event. So collecting the trace on network share or on slow disk or using profiler can slow down the trace write and make the threads wait, sometimes we may also end with dead locked schedulers.

So ideally you have to avoid running profiler when you see below waits and use sp_trace_create if you like to capture the trace and pass the local path for tracefile parameter.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s