ExtendedEvents-display different targets using extended events

Extenextendevents: returns all targets of the server using the following query in different target examples of the extended Event: SELECTp. nameASPackageName, o. nameASTargetName, o. descriptionASTargetDescriptionFROMsys. extends _

Extended SQL Server Events: returns all targets of the Server using the following query in different target examples of Extended Events: SELECTp. nameASPackageName, o. nameASTargetName, o. descriptionASTargetDescriptionFROMsys. extends _

SQL Server Extended Events )-- Examples of different targets using extended events

The following query returns all targets of the server:

SELECT p.name AS PackageName,o.name AS TargetName,o.description AS TargetDescriptionFROM sys.dm_xe_objects oINNER JOIN sys.dm_xe_packages pON o.package_guid = p.guidWHERE o.object_type = 'target'AND (p.capabilities IS NULL OR p.capabilities <> 1)ORDER BY PackageName, TargetName;

Each target has a configurable parameter list. Some columns are mandatory and some are optional. The following query returns the parameters of all available targets on the server.

SELECT p.name AS PackageName,o.name AS TargetName,c.name AS ParameterName,c.type_name AS ParameterType,case c.capabilities_descwhen 'mandatory' then 'yes'else 'no'end AS [Required]FROM sys.dm_xe_objects oINNER JOIN sys.dm_xe_packages pON o.package_guid = p.guidINNER JOIN sys.dm_xe_object_columns cON o.name = c.object_nameWHERE o.object_type = 'target'AND (p.capabilities IS NULL OR p.capabilities <> 1)ORDER BY PackageName, TargetName, [Required] desc;

Actions can be bound to specific events or extended events. They are synchronized to the thread that triggers the event, so performance may be affected in some cases. Actions can be used:

L aggregate event data

L append additional data to event data

L capture stack dump and observe data

L use variables to store status information in the current context

L stop the service and execute the creation and debugging checkpoint

Because actions are executed synchronously, they should be used to capture additional information as needed. Some actions such as package0.debug _ break should not be used in the production environment. The following query returns all available actions on the server.

SELECT p.name AS PackageName,o.name AS ActionName,o.description AS ActionDescriptionFROM sys.dm_xe_objects oINNER JOIN sys.dm_xe_packages pON o.package_guid = p.guidWHERE o.object_type = 'action'AND (p.capabilities IS NULL OR p.capabilities & 1 = 0)ORDER BY PackageName, ActionName;

Event Data is concatenated by a collection of bytes. To describe the data, the type provides a set of length and byte classification for the extended Event engine. Most columns have recognizable types, such as int32, unicode_string, float32, and boolean, which are quite common in most programming languages. However, there are also some special types in SQL Server, such as callstack, sos_context, and database_context.

The following query returns all available types.

SELECT p.name AS PackageName,o.name AS TypeName,o.description AS TypeDescriptionFROM sys.dm_xe_objects oINNER JOIN sys.dm_xe_packages pON o.package_guid = p.guidWHERE o.object_type = 'type'AND (p.capabilities IS NULL OR p.capabilities & 1 = 0)ORDER BY PackageName, TypeName;

Each column in the object load has a corresponding data type. This is obvious when defining predicates. Type is not part of the session definition.

A predicate is used to dynamically pass events when an event is triggered to determine which events are triggered in accordance with the Rules. This makes event sessions more targeted to specific problems, rather than collecting all event traces and then filtering. This is very important for specific targets like ringbuffer, because when the memory is full, the event will begin to be truncated.

A predicate is a Boolean expression used to filter global state data and local event data. Local session data is defined by the load or column of an event in its column architecture. The global status data is defined by an available object or a column that is called a word. You can find it from the object_type in sys. dm_xe_objects view that is equal to pred_source.

SELECT p.name AS PackageName,o.name AS ColumnName,o.description AS ColumnDescriptionFROM sys.dm_xe_objects oINNER JOIN sys.dm_xe_packages pON o.package_guid = p.guidWHERE o.object_type = 'pred_source'AND (p.capabilities IS NULL OR p.capabilities & 1 = 0)ORDER BY PackageName, ColumnName;

In addition, some predicates can be stored to allow events to be triggered every N counts, or the first occurrence of N counts. This can be used for sample-based event capture. It can be used to remove a few events from frequent events.

Predicates are implemented through standard mathematical operations. Some operation sets are called comparison operators that can be used for filtering. The following query can find a set of comparison operators:

SELECT p.name AS PackageName,o.name AS ComparatorName,o.description AS ComparatorDescriptionFROM sys.dm_xe_objects oINNER JOIN sys.dm_xe_packages pON o.package_guid = p.guidWHERE o.object_type = 'pred_compare'AND (p.capabilities IS NULL OR p.capabilities & 1 = 0)ORDER BY PackageName, ComparatorName;

The comparison operator syntax is:

Package_name.predicate_comparator_name ( , )

The comparison operator must have the same data type as the source column. The data type of the source column cannot be converted. The following script shows how to use the comparison operator:

Ring BufferExample

The ring buffer in the memory temporarily stores event data to the memory.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='BufferManagerWatcher')DROP EVENT SESSION [BufferManagerWatcher] ON SERVER;CREATE EVENT SESSION [BufferManagerWatcher]ON SERVERADD EVENT sqlserver.buffer_manager_page_life_expectancy(WHERE (([package0].[less_than_equal_uint64]([count],(3000)))))ADD TARGET package0.ring_buffer(SET max_memory=4096)

The preceding event is triggered when Page Life Expectancy falls below 3000 microseconds.

Matching provides an internal value for searching data so that the end user can know the meaning of the value. The following query provides available matching and value definitions:

SELECT name, map_key, map_valueFROM sys.dm_xe_map_valuesORDER BY name, map_key

Matching is a representation of extended event context like a type. They are not directly used to extend the definition of event sessions. They provide a search channel allocated to the predicates to filter events or calculate the value of the target data.

A session is a collection of events and related actions, predicates, and Session targets. One session can use one or more events and targets.

650) this. width = 650; "title =" image "style =" border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; "alt =" image "src =" http://www.68idc.cn/help/uploads/allimg/151111/121RG453-0.jpg "border =" 0 "height =" 205 "/>

Sessions have independent event and target configurations. In the preceding example, the ring_buffer target is used for two sessions but different memory configurations are used. An event in a session is defined as an event of the same name in different sessions of other instances.

Event FileExample

Errorlog_written event: triggered when an SQL Server Error Log is written.

Step 1:

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='FileTargetDemo')DROP EVENT SESSION [FileTargetDemo] ON SERVER;CREATE EVENT SESSION [FileTargetDemo]ON SERVERADD EVENT sqlserver.errorlog_writtenADD TARGET package0.asynchronous_file_target(SET filename='c:\FileTargetDemo.etl', metadatafile='c:\FileTargetDemo.mta')ALTER EVENT SESSION [FileTargetDemo]ON SERVERSTATE=START

Step 2:

DBCC CHECKDB(‘AdventureWorks2012’)

Step 3:

SELECT name, target_name, CAST(target_data AS XML) target_data,(SELECT c.column_name + '=' + c.column_value + ', 'FROM sys.dm_xe_session_object_columns cWHERE s.address = c.event_session_addressAND t.target_name = c.object_nameAND c.object_type = 'target'ORDER BY column_idfor xml path('')) AS optionsFROM sys.dm_xe_sessions sINNER JOIN sys.dm_xe_session_targets tON s.address = t.event_session_addressWHERE s.name = 'FileTargetDemo' SELECT *FROM sys.fn_xe_file_target_read_file('c:\FileTargetDemo*etl', 'c:\FileTargetDemo*mta', null, null)

Step 4:

ALTER EVENT SESSION [FileTargetDemo]ON SERVERSTATE=STOP

Example of Synchronous Event Counter

Records the number of events triggered in a session

Step 1:

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='EventCounterTargetDemo')DROP EVENT SESSION [EventCounterTargetDemo] ON SERVER;CREATE EVENT SESSION [EventCounterTargetDemo]ON SERVERADD EVENT sqlserver.sql_statement_starting,ADD EVENT sqlserver.sql_statement_completedADD TARGET package0.synchronous_event_counterWITH (MAX_DISPATCH_LATENCY = 5 SECONDS)ALTER EVENT SESSION [EventCounterTargetDemo]ON SERVERSTATE=START

Step 2:

SELECT name, target_name, CAST(target_data AS XML) target_dataFROM sys.dm_xe_sessions sINNER JOIN sys.dm_xe_session_targets tON s.address = t.event_session_addressWHERE s.name = 'EventCounterTargetDemo'

Step 3:

ALTER EVENT SESSION [EventCounterTargetDemo]ON SERVERSTATE=STOP

Event PairingExample

Find the events that trigger the startup but do not trigger the corresponding completed events. The Transaction Begin and Transaction End events are useful for tracking Lock Acquired and Lock Released event pairs.

Step 1:

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='PairMatchingTargetDemo')DROP EVENT SESSION [PairMatchingTargetDemo] ON SERVER;CREATE EVENT SESSION [PairMatchingTargetDemo]ON SERVERADD EVENT sqlserver.database_transaction_begin(ACTION (sqlserver.session_id)),ADD EVENT sqlserver.database_transaction_end(ACTION (sqlserver.session_id))ADD TARGET package0.pair_matching(SET begin_event='sqlserver.database_transaction_begin', begin_matching_actions='sqlserver.session_id', end_event='sqlserver.database_transaction_end', end_matching_actions='sqlserver.session_id')WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);ALTER EVENT SESSION [PairMatchingTargetDemo]ON SERVERSTATE=START;

Step 2:

SELECT name, target_name, CAST(target_data AS XML) target_dataFROM sys.dm_xe_sessions sINNER JOIN sys.dm_xe_session_targets tON s.address = t.event_session_addressWHERE s.name = 'PairMatchingTargetDemo'

Step 3:

ALTER EVENT SESSION [PairMatchingTargetDemo]ON SERVERSTATE=STOP;

Event BucketingExample

Used to group events based on the event load column or action group. The following example uses wait_type to group wait_info events.

Step 1:

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='BucketingTargetDemo')DROP EVENT SESSION [BucketingTargetDemo] ON SERVER;CREATE EVENT SESSION [BucketingTargetDemo]ON SERVERADD EVENT sqlos.wait_infoADD TARGET package0.asynchronous_bucketizer(SET filtering_event_name='sqlos.wait_info', source_type=0, source='wait_type')WITH (MAX_DISPATCH_LATENCY = 2 SECONDS)ALTER EVENT SESSION [BucketingTargetDemo]ON SERVERSTATE=START

Step 2:

SELECT name, target_name, CAST(target_data AS XML) target_dataFROM sys.dm_xe_sessions sINNER JOIN sys.dm_xe_session_targets tON s.address = t.event_session_addressWHERE s.name = ' BucketingTargetDemo '

Step 3:

ALTER EVENT SESSION [BucketingTargetDemo]ON SERVERSTATE=STOP;

Event Tracing for Windows (ETW)Example

The ETW target file is an external file and will not be used internally by SQL Server. The external ETW is usually used to track external events related to internal events.

Step 1:

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='ETWTargetDemo')DROP EVENT SESSION [ETWTargetDemo] ON SERVER;CREATE EVENT SESSION [ETWTargetDemo]ON SERVERADD EVENT sqlserver.sql_statement_starting (WHERE sqlserver.database_id = 2),ADD EVENT sqlserver.sql_statement_completed (WHERE sqlserver.database_id = 2),ADD EVENT sqlserver.file_written (WHERE sqlserver.database_id = 2),ADD EVENT sqlserver.file_write_completed (WHERE sqlserver.database_id = 2)ADD TARGET package0.etw_classic_sync_target (SET default_etw_session_logfile_path = N'C:\sqletwtarget.etl')

At the same time, start an ETW Session on the Windows command line to collect external ETW data for disk and file operations

Logman start "NT Kernel Logger"-p "Windows Kernel Trace" (disk, file)-ets-o C: \ systemetw. etl-bs 1024-ct system

Step 2:

ALTER EVENT SESSION [ETWTargetDemo]ON SERVERSTATE=START

Step 3:

Import batch data to tempdb.

Step 4:

ALTER EVENT SESSION [ETWTargetDemo]ON SERVERSTATE=STOP

Step 5:

Stop the external etw session of the system etw collection and expansion event from the command line.

Logman update "NT Kernel Logger"-fd-ets

Logman stop "NT Kernel Logger"-ets

Logman update XE_DEFAULT_ETW_SESSION-fd-ets

Logman stop XE_DEFAULT_ETW_SESSION-ets

Step 6:

Use the tracerptcommand to connect two. ETL files to generate a. CSV file.

Tracerpt c: \ sqletwtarget. etl c: \ systemetw. etl-y-o sqletw_results.csv

Note:

C: \> logman /?

C: \> tracerpt /?