SQL Server – Extended Events – Metadata

Background

In our last post, needed to back port an Extended Event Session developed in SQL Server 2014 to v2008/R2.

Unfortunately, the exported script did not work in our initial attempt to apply against the v2008-R2 Instance.

I thus had to go back and see which events and columns were availed in the earlier edition, v2008-R2.

 

Metadata

General ( Plumbing )

Here we cover plumbing stuff.

Generalized information that is applicable to the SQL Server Version.

Get List of Packages

Code



SELECT
		 [@@VERSION] = @@VERSION

SELECT 
		  [packageName] = tblPkg.[name]
		, [packageDescription] = tblPkg.[description]

FROM   sys.dm_xe_packages tblPkg

order by
		  tblPkg.[name] asc

 

Output

v2008/R2

v2014/R2

Explanation

  1. Compared to v2008/2008-R2, there are many more packages in v2014

 

 

Get List of Objects

Overview

Let us get objects that have “completed” in their name.

Code




DECLARE @packageName varchar(120)
DECLARE @objectName  varchar(120)

set @packageName = 'sqlserver'
set @objectName  = 'completed'

SELECT
		 [@@VERSION] = @@VERSION

SELECT 
		  [packageName] = tblPkg.[name]
		, [packageDescription] = tblPkg.[description]
		, [objectName] = tblObj.[name]
		, tblObj.[object_type]

FROM   sys.dm_xe_packages tblPkg

INNER JOIN  sys.dm_xe_objects tblObj

	ON  tblPkg.[guid] = tblObj.[package_guid]

WHERE tblPkg.[name] = @packageName

AND   tblObj.[name] like '%'  + @objectName + '%'

order by
		  tblPkg.[name] asc
		, tblObj.[name]



 

Output

v2008/R2

 

v2014

 

Explanation

In v2008, v2008-R2, there are 5 events that have completed in their name.

In v2014, there are 12 events with same signature.

 

 

Get List of Event Columns

Code



DECLARE @packageName varchar(120)
DECLARE @objectName  varchar(120)

set @packageName = 'sqlserver'
--set @objectName  = 'sql_batch_completed'
set @objectName  = 'completed'

SELECT
		 [@@VERSION] = @@VERSION

SELECT 
		  [packageName] = tblPkg.[name]
		, [packageDescription] = tblPkg.[description]
		, [objectType] = tblObj.[object_type]
		, [objectName] = tblObj.[name]
		, [columnName] = tblObjCol.[name]
		, [typeName]   = tblObjCol.[type_name]
		--, tblObjCol.*

FROM   sys.dm_xe_packages tblPkg

INNER JOIN  sys.dm_xe_objects tblObj

	ON  tblPkg.[guid] = tblObj.[package_guid]

INNER JOIN sys.dm_xe_object_columns tblObjCol

	ON tblObj.name = tblObjCol.object_name

WHERE tblPkg.[name] = @packageName

AND   tblObj.[name] like '%' + @objectName + '%'

/*
	Exclude objects that start with file such as :
		a) file_read_completed 
		b) file_write_completed
*/
AND   tblObj.[name] not like 'file' + '%' + @objectName + '%'

/*
AND   tblObj.[name] in
		(
			  'rpc_completed'
			, 'sp_statement_completed'
			, 'sql_statement_completed'
		)
*/
order by
		  tblPkg.[name] asc
		, tblObj.[name]
		, tblObjCol.[column_type]
		, tblObjCol.[name]


Output

v2008/R2

 

 

v2014

Explanation

In v2008, v2008-R2, there are 33 event columns that have completed in their name.

In v2014, there are 132 columns with same signature.

 

Actual

Here we cover the sessions that are actually registered on the targeted instance.

Get List of Extended Event Sessions that are currently running

Code


SELECT
		  [session]
			= tblDMXES.[name]

		, [createTS]
			= convert(varchar(30), tblDMXES.[create_time], 100)
		
FROM sys.dm_xe_sessions AS tblDMXES

;

Output

 

 

Get Extended Event Sessions Targets

Code


select 
		  [session] = tblDMXES.[name]
		, [targetName] = tblDMXEST.target_name
		--, [packageGUID] = tblDMXEST.[target_package_guid]
		, [executionCount] = tblDMXEST.[execution_count]
		, [executionDurationInms] = tblDMXEST.[execution_duration_ms]
		--, tblDMXEST.*
		
from   sys.dm_xe_sessions AS tblDMXES

inner join sys.dm_xe_session_targets tblDMXEST

		on tblDMXES.address = tblDMXEST.event_session_address


 

Output

Get Extended Event Sessions Target Files

Get Extended Event Sessions Target Files – Vertical

Code


declare @session sysname

set @session = 'performance'

SELECT 
		  [session] = tblDMXES.[name]
		, [object]  = tblDMXESOC.[object_name]
		, [column]  = tblDMXESOC.column_name
		, [value]   = tblDMXESOC.column_value

FROM sys.dm_xe_sessions tblDMXES

JOIN sys.dm_xe_session_object_columns tblDMXESOC

    ON tblDMXES.[address] = tblDMXESOC.event_session_address

WHERE tblDMXESOC.[object_name] in
				(
					   'asynchronous_file_target' -- v2008, v2008-R2
					 , 'event_file' -- v2014
				)

AND	  (
			(
			       ( @session is null )
				or ( tblDMXES.[name] like @session + '%' )			
			)
	  )

ORDER BY
		  tblDMXES.[name]
		, tblDMXESOC.column_name


Output

Output – v2008/R2

Output – v2014

Explanation

  1. Object
    • In v2008-R2, Object name is asynchronous_file_target
    • In v2014, Object name is event_file

Get Extended Event Sessions Target Files – Horizontal

Code


declare @session sysname

set @session = 'performance'

;with cteEventFile
(
	  [session]
	, [column] 
	, [value]  
)
as
(
	SELECT 
			    [session] = tblDMXES.[name]
			  , [column]  = tblDMXESOC.[column_name]
			  , [value]   = tblDMXESOC.[column_value]

	FROM sys.dm_xe_sessions tblDMXES

	JOIN sys.dm_xe_session_object_columns tblDMXESOC

		ON tblDMXES.[address] = tblDMXESOC.event_session_address


	WHERE tblDMXESOC.[object_name] in
					(
						   'asynchronous_file_target' -- v2008, v2008-R2
						 , 'event_file' -- v2014
					)


)
select 
			  [session]
			, [filename]
			, [metadatafile]
			, [maxFileSize] = max_file_size
			, [maxRollOverFiles] = max_rollover_files
			, increment


from   
		(
		
			select *

			from   cteEventFile cteEF

			WHERE  (

						(
							   ( @session is null )
							or ( cteEF.[session] like @session + '%' )			
						)
				  )

		) as [src]


PIVOT
	(
		MAX(value)
		FOR [column] in
				(
					  [filename]
					, metadatafile
					, max_file_size
					, max_rollover_files
					, increment
				)
	) tblPivot


Output

Output – v2008/R2

Output – v2014

List Event Predicates  ( Top Level )

Code



select 
		  [session] = tblDMXES.[name]
		, [eventName] = tblDMXESE.event_name
		, [eventPredicate] = tblDMXESE.event_predicate
		--, tblDMXESE.*
		
from   sys.dm_xe_sessions AS tblDMXES

inner join sys.dm_xe_session_events tblDMXESE

		on tblDMXES.[address] = tblDMXESE.[event_session_address]

Output

 

List Event Predicates  ( Detail )

 

View XML

Let us review the XML in the event predicate column.

We use the XPath Tester / Evaluator @ freeformatter.com ( Link )

 

XML Input & Xpath Expression

Once we pass in //leaf as the XPath expression, we get the output pasted below:

XPath Result

 

Code

Outline

It is easier to write the SQL Query along with its XPATH once we try things out in an Xpath Query tool.

Sample


; with cteEventPredicate
(
	  [session]
	, [eventName]
	, [eventPredicate]
)
as
(
	select 
		  [session] = tblDMXES.[name]
		, [eventName] = tblDMXESE.event_name
		, [eventPredicate] = cast(tblDMXESE.event_predicate as xml)
		--, tblDMXESE.*
		
	from   sys.dm_xe_sessions AS tblDMXES

	inner join sys.dm_xe_session_events tblDMXESE

			on tblDMXES.[address] = tblDMXESE.[event_session_address]

)
, cteClause
(
	  [session]
	, [eventName]
	, [attribute] 
	, [comparator] 
	, [value] 
)
as
(

	select 
			  cteEP.[session]
			, cteEP.[eventName]
			, [attribute] = leaves.leaf.value('(global/@name)[1]', 'varchar(100)') 
			, [comparator] = leaves.leaf.value('(comparator/@name)[1]', 'varchar(100)') 
			, [value] = leaves.leaf.value('(value)[1]', 'varchar(100)') 

	from   cteEventPredicate cteEP

	cross apply cteEP.eventPredicate.nodes( N'//leaf') as leaves(leaf)

)
select *

from   cteClause cteC

where  cteC.[attribute] is not NULL


Output

 

Dedicated

Dedicated to Microsoft for strong and fruitful commitment to their DB Products.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s