Sessions
Sessions are groupings of
events, actions, predicates and targets. Any events can be linked with any
action within a session. This means that we can have one event defined in
multiple sessions independently of each other.
Source: MSDN
For each session we have to
define parameters such as how much memory is used for event buffering, how many
events can be disregarded if there is memory pressure, how long will the events
stay in the buffer before being sent to the target, whether the session will be
auto started when SQL Server service starts and if we want to enable causality
tracking.
How A Session Works:
:
Source : MSDN
Each Windows process can have
one or more modules (Win32 process, Win32 module). These are also known as
binaries or executable modules. In this case the module is sql server.
Each of the Windows process
modules can contain one or more Extended Events packages
Inside a host process there
can only be one instance of the Extended Events engine (Extended event engine),
which:
- Manages some aspects of the
session (for example, enumerating sessions). - Handles dispatching
(Dispatcher). This is similar to a thread pool. - Handles memory buffers
(Buffer) for events. When buffers are filled, the buffers are dispatched
to targets.
Implementation
Let us say we want to find
out which queries are causing page
split.
1.
The next step is to find out which
columns we can access for the page split event.
SELECT c.column_id ,c.name,c.column_type,c.description,c.type_name ,c.object_name FROM
sys.dm_xe_object_columns c
WHERE [object_name] = ‘page_split’
2.
You can see that above will only provide
the file_id and page_id. However, you may need additional information such as
the database name, query executed, user etc.
SELECT xp.[name]
PackageName, xo.name
EventName, xo.description
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp
WHERE xp.[guid] = xo.[package_guid]
AND xo.[object_type] = ‘action’
AND xo.[name] IN (‘database_id’,’sql_text’,’nt_username’,’transaction_id’)
4. Next, select a target from
the available six targets. For the simplicity and better performance we will
select asynchronous target, such as event file.
5. Next create the Session:
CREATE EVENT SESSION xe_event_page_split ON
SERVER
ADD EVENT sqlserver.page_split
(ACTION (sqlserver.database_id, sqlserver.sql_text,sqlserver.nt_username,sqlserver.transaction_id)
WHERE sqlserver.database_id > 4)
ADD TARGET package0.asynchronous_file_target
(SET FILENAME=N’c:xexe_event_page_split.xel’,
metadatafile=N’c:xexe_event_page_split.xem’);
xel is the log file while
xem is the meta data file that describes the output log file and allows the
events and associated actions to be parsed correctly.
6. By default, the session
will not start and you will have to start the session manually:
ALTER
EVENT SESSION
xe_event_page_split ON SERVER
STATE = start;
7. The event is now started and
we need to find some data. I used the same page split as in the page split
document linked to above.
8. Verify whether data is in
the files by executing the following query.
SELECT COUNT(*)
FROM sys.fn_xe_file_target_read_file
(‘c:xexe_event_page_split*.xel’, ‘c:xexe_event_page_split*.xem’, null, null)
You might notice that there
is * in the above query which is because, when the file is created there will
be some numbers added to the file.
9. Next, view the data. To
read the XML file:
SELECT
xml_data
, xml_data.value(‘(/event[@name=”page_split”]/@timestamp)[1]’,’datetime’) Datetime
, xml_data.value(‘(/event/data[@name=”file_id”]/value)[1]’,’int’) file_id
, xml_data.value(‘(/event/data[@name=”page_id”]/value)[1]’,’int’) page_id
, xml_data.value(‘(/event/action[@name=”database_id”]/value)[1]’,’int’) database_id
, xml_data.value(‘(/event/action[@name=”transaction_id”]/value)[1]’,’int’) transaction_id
, xml_data.value(‘(/event/action[@name=”sql_text”]/value)[1]’,’varchar(max)’) sql_text
FROM
(SELECT object_name AS EVENT, CONVERT(XML, event_data) AS xml_data
FROM sys.fn_xe_file_target_read_file
(‘c:xexe_event_page_split*.xel’, ‘c:xexe_event_page_split*.xem’, null, null)
) v
ORDER BY Datetime
The output of this is,
By integrating this with DBCC
commands and other sys tables, you will be able to access information like
database name, index etc.
NB: in the xml_data column
you can see all the events attributes.