First published on MSDN on Dec 12, 2012
Is SQL Trace, or SQL Profiler, or any other 3rd party tracing tool impacting SQL Server performance? The DBAs often contemplate this issue when they want to get insight from their SQL Server. Essentially the tracing mechanisms in SQL Server will present themselves as a bottlenecks (waits). See, when a SQL worker thread is performing its task (to execute a query or process a login), and a trace is enabled, then the thread needs to “report” what it has accomplished to the trace “master” When it does so, it acquires a type of a lock and so other worker threads have to wait their turn to report to the “master”. In other words, these threads will wait for the lock to be released.
Over the years, different versions of SQL Server have used different wait types to represent waiting for the trace lock whether from a server or client side trace. “ Client-side trace ” refers to using the SQL Profiler tool to connect to a SQL Server, or 3rd party applications using the Microsoft.SqlServer.Management.Trace API to programmatically capture trace events . “Server-side tracing” involves using SQL Trace via the T-SQL sp_trace_* procedures inside SQL Server. Note this trace runs inside the SQL Server process as opposed to another application.
Below is a summary of the server-side and client side wait types when a trace lock is waited on:
SQL Server 2000
Client-side (GUI/SQL Profiler): LATCH_EX (see KB 929728 )
Server-side: LATCH_EX (needs verification as it is hard to catch)
SQL Server 2005
Client-side (GUI/SQL Profiler): TRACEWRITE + OLEDB (if client is remote we will see these two wait types alternate)
Server-side (including default audit): SQLTRACE_LOCK
SQL Server 2008
Client-side (GUI/SQL Profiler): TRACEWRITE + OLEDB (if client is remote we will see these two wait types alternate)
Server-side (including default audit): SQLTRACE_LOCK
SQL Server 2008 R2
Client-side (GUI/SQL Profiler): TRACEWRITE + OLEDB (if client is remote we will see these two wait types alternate)
Server-side (including default audit): SQLTRACE_FILE_BUFFER, SQLTRACE_FILE_WRITE_IO_COMPLETION
I have not tested SQL Server 2012 and later, but I do not expect this has changed from 2008 R2.
Troubleshooting SQLTrace_* Waits (Update Jan 2022)
Excessive waits on this category constitute generally one of the following causes:
- Multiple servers-side traces: If you unknowingly start third party tool that monitor SQL Server or different users start traces to monitor performance, the server may end up with too many active traces. Then each worker threads has to report its completed events to each of them and this will lead to longer waits.
- Too many trace events added to traces: You may not have many traces, but you may have added dozens of different events in your trace. Again, this will cause each thread to have to go through each of this events and report status, causing a delay it is execution and the corresponding wait type.
- Slow disk or network disk: First, you must not trace to a network disk. It will slow down server write to the remote disk. A slow local disk will have the same effect. Ensure that the disk you are writing the trace to is local and performs fast. Preferrably use a dedicated volume for traces to be written to.
Namaste!
Joseph
Microsoft
