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