Introduction to Extended Events
For those fighting with all the Extended Event terminology, let's step back and have a small overall introduction to Extended Events.
This post will give you a simplified end to end view through some of the elements in Extended Events. Before we start, let’s review the first Extented Events Objects that we are going to use:
- Events: The SQL Server code is populated with event calls that, by default, are disabled. Adding events to a session enables those event calls. Once enabled, they will execute the set of functionality defined by the session.
- Target: This is an Extended Event Object that can be used to log event information.
Also it is important to understand the following Extended Event concept
- Session: Server Object created by the user that defines and configures the Extended Event object to be use to when certain events happen.
It’s time to write a small “Hello World” using Extended Events. This will help understand the above terms. We will use:
- Event sqlserver. error_reported: This event gets fired every time that an error happens in the server.
- Target package0.asynchronous_file_target: This target stores the event data in disk.
- Session: We will create a session that sends all the error_reported events to the file target.
Before we get started, a quick note: Don’t run this script in a production environment. Even though, we are going just going to be raise very low severity user errors, we don't want to introduce noise in our servers.
-- TRIES TO ELIMINATE PREVIOUS SESSIONS
BEGIN TRY
DROP EVENT SESSION test_session ON SERVER
END TRY BEGIN CATCH END CATCH
GO
-- CREATES THE SESSION
CREATE EVENT SESSION test_session ON SERVER
ADD EVENT sqlserver.error_reported
ADD TARGET package0.asynchronous_file_target
-- CONFIGURES THE FILE TARGET
(set filename = 'c:\temp\data1.xel' , metadatafile = 'c:\temp\data1.xem')
GO
-- STARTS THE SESSION
ALTER EVENT SESSION test_session ON SERVER STATE = START
GO
-- GENERATES AN ERROR
RAISERROR (N'HELLO WORLD', -- Message text.
1, -- Severity,
1, 7, 3, N'abcde'); -- Other parameters
GO
-- STOPS LISTENING FOR THE EVENT
ALTER EVENT SESSION test_session ON SERVER
STATE = STOP
GO
-- REMOVES THE EVENT SESSION FROM THE SERVER
DROP EVENT SESSION test_session ON SERVER
GO
-- REMOVES THE EVENT SESSION FROM THE SERVER
select CAST(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file
('c:\temp\data1*.xel','c:\temp\data1*.xem', null, null)
This query will output the event data with our first hello world in the Extended Event format:
<event name="error_reported" package="sqlserver" id="100" version="1" timestamp="2010-02-27T03:08:04.210Z">
<data name="error"><value>50000</value><text /></data>
<data name="severity"><value>1</value><text /></data>
<data name="state"><value>1</value><text /></data>
<data name="user_defined"><value>true</value><text />
</data><data name="message"><value>HELLO WORLD</value><text /></data></event>
More on parsing event data in this post: Reading event data 101
Now let's move on to the other three Extended Event objects:
- Actions. This Extended Objects actions get executed before events are published (stored in buffers to be transferred to the targets). Currently we used them to add additional data (like the TSQL Statement related to an event, the session, the user) or generate dumps.
- Predicates: Predicates are logical expressions that specify what predicates to fire (E.g. only listen to errors with a severity greater than 16). This are composed of two Extended Objects:
o Predicate comparators: Defines an operator for a pair of values. Examples:
§ Severity > 16
§ error_message = ‘Hello World!!’
o Predicate sources: These are values that can also be in a predicate. They are generic data that isn’t usually provided in the event (similar to the actions).
§ Sqlserver.username = ‘Tintin’
As logical expressions they can be combined using logical operators (and, or, not). Note: In predicate expression you always have to specify an event field or predicate source and then a value to compare it to:
- sqlserver.username = 'Tintin' -- valid predicate
- 'Tintin' = sqlserver.username -- invalid predicate
- eventfield1 = eventfield2 -- invalid predicate
Let’s extend the previous example. We will trigger errors but we will use predicate to only register the ones that have severity > 1 and error message != ‘filter’ . To the events we will use the action sql_text that will attach the sql statement to the event data:
-- TRIES TO ELIMINATE PREVIOUS SESSIONS
BEGIN TRY
DROP EVENT SESSION test_session ON SERVER
END TRY BEGIN CATCH END CATCH
GO
-- CREATES THE SESSION
CREATE EVENT SESSION test_session ON SERVER
ADD EVENT sqlserver.error_reported (
ACTION (sqlserver.sql_text)
WHERE severity > 1 and (not (message = 'filter'))
-- WHERE severity > 1 and message <> 'filter' -- equivalent statements
)
ADD TARGET package0.asynchronous_file_target
(set filename = 'c:\temp\data2.xel' , metadatafile = 'c:\temp\data2.xem')
GO
-- STARTS THE SESSION
ALTER EVENT SESSION test_session ON SERVER STATE = START
GO
-- THIS ERROR WILL BE FILTERED BECAUSE SEVERITY <2
RAISERROR (N'PUBLISH', 1, 1, 7, 3, N'abcde');
GO
-- THIS ERROR WILL BE FILTERED BECAUSE MESSAGE = 'FILTER'
RAISERROR (N'FILTER', 2, 1, 7, 3, N'abcde');
GO
-- THIS ERROR WILL BE PUBLISHED
RAISERROR (N'PUBLISH', 2, 1, 7, 3, N'abcde');
GO
-- STOPS LISTENING FOR THE EVENT
ALTER EVENT SESSION test_session ON SERVER
STATE = STOP
GO
-- REMOVES THE EVENT SESSION FROM THE SERVER
DROP EVENT SESSION test_session ON SERVER
GO
-- REMOVES THE EVENT SESSION FROM THE SERVER
select CAST(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file
('c:\temp\data2*.xel','c:\temp\data2*.xem', null, null)
This last statement will output one event with the following data:
<event name="error_reported" package="sqlserver" id="100" version="1" timestamp="2010-03-05T23:15:05.481Z">
<data name="error">
<value>50000</value>
<text />
</data>
<data name="severity">
<value>2</value>
<text />
</data>
<data name="state">
<value>1</value>
<text />
</data>
<data name="user_defined">
<value>true</value>
<text />
</data>
<data name="message">
<value>PUBLISH</value>
<text />
</data>
<action name="sql_text" package="sqlserver">
<value>-- THIS ERROR WILL BE PUBLISHED
RAISERROR (N'PUBLISH', 2, 1, 7, 3, N'abcde');
</value>
<text />
</action>
</event>
If you see more events, check if you have deleted previous event files. If so, please delete them manually or use the following script:
-- Deletes previous event files
EXEC SP_CONFIGURE
GO
EXEC SP_CONFIGURE 'xp_cmdshell', 1
GO
RECONFIGURE
GO
XP_CMDSHELL 'del c:\temp\data*.xe*'
GO
With this simple tutorial, we have learned the basics of Extended Events. With this information you can alter the session to monitor, filter and attach information (sqltext, login user, ...) to the errors happening in your server to detect:
Login Failures: You can capture events with error messages as 18456 and see how many happened.
Attach more information to specific errors: You can specify additional actions to attach to specific error_events, such as sqltext, callstack or even a dump of a single or multiple threads.
All the Extended Event Objects (Events, Actions, Targets, Predicate Sources and Predicate Targets) are exposed in sys.dm_xe_objects. For other DMV or system catalog views of SQL Server Extended Events check this post: