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

Query performance problems are one of the most common reasons SQL Server workloads degrade on Windows Server 2025. Identifying slow queries, long-running transactions, and lock contention requires capturing detailed execution data at runtime — something that neither Windows Performance Monitor nor basic SQL Server DMVs provide out of the box. SQL Server Profiler, the legacy GUI-based tracing tool, offers an accessible starting point for capturing events like SQL:BatchCompleted and RPC:Completed with duration and I/O columns. However, Microsoft’s modern replacement — Extended Events (XEvents) — provides lower overhead, greater flexibility, and better scalability for production environments. This guide covers creating useful traces in SQL Server Profiler, filtering for slow queries, saving and replaying traces, and then migrating to XEvents with CREATE EVENT SESSION for production-grade monitoring.

Prerequisites

SQL Server 2019 or SQL Server 2022 running on Windows Server 2025
SQL Server Management Studio (SSMS) 20 or later installed (includes SQL Server Profiler)
VIEW SERVER STATE permission (required for XEvents and DMV queries)
ALTER ANY EVENT SESSION permission (required to create Extended Event sessions)
Sufficient disk space for trace files (plan for at least 2–5 GB for active workload captures)
SQL Server Agent running (for scheduled trace jobs)

Step 1: Open SQL Server Profiler and Create a New Trace

Launch SQL Server Profiler from SSMS (Tools → SQL Server Profiler) or directly from the Start menu. Connect to your SQL Server instance when prompted.

Click File → New Trace
In the Trace Properties dialog, name the trace (e.g., SlowQueryCapture_20260517)
Select the TSQL_Duration template as a starting point — it pre-selects the most useful event columns
Under Save to file, set a path: D:SQLTracesSlowQueryCapture.trc with a maximum file size of 2048 MB and enable file rollover
Optionally, enable Save to table for query-based analysis: connect to a DBA database and specify table DBA.dbo.TraceCapture

Step 2: Configure Event Selection for Performance Analysis

Click the Events Selection tab. Remove the default events and add only those needed for slow query analysis to minimize overhead:

SQL:BatchCompleted — captures ad-hoc SQL batch execution (completed)
RPC:Completed — captures stored procedure and parameterized query execution
SQL:StmtCompleted (optional) — statement-level granularity within batches

For each event, enable these columns:

TextData — the SQL text or procedure name
Duration — execution time in microseconds
CPU — CPU time in milliseconds
Reads — logical read count
Writes — logical write count
LoginName — the connecting user
DatabaseName — database context
SPID — session process ID

Step 3: Apply Duration Filters to Capture Only Slow Queries

Capturing every query on a busy SQL Server generates enormous trace files and imposes significant overhead. Filter by duration to capture only queries exceeding your slow-query threshold:

In Events Selection, click Column Filters
Select Duration
Set Greater than or equal to 1000000 (Duration is in microseconds; 1,000,000 µs = 1 second)
Optionally filter by DatabaseName (Like = YourDatabase) to narrow scope
Optionally exclude system queries: filter LoginName to Not like NT AUTHORITY%

Click Run to start the trace. Let it capture for 15–30 minutes during peak load.

Step 4: Analyze Trace Results for Slow Queries

With the trace saved to a file or table, analyze it using T-SQL for efficient sorting and aggregation:

# Open a trace file in SSMS using fn_trace_gettable
# Run this T-SQL in SSMS against the DBA database

-- Load trace file into a queryable result set
SELECT
    TextData,
    Duration       / 1000  AS Duration_ms,
    CPU                    AS CPU_ms,
    Reads                  AS LogicalReads,
    Writes                 AS LogicalWrites,
    LoginName,
    DatabaseName,
    StartTime
FROM fn_trace_gettable('D:SQLTracesSlowQueryCapture.trc', DEFAULT)
WHERE Duration >= 1000000   -- filter >= 1 second (µs)
ORDER BY Duration DESC;

-- Aggregate by normalized query text (top 10 worst queries)
SELECT TOP 10
    TextData,
    COUNT(*)                     AS ExecutionCount,
    AVG(Duration / 1000)         AS AvgDuration_ms,
    MAX(Duration / 1000)         AS MaxDuration_ms,
    AVG(Reads)                   AS AvgLogicalReads
FROM fn_trace_gettable('D:SQLTracesSlowQueryCapture.trc', DEFAULT)
WHERE Duration >= 1000000
GROUP BY TextData
ORDER BY AVG(Duration) DESC;

Step 5: Replay a Trace for Stress Testing

SQL Server Profiler can replay a captured trace against a test server, reproducing the exact workload for regression testing after index or query changes:

For replay, the trace must include the Replay event set — use the TSQL_Replay template when capturing
Required additional columns for replay: SPID, ClientProcessID, BinaryData
Open the saved .trc file: File → Open → Trace File
Click Replay → Start and connect to your test SQL Server instance
Set replay options: choose Replay using multiple threads for load testing, or Reproduce performance counters for faithful timing

Step 6: Migrate to SQL Server Extended Events for Production

SQL Server Profiler is deprecated for production use due to its performance overhead. Extended Events (XEvents) capture the same data with significantly lower impact. Create an XEvent session using T-SQL:

-- Create an Extended Events session to capture slow queries
-- (equivalent to the Profiler trace above)
CREATE EVENT SESSION [SlowQueryMonitor] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    WHERE (
        [duration] >= 1000000   -- 1 second in microseconds
        AND [sqlserver].[database_name]  N'master'
        AND [sqlserver].[database_name]  N'tempdb'
    ),
    ACTION (
        sqlserver.sql_text,
        sqlserver.plan_handle,
        sqlserver.query_hash,
        sqlserver.session_id,
        sqlserver.username,
        sqlserver.database_name
    )
),
ADD EVENT sqlserver.rpc_completed (
    WHERE ([duration] >= 1000000),
    ACTION (
        sqlserver.sql_text,
        sqlserver.plan_handle,
        sqlserver.session_id,
        sqlserver.username
    )
),
ADD EVENT sqlserver.lock_acquired (
    WHERE ([resource_type] = (5)),   -- 5 = OBJECT level locks
    ACTION (sqlserver.sql_text, sqlserver.session_id)
)
ADD TARGET package0.ring_buffer (
    SET MAX_MEMORY = 51200   -- 50 MB ring buffer
),
ADD TARGET package0.event_file (
    SET FILENAME = N'D:XEventsSlowQueryMonitor.xel',
        MAX_FILE_SIZE = 512,          -- MB per file
        MAX_ROLLOVER_FILES = 10
)
WITH (
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON    -- auto-start with SQL Server
);

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

-- Verify the session is running
SELECT name, create_time, event_retention_mode_desc, target_count, session_source
FROM sys.dm_xe_sessions
WHERE name = 'SlowQueryMonitor';

Step 7: Read Extended Events Data

Query XEvent ring buffer data in real time, or read from the file target:

-- Read from ring buffer target (real-time, in-memory)
SELECT
    event_data.value('(event/@name)[1]',              'nvarchar(100)')  AS EventName,
    event_data.value('(event/@timestamp)[1]',         'datetime2')      AS EventTime,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') / 1000 AS Duration_ms,
    event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'bigint') / 1000 AS CPU_ms,
    event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'bigint')   AS LogicalReads,
    event_data.value('(event/action[@name="sql_text"]/value)[1]',   'nvarchar(max)') AS SqlText,
    event_data.value('(event/action[@name="username"]/value)[1]',   'nvarchar(100)') AS Username
FROM (
    SELECT CAST(target_data AS XML) AS TargetXML
    FROM sys.dm_xe_session_targets t
    JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
    WHERE s.name = 'SlowQueryMonitor'
      AND t.target_name = 'ring_buffer'
) AS rb
CROSS APPLY TargetXML.nodes('RingBufferTarget/event') AS XEventData(event_data)
ORDER BY Duration_ms DESC;

-- Read from file target (.xel file) using sys.fn_xe_file_target_read_file
SELECT
    event_data.value('(event/@name)[1]',              'nvarchar(100)')   AS EventName,
    event_data.value('(event/@timestamp)[1]',         'datetime2')       AS EventTime,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') / 1000 AS Duration_ms,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS SqlText
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file(
        'D:XEventsSlowQueryMonitor*.xel', NULL, NULL, NULL
    )
) AS FileData
ORDER BY Duration_ms DESC;

You can also open XEL files directly in SSMS: File → Open → File, select the .xel file, and use the XDL viewer to filter, sort, and group events interactively. Right-click any event to open the associated execution plan in the graphical plan viewer.

Step 8: PowerShell Automation for XEvent Session Management

# Start or stop the XEvent session via PowerShell + Invoke-Sqlcmd
$SqlInstance = "sql01.contoso.local"

# Start the session
Invoke-Sqlcmd -ServerInstance $SqlInstance -TrustServerCertificate -Query `
    "ALTER EVENT SESSION [SlowQueryMonitor] ON SERVER STATE = START;"

# Stop the session
Invoke-Sqlcmd -ServerInstance $SqlInstance -TrustServerCertificate -Query `
    "ALTER EVENT SESSION [SlowQueryMonitor] ON SERVER STATE = STOP;"

# List all XEvent sessions and their status
Invoke-Sqlcmd -ServerInstance $SqlInstance -TrustServerCertificate -Query @"
SELECT name, create_time,
       CASE WHEN is_running = 1 THEN 'Running' ELSE 'Stopped' END AS Status
FROM sys.server_event_sessions s
LEFT JOIN sys.dm_xe_sessions x ON x.name = s.name
ORDER BY name;
"@

# Archive and compress XEL files older than 24 hours
$XelPath = "D:XEvents"
Get-ChildItem -Path $XelPath -Filter "*.xel" |
    Where-Object { $_.LastWriteTime -lt (Get-Date).AddHours(-24) } |
    ForEach-Object {
        Compress-Archive -Path $_.FullName `
            -DestinationPath "$XelPathArchive$($_.BaseName).zip" `
            -CompressionLevel Optimal
        Remove-Item $_.FullName
        Write-Output "Archived: $($_.Name)"
    }

Conclusion

SQL Server Profiler provides a low-barrier entry point for capturing and analyzing query performance on Windows Server 2025 SQL Server instances — its GUI-driven event selection, duration filters, and trace replay capability make it valuable for ad-hoc investigations and workload replay scenarios. However, for ongoing production monitoring, Extended Events deliver the same diagnostic data at a fraction of the overhead, with the added benefits of file-target persistence, ring-buffer streaming, and full T-SQL management. By capturing sql_statement_completed and rpc_completed events filtered to queries exceeding your SLA threshold, and storing results in rotating XEL files readable by the SSMS XDL viewer, you establish a lightweight, always-on slow query monitor that flags problems before users report them. Pair XEvent data with execution plan analysis in SSMS to move quickly from symptom to solution.

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