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 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 functionality to be executed every time a set of
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 ring buffer.
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 that lets 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 they are used additional data (like
the TSQL Statement related to an event,
the session,
the user) or generate a mini dump.
- Predicates: Predicates express 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 be also used by
the predicates. 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: This pair always has to be first an event field or predicate source and then a value
Let’s do another small Example. We will trigger errors but we will use
the ones that have severity >= 10 and
the error message != ‘filter’. To verify this 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 = 2 and (not (message = 'filter')))
ADD TARGET package0.asynchronous_file_target
-- CONFIGURES
THE 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 EVENT WILL BE FILTERED BECAUSE SEVERITY != 2
RAISERROR (N'PUBLISH', 1, 1, 7, 3, N'abcde');
GO
-- THIS EVENT 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 run
-- 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
or delete them manually.
More Info on Events: Extended Event
Events
More Info on Targets: Extended Event Targets
More Info on Sessions: Extended Event Sessions
More Info on Actions: Extended Event Actions
More Info on Predicates: Extended Event Predicates
Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!
