Extended Events infrastructure characteristics in C#

Generator Code-128 in C# Extended Events infrastructure characteristics

-- Getting the information DECLARE @MyXML XML; SELECT @MyXML = CAST(T.target_data AS XML) FROM sys.dm_xe_session_targets T JOIN sys.dm_xe_sessions S ON S.address = T.event_session_address JOIN sys.server_event_sessions ES ON S.name = ES.name WHERE T.target_name = 'synchronous_bucketizer' AND S.name = 'GetLongRunningQueries'; -- Display the information as raw XML SELECT @MyXML AS XML_Representation; -- Display the information using XQuery for a better output SELECT T.Slot.value('@count', 'int') [NbmOfExecutions], T.Slot.value('value[1]', 'varchar(MAX)') [QueryText] FROM @MyXML.nodes('/BucketizerTarget/Slot') AS T(Slot) -- Clean-Up DROP PROCEDURE usp_WaitForATime; GO ALTER EVENT SESSION GetLongRunningQueries ON SERVER STATE=STOP; GO DROP EVENT SESSION GetLongRunningQueries ON SERVER; GO

XML_Representation ---------------------------------------------<BucketizerTarget truncated="0" buckets="256"> <Slot count="5" trunc="1"> <value>EXEC usp_WaitForATime;</value> </Slot> </BucketizerTarget> NbmOfExecutions QueryText --------------- ---------------------5 EXEC usp_WaitForATime;

In this chapter, I ll use the code from listing 1 to explain the following concepts: Events Points of interests in the code of an application such as SQL Server. In the example, we re interested in the event fired when a T-SQL statement is finished (sqlserver.sql_statement_completed). Packages Containers for Extended Events objects (events, targets, actions, types, predicates, and maps). In our example we use package0, which contains Extended Events system objects.

Targets The event consumers. In the case of our example, we use the Event Bucketing Target, which retains data (the sql_text) for the sql_statement_ completed event. Actions Programmatic responses or a series of responses to an event (such as capturing the T-SQL stack collection or generating a user dump). For the previous example, the action is collecting the sql_text column for the sql_statement_completed event. Other possible actions include capturing a stack dump, aggregating event data, appending information to event data, or storing state information. Predicates Boolean expressions that filter the events. In our example, the sqlserver.database_id predicate filters the events that occurred in the database with the ID of 1 (the master database). Types Encapsulate the characteristics of collected data. For example, the type of the sqlserver.database_id predicate is pred_source. Maps Associate internal values to more meaningful descriptions. For example, the internal value 3 for lock_resource_type is mapped to the string FILE (which is self explanatory). The complete mappings can be obtained by running the following code: SELECT name, map_key, map_value FROM sys.dm_xe_map_values Sessions Contain targets and the enabled events. The next section of the chapter goes deeper in explaining the XEVENT elements.

If you need more details about the Extended Events architecture, this section is for you. I also encourage you to use Books Online for additional information.

The SQL Server Extended Events engine is a collection of services and objects that enables you to define events, process event data, and manage the XEVENT sessions, services, and objects. One characteristic that makes XEVENT a general event infrastructure is that the events engine is event-agnostic. The processes that use the engine define the interaction with the engine and supply event points and actions to take when an event fires. Thus any event can be bound to any target. Figure 1 represents the high-level architecture of the SQL Server Extended Events engine: The SQL Server Extended Events engine is integrated in the SQL Server database engine. A host process contains only one instance of the Extended Events engine. It handles session management, dispatching, and memory buffers for events. A session can contain enabled events, targets, and buffers. Instances of targets may be created and added to a session.

The memory buffers for the events are dispatched to targets when they re filled. Dispatchers are worker threads. Each Windows process can have one or more modules (a Win32 process or Win32 module). Each of the Windows process modules can contain one or more Extended Events packages, which contain one or more Extended Events objects (types, targets, actions, maps, predicates, and events).