Light Weight Monitoring using Extended Events


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.

Object co-existance and sharing in sessions.

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

How A Session Works:

:Detailed extended events architecture

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),

  • 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.


Let us say we want to find
out which queries are causing page

The next step is to find out which
 columns we can access for the page split event.

SELECT c.column_id ,,c.column_type,c.description,c.type_name ,c.object_name FROM

 sys.dm_xe_object_columns  c

WHERE [object_name] = ‘page_split’

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]
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

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’,


 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:

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.


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:



  , 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


(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.


Pages: 1 2 3


No comments yet... Be the first to leave a reply!