How to Monitor SQL Server with SQL Server Profiler on Windows Server 2022

Introduction to SQL Server Profiler on Windows Server 2022

SQL Server Profiler is a graphical interface for creating and managing traces — real-time captures of SQL Server event streams. A trace records the exact SQL statements, stored procedure calls, errors, and performance data that flow through the SQL Server engine, making it an indispensable tool for diagnosing performance problems, identifying slow queries, investigating deadlocks, and auditing database activity. While Microsoft has been positioning Extended Events as the modern long-term replacement for Profiler, SQL Server Profiler remains fully functional in SQL Server 2019 and 2022 and is often faster to use for ad-hoc diagnostics. This guide covers everything from launching Profiler to analyzing captured trace data and understanding when to move to Extended Events.

SQL Server Profiler vs Extended Events

Before using Profiler, understand the trade-offs between it and Extended Events:

SQL Server Profiler advantages: Graphical interface built into SSMS makes it easy to start capturing events immediately without writing XML. Pre-built templates cover the most common monitoring scenarios. The Replay feature allows captured traces to be replayed against a test server for performance regression testing. Deadlock Graph events are presented visually in Profiler’s output. For ad-hoc diagnostics on familiar platforms, Profiler is faster to use than Extended Events.

SQL Server Profiler disadvantages: Profiler has measurable overhead on the SQL Server instance — it consumes additional CPU and memory proportional to the volume of events captured. Running a Profiler trace on a heavily loaded production server can exacerbate performance problems. Profiler is not available on SQL Server on Linux. Microsoft has stated it may be deprecated in a future SQL Server version.

Extended Events advantages: Minimal overhead — Extended Events was designed for low-impact monitoring of production systems. Events are defined in XML and stored in sessions that can persist across server restarts. The Extended Events engine is available in all SQL Server versions since 2008, including SQL Server on Linux. The sys.dm_xe_sessions DMV provides programmatic access to session state.

For production monitoring of critical systems, prefer Extended Events. For quick one-off diagnostics on a test or development server, Profiler is acceptable and faster. This guide covers both.

Launching SQL Server Profiler

SQL Server Profiler is included with SQL Server Management Studio (SSMS) and can also be launched as a standalone application. Prerequisites:

The account used to run Profiler requires the ALTER TRACE server-level permission on the SQL Server instance:

-- Grant trace permission to a monitoring account
USE master;
GRANT ALTER TRACE TO [DOMAINMonitoringUser];
GO

-- Verify the permission
SELECT 
    sp.name AS LoginName,
    spe.permission_name,
    spe.state_desc
FROM sys.server_permissions spe
JOIN sys.server_principals sp ON spe.grantee_principal_id = sp.principal_id
WHERE spe.permission_name = 'ALTER TRACE';
GO

Launch Profiler from SSMS: Tools menu > SQL Server Profiler. Alternatively, launch it directly from the command line:

& "C:Program Files (x86)Microsoft SQL Server170ToolsBinnPROFILER.EXE"

Adjust the path for your SQL Server version: 110=SQL2012, 120=SQL2014, 130=SQL2016, 140=SQL2017, 150=SQL2019, 160=SQL2022.

When Profiler opens, it immediately prompts you to connect to a SQL Server instance. Enter the server name (or use a named instance format SERVERINSTANCENAME), select authentication, and click Connect.

Creating Trace Templates

After connecting, the Trace Properties dialog opens. The General tab allows you to:

Name the trace (use a descriptive name like “SlowQuery-Prod-20260517”), select a trace template, choose whether to save trace output to a file or table, and set a maximum file size with automatic file rollover.

SQL Server Profiler includes several built-in templates for common scenarios:

Standard (default): Captures SQL:BatchCompleted, RPC:Completed, SQL:BatchStarting, and Audit Login/Logout events. Good for general activity monitoring.

TSQL_Duration: Captures only SQL:BatchCompleted and RPC:Completed with duration, CPU, reads, and writes. Best for identifying slow queries without the login noise.

TSQL_Replay: Captures the full set of events needed to replay the workload on another server. More events captured means higher overhead.

Tuning: Events required for the Database Engine Tuning Advisor. Captures RPC:Completed, SQL:BatchCompleted with database and object information.

To save a custom template for reuse: after configuring events and filters, go to File > Save As > Trace Template. Custom templates are stored per-user and available in the template dropdown for future traces.

Capturing Key Events

The Events Selection tab in Trace Properties controls which SQL Server events are captured. Understanding the key events is essential for effective tracing:

SQL:BatchCompleted: Fires when a T-SQL batch finishes executing. Captures the full batch text, duration (milliseconds), CPU time, logical reads, logical writes, and row count. This is the primary event for identifying slow ad-hoc queries. Duration is reported in milliseconds.

SQL:BatchStarting: Fires when a batch begins. Useful for correlating start and end times but adds overhead since it fires twice per batch. Only include this if you need precise start timestamps.

RPC:Completed: Fires when a stored procedure or parameterized query (Remote Procedure Call) completes. Like SQL:BatchCompleted but for procedure calls. If your application uses parameterized queries or stored procedures, this event is more useful than SQL:BatchCompleted.

Exception: Fires when a SQL Server error occurs. Captures the error number, severity, state, and error message. Essential for identifying application errors that are being silently swallowed by the application layer.

Deadlock Graph: Found under the Locks event category. Captures deadlock chain information as XML that Profiler displays as a visual graph. This is one of Profiler’s most compelling advantages over basic Extended Events — the visual deadlock graph makes it immediately clear which processes and objects were involved in the deadlock.

Lock:Timeout: Fires when a lock request times out (exceeds the lock timeout setting). Useful for identifying blocking scenarios.

To add or remove events: click Column Filters on the Events Selection tab to access the per-column filter settings, or check/uncheck the event checkboxes in the event tree.

Filtering Traces by Duration, Database, and Login

Running an unfiltered trace on a busy SQL Server generates enormous amounts of data and creates significant overhead. Always apply filters to capture only the events relevant to your investigation.

Click Column Filters on the Events Selection tab to open the filter configuration. Key filters to apply:

Duration (Greater than or equal): Set to 1000 (1 second in milliseconds) to capture only queries taking longer than 1 second. For investigating very slow queries, use 5000 (5 seconds). This single filter dramatically reduces trace volume on busy servers.

DatabaseName (Like): Filter to a specific database name (e.g., ProductionDB) to exclude system database activity and focus on your application database.

LoginName (Not like): Exclude monitoring account logins (e.g., solarwinds_monitor, NT AUTHORITYSYSTEM) that generate constant low-level activity unrelated to application performance.

ApplicationName (Like): Filter to a specific application name if your connection strings include the Application Name parameter (e.g., JDBC% or EntityFramework%).

Example filter combination for identifying slow queries in a specific database:

Duration >= 2000 (2 seconds) AND DatabaseName = ‘OrdersDB’ AND LoginName NOT LIKE ‘sa’ AND LoginName NOT LIKE ‘NT%’

Saving Trace to File or Table

Saving to a file (.trc): On the General tab, check “Save to file” and specify a path. Use the “Maximum file size” option with “Enable file rollover” for long-running traces. Traces saved to file can be opened for offline analysis without connecting to SQL Server. Set the file path to a non-system drive with sufficient free space — a high-activity trace can generate gigabytes of data per hour.

-- Example: Reading a .trc file with T-SQL (no Profiler needed):
SELECT 
    TextData,
    Duration,
    CPU,
    Reads,
    Writes,
    StartTime,
    EndTime,
    LoginName,
    DatabaseName,
    ObjectName
FROM sys.fn_trace_gettable('C:TracesSlowQuery_20260517.trc', default)
WHERE Duration > 5000000  -- Duration is in microseconds in trace files (5 seconds)
ORDER BY Duration DESC;

Saving to a table: Check “Save to table” on the General tab and specify a database and table name. The trace data streams directly into a SQL Server table, enabling real-time T-SQL queries against the captured data. Useful for long-running monitoring where you want to query trace data while it is still being captured.

-- Query the trace table while capture is running:
SELECT TOP 50
    TextData,
    Duration / 1000 AS DurationMS,
    CPU,
    Reads,
    StartTime,
    LoginName,
    DatabaseName
FROM DBA.dbo.ProfilerTrace
WHERE Duration > 1000000
ORDER BY Duration DESC;

Analyzing Trace Results

After stopping a trace, analyze the captured data to identify performance bottlenecks. Key analysis approaches:

Sort by Duration: Click the Duration column header to sort descending. The queries at the top are your slowest individual executions. Review the TextData column to identify the query text and the DatabaseName/ObjectName to identify which database objects are involved.

Identify high-frequency queries: A query that runs in 100ms but executes 10,000 times per hour generates 1,000,000 ms of CPU load. Sort by CPU or use the trace file T-SQL query to GROUP BY query text and SUM the CPU and Duration:

SELECT 
    -- Normalize query text by removing literals for grouping
    LEFT(TextData, 200) AS QueryText,
    COUNT(*) AS ExecutionCount,
    SUM(Duration) / 1000 AS TotalDurationMS,
    AVG(Duration) / 1000 AS AvgDurationMS,
    MAX(Duration) / 1000 AS MaxDurationMS,
    SUM(CPU) AS TotalCPU,
    SUM(Reads) AS TotalReads
FROM sys.fn_trace_gettable('C:Tracescapture.trc', default)
WHERE TextData IS NOT NULL
    AND Duration > 0
GROUP BY LEFT(TextData, 200)
ORDER BY TotalDurationMS DESC;

Deadlock analysis: Filter the trace view to show only Deadlock Graph events. Click on a deadlock graph event row and Profiler displays the deadlock chain visually in the lower pane, showing which SPIDs were involved and what resources they were waiting on. The XML tab shows the raw deadlock XML which can be saved for reference.

The Replay Feature

The Replay feature replays a captured trace against a test or development SQL Server instance. This is valuable for performance testing — capture a production workload, optimize queries or indexes, then replay the captured workload on a test server to measure the improvement.

Requirements for replay: The trace must have been captured with the TSQL_Replay template (or manually include all required replay events: SQL:BatchStarting, SQL:BatchCompleted, RPC:Starting, RPC:Completed, Audit Login, Audit Logout, and SPID columns). The target server must have the same databases and data as the source.

To start a replay: open the saved .trc file in Profiler (File > Open > Trace File), then click Replay > Start. Connect to the target replay server, configure replay options (number of replay threads, synchronization mode), and click Run.

Extended Events as a Modern Alternative

For production-grade query monitoring with minimal overhead, use Extended Events instead of Profiler. The following creates an Extended Events session equivalent to a Profiler slow query trace:

-- Create an Extended Events session to capture queries over 1 second
CREATE EVENT SESSION [SlowQueryCapture] ON SERVER
ADD EVENT sqlserver.sql_batch_completed (
    WHERE ([duration] > (1000000))  -- Duration in microseconds
    ACTION (
        sqlserver.sql_text,
        sqlserver.database_name,
        sqlserver.username,
        sqlserver.client_hostname,
        sqlserver.plan_handle
    )
),
ADD EVENT sqlserver.rpc_completed (
    WHERE ([duration] > (1000000))
    ACTION (
        sqlserver.sql_text,
        sqlserver.database_name,
        sqlserver.username
    )
)
ADD TARGET package0.ring_buffer (
    SET max_memory = 51200  -- 50 MB ring buffer
),
ADD TARGET package0.event_file (
    SET filename = N'C:XEventsSlowQuery.xel',
    max_file_size = 256,    -- 256 MB per file
    max_rollover_files = 5
)
WITH (
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    TRACK_CAUSALITY = ON
);
GO

-- Start the session:
ALTER EVENT SESSION [SlowQueryCapture] ON SERVER STATE = START;
GO

Query the Extended Events session output in real time:

-- Read from ring buffer (in-memory, no file required)
SELECT
    event_data.value('(event/@name)', 'varchar(50)') AS EventName,
    event_data.value('(event/@timestamp)', 'datetime2') AS EventTime,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') / 1000 AS DurationMS,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS SqlText,
    event_data.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS DatabaseName
FROM (
    SELECT CAST(target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets t
    JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
    WHERE s.name = 'SlowQueryCapture'
        AND t.target_name = 'ring_buffer'
) AS RingBuffer
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS Events(event_data)
ORDER BY EventTime DESC;

Stop and drop the session when done:

ALTER EVENT SESSION [SlowQueryCapture] ON SERVER STATE = STOP;
DROP EVENT SESSION [SlowQueryCapture] ON SERVER;

SQL Server Audit vs Profiler

SQL Server Audit is the appropriate tool for compliance-grade database activity recording. Unlike Profiler (which is a diagnostic tool), SQL Server Audit provides tamper-evident, structured audit trails that satisfy requirements like PCI-DSS, HIPAA, and SOX. Audit events are written to the Windows Security event log or dedicated audit files and cannot be modified by SQL Server administrators. Use Profiler for performance diagnostics; use SQL Server Audit for security and compliance monitoring. The two tools serve different purposes and are used in parallel on production systems.

Finding Slow Queries with Profiler

The most common Profiler use case is identifying slow queries. A recommended workflow for production query diagnosis:

1. Start a Profiler trace with the TSQL_Duration template.

2. Set a Duration filter of 3000 (3 seconds) to capture only significantly slow queries.

3. Save to a file with a 500 MB maximum and rollover enabled.

4. Run for 30 minutes during peak load.

5. Stop the trace and open the .trc file. Sort by Duration descending.

6. Take the top 5 slowest queries and run them through SSMS with Include Actual Execution Plan enabled.

7. Look for table scans on large tables, high-cost hash joins, and missing index recommendations (shown as green Missing Index suggestion lines in the execution plan).

-- After identifying a slow query from Profiler, check for missing indexes:
SELECT
    mid.statement AS TableName,
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS ImprovementMeasure,
    'CREATE INDEX IX_' + REPLACE(mid.statement, '.', '_') + 
        '_' + REPLACE(REPLACE(ISNULL(mid.equality_columns,''), ', ', '_'), '[', '') AS SuggestedIndex,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY ImprovementMeasure DESC;

Conclusion

SQL Server Profiler remains a powerful and practical tool for SQL Server monitoring on Windows Server 2022. Use it for ad-hoc performance diagnostics, deadlock analysis, and quick slow-query identification where its visual interface offers speed advantages over Extended Events. Always apply duration and database filters to minimize overhead, save traces to file for offline analysis, and use the T-SQL trace file queries to aggregate and rank captured data. For persistent, low-impact production monitoring, transition to Extended Events sessions that run continuously with minimal server impact. Understanding both tools and when to use each gives you comprehensive visibility into SQL Server workload performance on Windows Server 2022.