TRACEWRITE and querying sys.traces

This post is about a wait type that I have seen being safely ignored by admins in the past but actually it does highlight an particular that I like to check up on in systems that I’m maintaining.

The wait type I’m referring to is TRACEWRITE and it’s easy to see why it often gets classed as being safe to ignore. SQL Server does run a default trace continuously so naturally this wait will show up in wait stat queries for your instances and be quite prominent (like the one below) but that said; whilst it’s relatively safe for the default trace to be ruled out of any further investigation what about traces that have been started by other users?

I typically see a scenario in non-production systems where a trace has been turned on to monitor a particular process for testing and then forgotten about and not stopped again once the process has finished. Tracing is not particularly cost friendly at the best of times so one running way beyond its expected duration is something you do want to know about.

The following query looks at what traces are currently running on the system:

SELECT

start_time,

stop_time,

[status],

[path],

is_default,

reader_spid

FROM sys.traces

 

This query returns information on all the current traces running on your system. It’s worth adding at this point that tracing to some degree (I still see tracing, a lot! has been replaced by extended events which are much more lightweight in operation, so another reason to be checking. Here's what a default trace looks like:

The columns returned are pretty self-explanatory, a value of 1 in the status column means the trace is currently running (0 = stopped) and you can use the reader_spid column to return information on who is using it, and who to ask about turning the trace off.


To summarise; in most cases trace related waits like TRACEWRITE are safe to ignore but it's good practice to routinely run a check on those systems where people might be running traces or you could even set up jobs that will automatically stop any traces outside of working hours.


One final point, it is very common for monitoring applications to sue some form of trace analysis, these could well be part of routine maintenance so prior to switching anything off ensure you know how the trace has originated and what its purpose is.