Light Weight Monitoring using Extended Events

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.