For an instructor lead, in-depth look at learning SQL click below.
ploring SQL Server Extended Events
SQL Server Extended Events is a light-weight performance monitoring system that allows database administrators to collect as little or as much data as necessary to troubleshoot or identify a performance problem. It’s a highly customizable, event-driven data collection system that SQL Server uses to send data to users or other targets.
Extended Events use event processing sessions to control how the events are handled. These can be viewed using the sys.dm_xe_sessions dynamic management view.
|
1 2 3 4 |
SELECT * FROM sys.dm_xe_sessions; |
**Creating an Extended Event Sessions**
To configure an extended events session, we use the CREATE EVENT SESSION command. We can specify features like the name of the session, the events we want to capture, and where we want to store those events.
|
1 2 3 4 5 6 |
CREATE EVENT SESSION [DemoSession] ON SERVER ADD EVENT sqlserver.sql_statement_starting, ADD EVENT sqlserver.sql_statement_completed ADD TARGET package0.event_file(SET filename='C:\temp\DemoSession.xel') |
Starting an Extended Event Session
We start a new session we’ve configured by using the ALTER EVENT SESSION command with the STATE = START argument.
|
1 2 3 4 5 |
ALTER EVENT SESSION [DemoSession] ON SERVER STATE=START; |
Stopping an Extended Event Session
Similarly, to stop a session, we use the ALTER EVENT SESSION command with the STATE = STOP argument.
|
1 2 3 4 5 |
ALTER EVENT SESSION [DemoSession] ON SERVER STATE=STOP; |
Dropping an Extended Event Session
To completely remove an extended event session, we can use the DROP EVENT SESSION command. This will deactivate the session and remove it from SQL Server.
|
1 2 3 4 |
DROP EVENT SESSION [DemoSession] ON SERVER; |
Extended Events are a powerful tool in SQL Server for performance troubleshooting. They require some getting used to, but once mastered they offer unparalleled insight into what is happening on your SQL Server installation.