SQL Server – Extended Events – Querying Data – Day 1

Background

Once we have our Extended Events Session Setup and it is collecting data, we should occasionally check in and review the collected data.

 

Sample

XML Data

 

Query

Outline

We have a sample query below, here is what it does:

  1. XEL & XEM
    • It sets the @fileXEL variable to the folder and file signature for our XEL files
    • It sets the @fileXEM variable to the folder and file signature for our XEM files
  2. It creates a Common Table Expression (CTE)
    • invokes sys.fn_xe_file_target_read_file
      • Passes along the XEL and XEM file signatures
      • It discard records that have “UPDATE STATISTICS
  3. It uses XPATH and breaks the record using //event
  4. Parses data
    • XML Fragment
      • query(‘.’)
        • Sample data
          • <event name=”rpc_completed” package=”sqlserver” id=”64″ version=”1″ timestamp=”2017-04-01T16:14:45.363Z”><data name=”cpu”><value>0</value><text /></data></event>
    • timestamp
      • value(‘@timestamp’, ‘datetimeoffset’)
        • Sample Data
          • 2017-04-01 16:14:45.3630000 +00:00
    • collect_system_time
      • .query(‘/event/action[@name=”collect_system_time”]/value/text()’)
        • Sample Data
          • 131355368858528137
      • .query(‘/event/action[@name=”collect_system_time”]/text/text()’)
        • Sample Data
          • 2017-04-01T16:14:45.852Z
    • session_id
      • .query(‘/event/action[@name=”session_id”]/value/text()’)
        • Sample Data
          • 54
    • client_host_name
      • .query(‘/event/action[@name=”client_hostname”]/value/text()’)
        • Sample Data
          • LAB-DB-SQL
    • username
      • .query(‘/event/action[@name=”username”]/value/text()’)
        • Sample Data
          • tomw
    • client_app_name
      • .query(‘/event/action[@name=”client_app_name”]/value/text()’)
        • Sample Data
          • Report Server
    • sql_text
      • .query(‘/event/action[@name=”sql_text”]/value/text()’)

 

Code


declare @fileXEL sysname
declare @fileXEM sysname


--set @fileXEL = 'E:\Microsoft\SQLServer\ExtendedEvent\Events\Performance\Performance_0_131356693200370000.xel'
set @fileXEL = 'E:\Microsoft\SQLServer\ExtendedEvent\Events\Performance\Performance*.xel'
set @fileXEM = 'E:\Microsoft\SQLServer\ExtendedEvent\Events\Performance\Performance*.xem'

; with cte
(
	  [objectName] 
	, [eventData] 
)
as
(
	SELECT top 10 

		  [objectName] = tblFNXE.[object_name]
		, [eventData] = cast(tblFNXE.event_data as xml)

	FROM sys.fn_xe_file_target_read_file
		(
			  @fileXEL
			, @fileXEM
			, null
			, null
	   ) tblFNXE

	where tblFNXE.event_Data not like '%UPDATE STATISTICS%'

)
select 

		  [objectName] = cte.[objectName]

		, [eventData] = cast(cte.eventData as xml)
		, [XMLFragment] = [event].[node].query('.')

		--timestamp on root node
		, [tsAsDateTime] = [event].[node].value('@timestamp', 'datetimeoffset')		

		--collect system time
		, [systemTSAsValue] = ([event].[node].query('/event/action[@name="collect_system_time"]/value/text()'))
		, [systemTSAsText] = ([event].[node].query('/event/action[@name="collect_system_time"]/text/text()'))

		--session ID
		, [sessionID] = [event].[node].query('/event/action[@name="session_id"]/value/text()')

		-- client Host Name
		, [clientHostName] = [event].[node].query('/event/action[@name="client_hostname"]/value/text()')

		-- username
		, [username] = [event].[node].query('/event/action[@name="username"]/value/text()')

		-- client App Name
		, [clientAppName] = [event].[node].query('/event/action[@name="client_app_name"]/value/text()')

		-- SQL Text
		, [sqlText] = [event].[node].query('/event/action[@name="sql_text"]/value/text()')

from   cte

cross apply cte.eventData.nodes(N'//event') event(node)



Output

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