TRACEWRITE Wait Type Explained

TRACEWRITE waits originate from SQL Server's trace infrastructure, specifically the trace rowset provider that handles both legacy SQL Trace and Extended Events. When trace events are generated faster than they can be written to buffers or processed from buffers, the trace provider must wait for available buffer space.

The trace subsystem maintains internal memory buffers to queue events before writing them to files or memory targets. When applications generate high volumes of traced events (SELECT statements, logins, stored procedure executions), these buffers fill rapidly. The trace provider then enters a wait state with TRACEWRITE until either a buffer becomes available for new events or a filled buffer can be processed and written to the target.

This mechanism operates independently of the buffer pool and uses dedicated memory allocations within the trace subsystem. The wait occurs at the kernel level within SQL Server's tracing engine, not within user query execution paths. However, if the trace provider blocks due to buffer exhaustion, it can create backpressure that indirectly affects query performance.

SQL Server 2016 introduced improvements to Extended Events buffer management that reduced TRACEWRITE frequency compared to earlier versions. SQL Server 2019 and later versions include enhanced memory pressure detection that can automatically throttle trace event generation when buffer exhaustion occurs. SQL Server 2022 added better integration between trace subsystem memory management and overall instance memory pressure detection.

The wait typically correlates with active SQL Trace sessions, poorly configured Extended Events sessions with inadequate buffer sizes, or Extended Events sessions targeting synchronous targets like event files with slow disk subsystems.

-- Check active trace sessions and their buffer usage
SELECT 
    t.id,
    t.status,
    t.path,
    t.max_file_size,
    t.start_time,
    t.last_event_time,
    CASE WHEN t.is_rowset = 1 THEN 'Rowset Trace' ELSE 'File Trace' END as trace_type
FROM sys.traces t
WHERE t.status = 1;

-- Examine Extended Events sessions and memory targets
SELECT 
    s.name as session_name,
    s.blocked_event_fire_time,
    s.dropped_buffer_count,
    s.dropped_event_count,
    st.target_name,
    CASE st.target_name 
        WHEN 'ring_buffer' THEN 'Memory Target'
        WHEN 'event_file' THEN 'File Target'
        ELSE st.target_name 
    END as target_type
FROM sys.dm_xe_sessions s
INNER JOIN sys.dm_xe_session_targets st ON s.address = st.event_session_address
WHERE s.name NOT LIKE 'system_%';

-- Analyze TRACEWRITE wait statistics
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms as resource_wait_time_ms,
    CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) as pct_total_waits
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'TRACEWRITE'
    AND waiting_tasks_count > 0;

-- Check for Extended Events buffer pressure indicators
SELECT 
    s.name,
    s.blocked_event_fire_time,
    s.dropped_buffer_count,
    s.dropped_event_count,
    s.total_buffer_size,
    s.buffer_policy_flags,
    s.flag_desc
FROM sys.dm_xe_sessions s
CROSS APPLY (
    SELECT CASE s.buffer_policy_flags
        WHEN 0 THEN 'ALLOW_SINGLE_EVENT_LOSS'
        WHEN 1 THEN 'ALLOW_MULTIPLE_EVENT_LOSS' 
        WHEN 2 THEN 'NO_EVENT_LOSS'
        ELSE CAST(s.buffer_policy_flags as VARCHAR(10))
    END as flag_desc
) f
WHERE s.dropped_event_count > 0 OR s.dropped_buffer_count > 0;

-- Identify queries causing high trace event generation
SELECT TOP 10
    t.text,
    qs.execution_count,
    qs.total_worker_time,
    qs.total_elapsed_time,
    qs.creation_time,
    qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY qs.execution_count DESC;

Stop problematic SQL Trace sessions This script identifies and stops active rowset trace sessions that commonly cause TRACEWRITE waits. Rowset traces keep events in memory and are frequently the culprit.

-- Stop active rowset traces (common TRACEWRITE cause)
DECLARE @traceid INT;
DECLARE trace_cursor CURSOR FOR
    SELECT id 
    FROM sys.traces 
    WHERE is_rowset = 1 AND status = 1;

OPEN trace_cursor;
FETCH NEXT FROM trace_cursor INTO @traceid;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Stopping trace ID: ' + CAST(@traceid AS VARCHAR(10));
    EXEC sp_trace_setstatus @traceid, 0; -- Stop trace
    EXEC sp_trace_setstatus @traceid, 2; -- Close and delete trace
    FETCH NEXT FROM trace_cursor INTO @traceid;
END;

CLOSE trace_cursor;
DEALLOCATE trace_cursor;

Optimize Extended Events session buffer configuration Increases buffer sizes for Extended Events sessions showing dropped events, which reduces buffer pressure and TRACEWRITE waits.

-- Reconfigure Extended Events sessions with larger buffers
-- WARNING: Test buffer sizes in development first
DECLARE @session_name NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);

DECLARE session_cursor CURSOR FOR
    SELECT DISTINCT s.name
    FROM sys.dm_xe_sessions s
    WHERE s.dropped_event_count > 0 
        AND s.name NOT LIKE 'system_%';

OPEN session_cursor;
FETCH NEXT FROM session_cursor INTO @session_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'ALTER EVENT SESSION [' + @session_name + '] ON SERVER 
        WITH (MAX_MEMORY = 8192KB, MEMORY_PARTITION_MODE = PER_CPU)';
    
    PRINT 'Reconfiguring session: ' + @session_name;
    PRINT @sql;
    -- EXEC sp_executesql @sql; -- Uncomment after testing
    
    FETCH NEXT FROM session_cursor INTO @session_name;
END;

CLOSE session_cursor;
DEALLOCATE session_cursor;

Create optimized Extended Events session template Provides a template for creating Extended Events sessions with proper buffer configuration to prevent TRACEWRITE waits.

-- Template for TRACEWRITE-optimized Extended Events session
-- Adjust MAX_MEMORY and events based on your monitoring needs
CREATE EVENT SESSION [optimized_monitoring] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.username)
    WHERE ([duration]>=5000000)) -- Only capture statements > 5 seconds
ADD TARGET package0.ring_buffer(SET max_memory=(8192))
WITH (
    MAX_MEMORY=16384 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=PER_CPU,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF
);

-- Start the session
-- ALTER EVENT SESSION [optimized_monitoring] ON SERVER STATE = START;

Configure Extended Events sessions with adequate buffer sizes using MAX_MEMORY settings of at least 8MB per session. Use ALLOW_SINGLE_EVENT_LOSS or ALLOW_MULTIPLE_EVENT_LOSS retention modes instead of NO_EVENT_LOSS to prevent blocking when buffers fill.

Eliminate unnecessary SQL Trace sessions, particularly rowset traces that keep events in memory indefinitely. Replace SQL Trace with Extended Events for better performance and resource management.

Monitor Extended Events sessions regularly for dropped_event_count and dropped_buffer_count increases using sys.dm_xe_sessions. Set up alerts when dropped event counts exceed acceptable thresholds for your environment.

Use PER_CPU memory partition mode for Extended Events sessions on systems with multiple CPUs to reduce contention. Configure MAX_DISPATCH_LATENCY appropriately for your workload, typically 30-60 seconds for most monitoring scenarios.

Implement query-level filtering in Extended Events sessions using WHERE clauses to reduce event volume. Focus on duration-based filters, specific databases, or error conditions rather than capturing all activity.

For file-based Extended Events targets, ensure target storage has adequate throughput and avoid network locations. Use local SSDs when possible and configure appropriate MAX_FILE_SIZE and MAX_ROLLOVER_FILES settings.