Introduction to SQL Server Monitoring with SQL Server Profiler

SQL Server Profiler is a graphical interface for creating and managing SQL Server traces — real-time captures of SQL Server events including T-SQL queries executed, stored procedure calls, login events, error conditions, lock acquisitions, and query execution plans. Profiler is installed as part of SQL Server Management Studio (SSMS) and connects to SQL Server instances running on Windows Server 2019. While SQL Server Profiler is a legacy tool superseded by Extended Events (XEvents) for new deployments, it remains widely used for ad-hoc diagnostic sessions due to its intuitive interface. This guide covers using SQL Server Profiler for query performance analysis, deadlock detection, and workload capture, along with modern Extended Events alternatives.

Installing SQL Server Management Studio on Windows Server 2019

SQL Server Profiler ships with SSMS. Download SSMS from Microsoft’s documentation portal (currently available at aka.ms/ssmsfullsetup). Run the SSMS-Setup-ENU.exe installer as Administrator. The installation is straightforward — accept defaults and click Install. SSMS installs SQL Server Profiler, Object Explorer, Query Analyzer, and all associated tools. No reboot is required.

Alternatively, install SSMS silently via command line for automated deployments:

SSMS-Setup-ENU.exe /install /quiet /norestart /log "C:LogsSSMS_Install.log"

Starting a SQL Server Profiler Trace

Launch SQL Server Profiler from the SSMS Tools menu or by running profiler.exe from the SQL Server installation directory (typically C:Program FilesMicrosoft SQL Server150ToolsBinnSSMS). In Profiler, select File > New Trace. Connect to the SQL Server instance on your Windows Server 2019 machine using Windows Authentication or SQL Server Authentication.

The Trace Properties dialog opens. On the General tab, name the trace and select a Template. Available templates include: TSQL (captures all T-SQL statements), TSQL_Duration (T-SQL with execution duration), Tuning (for Database Engine Tuning Advisor input), SP_Counts (stored procedure statistics), and Standard (default, recommended starting point). Select Standard as the baseline template for general-purpose monitoring.

Customizing Trace Event Selection

Click the Events Selection tab to add or remove event classes. The Standard template includes SQL:BatchCompleted, SQL:StmtCompleted, RPC:Completed, SP:StmtCompleted, Login, Logout, and Errors and Warnings. Key additional events to add for specific scenarios:

For deadlock monitoring: Lock:Deadlock and Lock:Deadlock Chain. For query plan capture: Showplan XML (warning: this is very verbose and resource-intensive — use on a dev/test server or for brief captures only). For security audit: Audit Login Failed. For index scan analysis: Performance > Index Tuning subclass events. Click Column Filters to add filters. Always filter by DatabaseName to avoid capturing traffic from system databases (master, msdb, tempdb). Add a Duration filter (Duration >= 1000 for capturing queries over 1 second) to focus on slow queries and reduce trace volume.

Capturing and Saving Traces

Click Run in the Trace Properties dialog to start capturing. Events appear in real-time in the Profiler grid, color-coded by event type. The most important columns to display: EventClass, TextData (the T-SQL statement), Duration (in microseconds), CPU, Reads, Writes, SPID (session process ID), ApplicationName, LoginName, and DatabaseName.

Save the trace to a file for offline analysis. In Profiler, select File > Properties and enable Save to File. Specify a .trc file path on a fast local disk. Use the maximum file size setting (e.g., 500 MB) with rollover enabled to avoid filling the disk during long capture sessions. Saved trace files can be replayed in Profiler for testing or analyzed with T-SQL using the fn_trace_gettable function in SQL Server.

Analyzing Trace Data with T-SQL

Load a saved trace file into a table for structured analysis using T-SQL:

SELECT * INTO #TraceData FROM fn_trace_gettable('C:TracesMyTrace.trc', DEFAULT)

-- Top 10 slowest queries by total duration
SELECT TOP 10
    TextData,
    COUNT(*) AS ExecutionCount,
    SUM(Duration) / 1000 AS TotalDuration_ms,
    AVG(Duration) / 1000 AS AvgDuration_ms,
    MAX(Duration) / 1000 AS MaxDuration_ms,
    SUM(Reads) AS TotalLogicalReads
FROM #TraceData
WHERE EventClass = 12  -- SQL:BatchCompleted
AND TextData IS NOT NULL
GROUP BY TextData
ORDER BY TotalDuration_ms DESC

Find all deadlock events in the trace:

SELECT TextData, BinaryData, StartTime, SPID
FROM #TraceData
WHERE EventClass = 25  -- Lock:Deadlock
ORDER BY StartTime

Using Extended Events as the Modern Alternative

SQL Server Extended Events (XEvents) is the preferred modern tracing mechanism. It has lower overhead than SQL Server Profiler traces and supports more event types. Create an XEvent session to capture slow queries using T-SQL:

CREATE EVENT SESSION [SlowQueryCapture] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    WHERE sqlserver.sql_statement_completed.duration > 1000000  -- over 1 second (microseconds)
    ACTION(sqlserver.sql_text, sqlserver.database_name, sqlserver.username, sqlserver.client_app_name)
)
ADD TARGET package0.ring_buffer (SET max_memory = 51200)  -- 50 MB ring buffer
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS)
GO

ALTER EVENT SESSION [SlowQueryCapture] ON SERVER STATE = START;

Query the ring buffer target to see captured events:

SELECT
    xdr.value('@timestamp', 'datetime2') AS EventTime,
    xdr.value('(data[@name="duration"]/value)[1]', 'bigint') / 1000 AS Duration_ms,
    xdr.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS SQLText,
    xdr.value('(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 Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData(xdr)
ORDER BY EventTime DESC;

Identifying Performance Bottlenecks via DMVs

SQL Server Dynamic Management Views (DMVs) provide real-time query performance data without requiring a trace. Use these queries on Windows Server 2019 SQL instances for immediate insight:

Find the top CPU-consuming queries currently cached:

SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS avg_cpu_us,
    qs.execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_cpu_us DESC;

Find currently executing queries with their wait types and duration:

SELECT
    r.session_id, r.status, r.wait_type, r.wait_time,
    r.total_elapsed_time / 1000 AS elapsed_seconds,
    st.text AS sql_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id > 50
ORDER BY r.total_elapsed_time DESC;