This post has been republished via RSS; it originally appeared at: SQL Server Support articles.
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, but I do not expect this has changed from 2008 R2.
Finally, keep in mind that Extended Events is the “wave of the future” and SQL tracing may be removed in future versions.
Namaste!
Joseph