SQL Server Profiler & Extended Events
7 questions found
What is SQL Server Profiler, and what is it traditionally used for?
Beginner
SQL Server Profiler is a graphical tool that captures a detailed trace of events happening on a SQL Server instance, such as every query executed, how long each one took, and any errors encountered, historically used by developers and administrators to diagnose performance problems or understand exactly what an application is sending to the database.
-- Profiler traces are configured through its graphical interface
-- selecting specific event classes like SQL:BatchCompleted to capture
Real-world example
A developer uses SQL Server Profiler to capture every query an application sends to the database during a specific user action, discovering an unexpectedly large number of redundant queries being executed for a single page load.
Common follow-ups: Is SQL Server Profiler still recommended for use on production servers today?;What has largely replaced Profiler in more recent versions of SQL Server?
Extended Events;Query Optimization & Plans
What are Extended Events, and why are they generally recommended over SQL Server Profiler for modern monitoring needs?
Beginner
Extended Events are a lightweight, highly configurable event tracing system built directly into the SQL Server engine, using significantly less overhead than the older Profiler tool, making them a much safer choice for monitoring busy production servers without noticeably impacting performance while capturing the events you need.
CREATE EVENT SESSION SlowQueries ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(WHERE duration > 1000000)
ADD TARGET package0.event_file (SET filename = 'SlowQueries.xel');
Real-world example
A database administrator sets up an Extended Events session to capture only queries running longer than one second, gaining the monitoring insight they need with far less overhead than running a comparable trace in the older Profiler tool.
Common follow-ups: How much less overhead do Extended Events typically add compared to Profiler?;What are targets in the context of Extended Events, and what options are available?
SQL Server Profiler & Extended Events;Query Optimization & Plans
How do you create an Extended Events session to capture specific long running queries on a production server?
Intermediate
You define an event session specifying which event to capture, such as sql_statement_completed, add a predicate filtering for only the specific conditions you care about, like a minimum duration, choose a target such as an event file to store the captured data, and then start the session to begin capturing matching events going forward.
CREATE EVENT SESSION LongRunningQueries ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(ACTION (sqlserver.sql_text, sqlserver.client_app_name)
WHERE duration > 5000000)
ADD TARGET package0.event_file (SET filename = 'LongQueries.xel')
WITH (STARTUP_STATE = ON);
ALTER EVENT SESSION LongRunningQueries ON SERVER STATE = START;
Real-world example
A team troubleshooting intermittent slow performance sets up an Extended Events session capturing any query running longer than five seconds, along with the exact query text and originating application, running continuously in the background with minimal overhead.
Common follow-ups: What does the STARTUP_STATE option control?;How do you view the captured data once the session has been running for a while?
Query Optimization & Plans;Isolation & Locking
How do you analyze the data captured by an Extended Events session once it has been running for a period of time?
Intermediate
You can open the event file target directly in SQL Server Management Studio, which provides a graphical interface for viewing and filtering the captured events, or query the file programmatically using the sys.fn_xe_file_target_read_file function to extract the data into a table format for more detailed custom analysis.
SELECT event_data.value('(event/@timestamp)[1]', 'datetime2') AS EventTime,
event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS Duration
FROM sys.fn_xe_file_target_read_file('LongQueries*.xel', NULL, NULL, NULL)
CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) AS ed;
Real-world example
A performance analyst extracts captured Extended Events data into a queryable table format, allowing them to run aggregate calculations and identify patterns across thousands of captured slow query events.
Common follow-ups: Why is the captured data stored in an XML format?;What tools besides SQL Server Management Studio can be used to analyze Extended Events data?
Query Optimization & Plans;Data Types & Schema Design
How would you design an Extended Events session to monitor for a very specific, hard to reproduce issue, such as intermittent deadlocks, in a production environment?
Advanced
You would capture the xml_deadlock_report event, which automatically fires whenever a deadlock occurs, providing full details about the transactions and resources involved, and configure the session to run continuously with minimal overhead using an appropriate target like a ring buffer or event file, so you have complete deadlock details captured automatically the next time the issue occurs, without needing to catch it happening live.
CREATE EVENT SESSION DeadlockMonitor ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file (SET filename = 'Deadlocks.xel')
WITH (STARTUP_STATE = ON);
Real-world example
A team investigating occasional, hard to reproduce deadlocks sets up a permanent Extended Events session capturing the deadlock report event, finally capturing full details the next time the issue occurs in production without needing to actively watch for it.
Common follow-ups: How do you read and interpret the captured deadlock report once you have one?;What is the difference between a ring buffer and an event file target?
Isolation & Locking;Error Handling with TRY CATCH
What is the performance overhead comparison between SQL Server Profiler, server side traces, and Extended Events, and how should this inform monitoring decisions on a production system?
Advanced
SQL Server Profiler typically has the highest overhead since it processes and displays events in real time through the client interface, server side traces reduce this overhead somewhat by writing directly to a file, and Extended Events have the lowest overhead of the three due to their more efficient internal architecture, making Extended Events the generally recommended choice for any ongoing monitoring on a busy production system.
-- Extended Events is the lowest overhead option
-- for continuous production monitoring
ALTER EVENT SESSION LongRunningQueries ON SERVER STATE = START;
Real-world example
A database administrator switches their team's ongoing production monitoring from an old Profiler based trace to an equivalent Extended Events session, measurably reducing the monitoring overhead on their busiest servers.
Common follow-ups: Are there any scenarios where Profiler is still the more practical choice today?;How do you measure the actual overhead a monitoring session is adding to a server?
Query Optimization & Plans;SQL Server Architecture & Editions
How do you properly stop and clean up an Extended Events session once it is no longer needed?
Intermediate
You use the ALTER EVENT SESSION statement with STATE = STOP to stop the session from capturing further events, and optionally use DROP EVENT SESSION to remove its definition entirely if you do not plan to use it again, ensuring you are not leaving unnecessary monitoring sessions running and consuming resources indefinitely.
ALTER EVENT SESSION LongRunningQueries ON SERVER STATE = STOP;
DROP EVENT SESSION LongRunningQueries ON SERVER;
Real-world example
A team stops and removes a temporary Extended Events session they had created for a specific troubleshooting investigation, cleaning up once the issue was resolved and the session was no longer needed.
Common follow-ups: What happens to already captured data in an event file after the session is stopped?;How do you list all currently active Extended Events sessions on a server?
Query Optimization & Plans;SQL Server Agent & Job Scheduling