How to Use SQL Server Profiler on Windows Server 2016

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

SQL Server Profiler is a graphical interface included with SQL Server Management Studio that allows database administrators to capture and analyze a real-time trace of events occurring inside a SQL Server instance. By recording events such as query executions, stored procedure calls, login attempts, deadlocks, and lock escalations, Profiler helps identify slow queries, troubleshoot application errors, audit user activity, and tune query performance. This guide explains how to use SQL Server Profiler on a Windows Server 2016 host running SQL Server 2016 or SQL Server 2019, how to define event filters and trace templates, how to save traces for offline analysis, and how to replay captured workloads in a test environment.

SQL Server Profiler requires that the SQL Server instance is accessible from the machine running SSMS. Connect to the target instance with an account that holds the ALTER TRACE server permission, which is included in the sysadmin and serveradmin fixed server roles. Note that SQL Server Profiler has been deprecated in favor of Extended Events for new development, but it remains available and is widely used for day-to-day troubleshooting.

Step 1: Open SQL Server Profiler

Open SQL Server Management Studio from the Start menu. In the Tools menu, select SQL Server Profiler. Alternatively, launch it directly from the Start menu by searching for SQL Server Profiler. When Profiler opens, it immediately prompts you to connect to a SQL Server instance. Enter the server name and select your authentication method, then click Connect.

Step 2: Define a New Trace

The Trace Properties dialog opens automatically. On the General tab, enter a descriptive name for the trace such as ProductionQueryAudit. In the Use the template dropdown, select TSQL_Duration which captures all T-SQL statements and their execution duration, making it ideal for identifying slow queries. Select Save to file and specify a path such as C:SQLTracesQueryAudit.trc to write the trace output to a file for later analysis. Set a maximum file size limit such as 500 MB to prevent disk exhaustion. Check Enable file rollover to create a new file automatically when the size limit is reached.

Step 3: Filter Events to Reduce Noise

Without filters, a busy production SQL Server will generate an enormous trace that is difficult to analyze. Click the Events Selection tab. Uncheck event classes that are not relevant to your investigation. For query performance analysis, keep SQL:BatchCompleted and RPC:Completed and uncheck login-related events. Click Column Filters and add a Duration filter with a minimum value of 1000 (milliseconds) to capture only queries that take longer than one second. Add a DatabaseName filter to limit the trace to a specific database if needed. Click Run to start the trace.

Step 4: Interpret the Live Trace Output

The trace grid updates in real time as events are captured. Each row represents an event with columns showing the Event Class, the T-SQL text or procedure name, the Duration in milliseconds, the CPU usage, the number of Reads and Writes, the application name, the login name, and the SPID (Server Process ID). Sort by Duration descending to identify the slowest queries immediately. Right-click any row and select Extract Event Data to view the full query text for that event.

Step 5: Analyze Deadlocks with the Deadlock Graph

To capture deadlock information, create a new trace and include the Deadlock Graph event from the Locks event category. When a deadlock occurs, Profiler captures a graphical XML representation of the deadlock cycle. Click the Deadlock Graph row in the trace to view the diagram, which shows the processes involved, the resources each process was waiting for, and which process was chosen as the deadlock victim. This diagram is invaluable for identifying the tables, indexes, and query patterns that are causing contention.

-- Query to find recent deadlock information from the system health session
SELECT xdr.value('@timestamp', 'datetime2') AS DeadlockTime,
       xdr.query('.') AS DeadlockGraph
FROM (
    SELECT CAST(target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets t
    JOIN sys.dm_xe_sessions s ON t.event_session_address = s.address
    WHERE s.name = 'system_health'
      AND t.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY DeadlockTime DESC;

Step 6: Save and Replay a Trace

After capturing a workload trace, you can replay it against a test database to validate query changes or index additions before deploying to production. Open the saved TRC file in Profiler via File, Open, Trace File. Then select Replay, Start to replay the workload. For accurate replay, the trace must have been captured with the Replay template rather than a standard auditing template, as Replay captures additional event context required for accurate workload reproduction.

Step 7: Import a Trace into Database Engine Tuning Advisor

SQL Server Profiler traces can be fed directly into the Database Engine Tuning Advisor, which analyzes the workload and recommends indexes, indexed views, and statistics that would improve overall query performance. From SSMS, open Database Engine Tuning Advisor from the Tools menu. In the Workload section, select File and browse to your saved TRC file. Select the database to tune, then click Start Analysis. Review the recommendations and apply those that align with your application’s requirements.

-- Review current index usage statistics to complement Profiler analysis
SELECT OBJECT_NAME(i.object_id) AS TableName,
       i.name AS IndexName,
       s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
    ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY s.user_seeks DESC;

Step 8: Automate Trace Collection with SQL Server Extended Events

For long-running or always-on monitoring, Extended Events provides a lower-overhead alternative to Profiler traces. Create an Extended Events session to capture slow queries automatically:

CREATE EVENT SESSION [SlowQueryCapture] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    WHERE duration > 1000000  -- microseconds; 1 second
)
ADD TARGET package0.event_file (
    SET filename = N'C:SQLTracesSlowQueries.xel',
    max_file_size = 100
)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);

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

By combining SQL Server Profiler for interactive troubleshooting sessions with Extended Events for continuous background monitoring, database administrators can maintain comprehensive visibility into SQL Server performance on Windows Server 2016.