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)