SQL Server – Extended Events – Internal Session Definitions

Background

Wanted to track Extended Events, but ran into a stumbling block.

And, we go…

 

Metadata

Extended Events

Active Sessions ( sys.dm_xe_sessions )

Code


select 
		  [sessionName] = tblXES.[name]
		, [sessionCreateTime] = tblXES.[create_time]
		, [sessionSource] = tblXES.[session_source]
		--  tblXES.*

from   sys.dm_xe_sessions tblXES

Output

Explanation

  1. session_source
    • We can group Extended Event based on Source
      • Source :-
        • server
        • internal
    • In our case
      • server
        • system_health
          • MSFT Defined
        • performance
          • Defined by us
      • internal
        • hkenginexesession
          • Added in SQL Server v2016
          • Tracks Hekaton ( “In-Memory Database” Events )
        • sp_server_diagnostics session
          • Added in SQL Server v2014
          • SQL Server Diagnostic Events

 

Extended Event Definitions ( sys.server_event_sessions )

Code


select 
		  [sessionID] = tblSES.[event_session_id]

		, [extendedEvent]
			 = tblSES.[name]

		, [eventRetentionMode] 
			= tblSES.[event_retention_mode]

		, [trackCasualty]
			 = tblSES.[track_causality]

		, [startupState] 
			= tblSES.[startup_state]

		--, tblSES.*

from   sys.server_event_sessions tblSES

Output

Explanation

  1. sessionID
    • event_session_id
      • Starts at 65534
  2. Extended Event
    • name
  3. Event Retention Mode
    • Column :- event_retention_mode
  4. Track Casualty
    • track_causality
  5. Startup Start
    • startup_state

Tie things together

Let us join our two sources,  sys.server_event_sessions & sys.dm_xe_sessions.

 

Inner Join – sys.server_event_sessions & sys.dm_xe_sessions

Code

select 
		  [sessionID] =  tblSES.[event_session_id]
		, [extendedEvent] = tblSES.[name]
		, [eventRetentionMode] = tblSES.[event_retention_mode]
		, [trackCausality ] = tblSES.[track_causality]
		, [startupState] = tblSES.[startup_state]

		 , [sessionCreateTime] = tblXES.[create_time]
		 , [sessionSource] = tblXES.[session_source]

from  sys.server_event_sessions tblSES

left outer join sys.dm_xe_sessions tblXES

        on tblSES.[name]  = tblXES.[name]

Output

Explanation

  1. sessionID
    • event_session_id
  2. Extended Event
    • name
  3. Event Retention Mode
    • Column :- event_retention_mode
  4. Track Casualty
    • Column :- track_causality
  5. Startup State
    • Column :- startup_state
  6. sessionCreateTime
    • Column :- sys.dm_xe_sessions.create_time
  7. sessionSource
    • Column :- sys.dm_xe_sessions.session_source

Workaround ( using cte ) – sys.server_event_sessions & sys.dm_xe_sessions

Code

; with cteExtendedEvent
(
      [name]
    , [id]
    , [sessionSource]
)
as
(

    /*
        Get Defined Event Sessions
    */ 
    select 
              tblSES.[name]

            , [sessionID]
                = tblSES.[event_session_id]

            , [sessionSource]
                = null


    from   sys.server_event_sessions tblSES

    union
    
    /*
        Get Running Event Sessions that are shown in  sys.server_event_sessions
    */ 

    select 
              tblXES.[name]

            , cast(null as int)

            , [sessionSource]
                = tblXES.[session_source]

    from   sys.dm_xe_sessions tblXES

    where  tblXES.[name] not in
                (
                    select [name]
                    from   sys.server_event_sessions tblSES
                )


)
select 

          [extendedEvent] 
            = cteEE.[name]

        , [sessionID] 
             =  tblSES.[event_session_id]


        , [eventRetentionMode]
             = tblSES.[event_retention_mode]

        , [trackCausality] 
            = tblSES.[track_causality]

        , [startupState] 
            = tblSES.[startup_state]

        , [sessionCreateTime]
             = tblXES.[create_time]

        , [sessionSource]
             = tblXES.[session_source]


from  cteExtendedEvent cteEE

left outer join sys.server_event_sessions tblSES

        on cteEE.[name] = tblSES.[name] 

left outer join sys.dm_xe_sessions tblXES

        on cteEE.[name]  = tblXES.[name]

Output

Summary

An astute mind will see that there are some events listed in sys.dm_xe_sessions, but not in sys.server_event_sessions.

Again sys.dm_xe_sessions tracks running Event Sessions.  Whereas sys.server_event_sessions tracks sessions definitions.

Also, keep in mind that sys.dm_xe_sessions is a dynamic management view.  And, sys.server_event_sessions is a system catalog view.

It appears that sys.server_event_sessions has chosen not to expose internal Extended events within the sys.server_event_sessions view.

MSFT choice makes some sense as sys.server_event_sessions by definition exposes definitions.

And, MSFT is likely saying these definitions are private and personal to us and we have chosen not to expose them.

 

References

  1. Microsoft
    • Docs / SQL / Relational databases / System catalog views/
      • sys.server_event_sessions ( Transact-SQL )
      • sys.dm_xe_sessions ( Transact-SQL )

SQL Server – RML Utility – Converting Extended Events to SQL Server Profiler Trace Files

Background

Extended Events is the modern way to collect events.  But, in some cases one might want to convert Extended Events to SQL Server Profiler Traces.

RML Utility

Reading through a blog post found out one can convert files generated through Extended Events into Profiler Trace Files.

Download Site

The latest version of RML Utility are available here.

Curiosity

The saying goes curiosity killed the cat.

Same here this last Monday in January.

 

Installation

Download and Installed RML Utilities.

Review Installation

Targeted Folder

The targeted folder is C:\Program Files\Microsoft Corporation\RMLUtils

ReadTrace

File Version

Image

Explanation

The file version is 9.4.51.0

 

Processing

Code


set "_folderReadTrace=C:\Program Files\Microsoft Corporation\RMLUtils\"

set "_xelFolder=Z:\Microsoft\SQLServer\ExtendedEvents\TraceFiles\replay"

set "_xelFileFirst=replay_0_131617352214050000.xel"

set "_xelFileTarget=%_xelFolder%\%_xelFileFirst%"

set "_tracefile=Z:\Microsoft\SQLServer\ProfileTrace\TraceFiles"

set "_sqlServer=DBLAB"

if not exist %_tracefile% mkdir %_tracefile%

"%_folderReadTrace%\readtrace.exe"  -S%_sqlServer% -a -I"%_xelFileTarget%" -MS -o"%_tracefile%"

 

Output

Image

Textual


01/29/18 16:29:39.892 [0X000015D8] I/O Completion manager started
01/29/18 16:29:39.893 [0X00002BE4] Attempting DOD5015 removal of [Z:\Microsoft\S
QLServer\ProfileTrace\TraceFiles\ReadTrace.log]
01/29/18 16:29:39.896 [0X00002BE4] Readtrace a SQL Server trace processing utili
ty.
Version 9.04.0051 built for x64.
Copyright ⌐ 1997-2014 Microsoft. All Rights Reserved
01/29/18 16:29:39.897 [0X00002BE4]             Computer: D-ITS-AYSOSQL02
01/29/18 16:29:39.897 [0X00002BE4]          Base Module: C:\Program Files\Micros
oft Corporation\RMLUtils\ReadTrace.exe
01/29/18 16:29:39.897 [0X00002BE4]           Process Id: 6480
01/29/18 16:29:39.898 [0X00002BE4]  Active proc mask(0): 0x0000000F
01/29/18 16:29:39.898 [0X00002BE4]         Architecture: 9
01/29/18 16:29:39.898 [0X00002BE4]            Page size: 4096
01/29/18 16:29:39.898 [0X00002BE4]                 CPUs: 4
01/29/18 16:29:39.899 [0X00002BE4]     Processor groups: 1
01/29/18 16:29:39.899 [0X00002BE4]         Highest node: 0
01/29/18 16:29:39.899 [0X00002BE4]   Proximity: 00  Node: 00
01/29/18 16:29:39.900 [0X00002BE4] ---------------------------------------
01/29/18 16:29:39.900 [0X00002BE4]                Group: 0
01/29/18 16:29:39.900 [0X00002BE4] ---------------------------------------
01/29/18 16:29:39.901 [0X00002BE4]         Processor(s): 0x00000001 Function uni
ts: Separated
01/29/18 16:29:39.901 [0X00002BE4]         Package mask: 0x00000003
01/29/18 16:29:39.901 [0X00002BE4]         Processor(s): 0x00000002 Function uni
ts: Separated
01/29/18 16:29:39.901 [0X00002BE4]         Processor(s): 0x00000004 Function uni
ts: Separated
01/29/18 16:29:39.902 [0X00002BE4]         Package mask: 0x0000000C
01/29/18 16:29:39.902 [0X00002BE4]         Processor(s): 0x00000008 Function uni
ts: Separated
01/29/18 16:29:39.902 [0X00002BE4]         Processor(s): 0x0000000F assigned to
Numa node: 0
01/29/18 16:29:39.907 [0X00002BE4] Current time bias: 480 minutes 8.00 hours DST
 Standard
01/29/18 16:29:39.907 [0X00002BE4] -SDBLAB
01/29/18 16:29:39.908 [0X00002BE4] -a
01/29/18 16:29:39.908 [0X00002BE4] -IZ:\Microsoft\SQLServer\ExtendedEvents\Trace
Files\replay\replay_0_131617352214050000.xel
01/29/18 16:29:39.908 [0X00002BE4] File mirroring enabled, with single file targ
et
01/29/18 16:29:39.909 [0X00002BE4] -MS
01/29/18 16:29:39.909 [0X00002BE4] -oZ:\Microsoft\SQLServer\ProfileTrace\TraceFi
les
01/29/18 16:29:39.910 [0X00002BE4] Using language id (LCID): 1024 [English_Unite
d States.1252] for character formatting with NLS: 0x0006020E and Defined: 0x0006
020E
01/29/18 16:29:39.911 [0X00002BE4] Attempting to cleanup existing RML files from
 previous execution
01/29/18 16:29:39.991 [0X00002BE4] Detecting list of matching XEL files in the s
ame directory
01/29/18 16:29:40.039 [0X00002BE4]  INFO: Using additional file system cache rea
d ahead for XEL input
01/29/18 16:29:40.046 [0X00002BE4] XEL READER ERROR: The tookLock argument must
be set to false before calling this method.
01/29/18 16:29:40.048 [0X00002BE4] XEL READER ERROR: Attempt to open file Z:\Mic
rosoft\SQLServer\ExtendedEvents\TraceFiles\Replay\replay_0_131617352214050000.XE
L failed, see previous error(s) for more details.
01/29/18 16:29:40.049 [0X00002BE4] ERROR: Attempt to open Z:\Microsoft\SQLServer
\ExtendedEvents\TraceFiles\Replay\replay_0_131617352214050000.XEL failed.
01/29/18 16:29:40.050 [0X00002BE4] ERROR: Unable to construct proper base metada
ta generation for Z:\Microsoft\SQLServer\ExtendedEvents\TraceFiles\Replay\replay
_0_131617352214050000.XEL
01/29/18 16:29:40.053 [0X00002BE4] ERROR: Attempt to determine list of .XEL file
s failed.
01/29/18 16:29:40.057 [0X00002BE4] *** ERROR: Attempt to initialize trace file r
eader failed with operating system error 0x80070006 (The handle is invalid)
01/29/18 16:29:40.061 [0X00002BE4] Reads completed - Global Error Status 0xfffff
ffe
01/29/18 16:29:40.062 [0X00002BE4] Signaling worker threads to complete final ac
tions.
01/29/18 16:29:40.067 [0X00002BE4] Waiting for the worker threads to complete fi
nal actions.
01/29/18 16:29:40.069 [0X00002BE4] Performing general cleanup actions.
01/29/18 16:29:40.072 [0X00002BE4] Performing final mirroring actions.
01/29/18 16:29:40.075 [0X00002BE4] Total Events Processed: 0
01/29/18 16:29:40.076 [0X00002BE4]  Total Events Filtered: 0
01/29/18 16:29:40.077 [0X00002BE4] =============================================
=
01/29/18 16:29:40.079 [0X00002BE4] WARNING: A significant portion of the events
where filtered.
01/29/18 16:29:40.086 [0X00002BE4]          You may want to check the filtering
criteria.
01/29/18 16:29:40.088 [0X00002BE4] =============================================
=
01/29/18 16:29:40.090 [0X00002BE4] *********************************************
**********************************
* ReadTrace encountered one or more ERRORS. An error condition typically      *
* stops processing early and the ReadTrace output may be unusable.            *
* Review the log file for details.                                            *
*******************************************************************************
01/29/18 16:29:40.094 [0X00002BE4] ***** ReadTrace exit code: -2
01/29/18 16:29:40.097 [0X00002BE4]  INFO: Max set-aside XEL event hash table ent
ry count: 0
01/29/18 16:29:40.131 [0X00002BE4]
01/29/18 16:29:40.134 [0X00002BE4] INFO: Cleaning up connection info hash table
>

Explanation

Key details:

  1. [0X00002138] Detecting list of matching XEL files in the same directory
  2. [0X00002138] INFO: Using additional file system cache read ahead for XEL input
  3. [0X00002138] XEL READER ERROR: The tookLock argument must be set to false before calling this method.
  4. [0X00002138] XEL READER ERROR: Attempt to open file Z:\Microsoft\SQLServer\ExtendedEvents\TraceFies\Replay\replay_0_131617352214050000.XEL failed, see previous error(s) for more details.
  5. ERROR: Attempt to open Z:\Microsoft\SQLServer\ExtendedEvents\TraceFiles\Replay\replay_0_131617352214050000.XEL failed.
    [0X00002138] ERROR: Unable to construct proper base metadata generation for Z:\Microsoft\SQLServe
    \ExtendedEvents\TraceFiles\Replay\replay_0_131617352214050000.XEL
  6. [0X00002138] ERROR: Attempt to determine list of .XEL files failed.
  7. [0X00002138] *** ERROR: Attempt to initialize trace file reader failed with operating system error 0x80070006 (The handle is invalid)
  8. [0X00002138] Reads completed – Global Error Status 0xfffffffe
  9. [0X00002138] ***** ReadTrace exit code: -2

 

TroubleShooting

ReadTrace

Code


"C:\Program Files\Microsoft Corporation\RMLUtils\readtrace.exe" /?

Output

Explanation

  1. -I File name of the first .TRC or .XEL file to process [REQUIRED]. Note: XEL processing is ‘BETA’ and limited.
    • Clearly states that XEL ( Extended Events ) processing is in BETA mode and limited

SysInternals

Process Monitor

Captured Events
Image

Explanation

Confirmed that the extended event file was located and opened successfully.

 

Summary

It appears that readtrace at Version 9.4.51.0, last modified on Dec 10,2014 is starting to show its age.

BTW, in SQL Server parlance 9.x.y.z is MS SQL Server v2005.

SQL Server – Reporting Services – Error – “Invalid or Expired Session”

Background

Reviewing collected “Extended Events” and noticed a recurring error.

Error

Image

Tabulated

  1. Message :- Invalid or Expired Session: [session id]
  2. Event Name :- error_reported
  3. Error Number :- 50000
  4. Severity :- 16
  5. session_server_principal_name :- Web Site’s Application Pool Identity
  6. server_principal_name :-
  7. is_system :- false
  8. database name :- ReportServer
  9. client_app_name :- Report Server

TroubleShooting

Database

ReportServerTempDB

Table

ReportServerTempDB.dbo.SessionData
Reviewing date of entries
Reviewing date of entries – SQL

SELECT

        [ExpirationMin] 
		  = min 
				(
					[Expiration]
				)
      
      , [ExpirationMax] 
		= max 
			(
				[Expiration]
			)
      
      , [durationBetweenMinAndMax]
			= datediff
				(
					  minute
					, min ([Expiration])
					, max ([Expiration]) 
				)

      , [durationBetweenMinAndCurrent] 
		  = datediff
				(
					  minute
					, min ([Expiration])
					, getdate() 
				)

      , [durationBetweenMaxAndCurrent] 
		= datediff
			(
				  minute
				, max ([Expiration])
				, getdate() 
			)

FROM [dbo].[SessionData] tblSD


Explanation
  1. The entries returned will have duration reflecting the settings set for session recycling.

Remediate

Configuration

Configuration Files

rsreportserver.config

Item :- Configuration \ CleanupCycleMinutes
Default

The default setting for CleanupCycleMinutes is 10.

This translates to entries in the ReportServerTempDB been pruned every 10 minutes.

Revised

Please increase timeout.

Doing so will mean that records are recycled less frequently.

Image – Default

 

Image – Revised

Explanation

Change CleanupCycleMinutes from 10 minutes to 180 minutes, 3 hours.

SQL Server – Extended Event – Metadata – Get Current Target File

Background

For some optimization work that we are doing, I will like to know the name of the Extended Events’ Engine currently targeted file.

 

Code

Guide

As always we know that Microsoft exposes a lot of metadata via Dynamic Management Views.

The specific ones that we will use are:

  1. sys.dm_xe_sessions
    • Currently executing Extended Event Sessions
  2. sys.dm_xe_session_targets
    • Targets for currently executing sessions

SQL



SELECT
		  [extendedEvent]
			= tblXES.[name]

		, [executionCount]
			= tblXEST.execution_count

		, [executionDuration]
			= tblXEST.execution_duration_ms

		, [targetData]
			= tblXEST.target_data

		, [fileCurrent]
			= cast(tblXEST.target_data as xml).value('(//File/@name)[1]','nvarchar(255)')

FROM sys.dm_xe_sessions AS tblXES

INNER JOIN sys.dm_xe_session_targets AS tblXEST

    ON tblXES.[address] = tblXEST.[event_session_address]

where 
	(

		( tblXEST.target_name = 'event_file' )

	)


Output

Sample Output

 

SQL Server – Extended Event – Data Storage – Change Folder / File Name

Background

Due to Storage Constraints having to change the folder that Extended Events are captured in.

 

GUI

I know it is not possible to do so via GUI.

As shown below, just as your author, the properties are dimmed.

 


Script

So let us script it

Outline

  1. Stop the Extended Event Session
  2. Drop the Extended Event Session target
    • In our case, we are using the adopted default target name of package0.event_file
  3. Add a new Target
    • Keeping everything from the old target
    • But, changing the targeted folder
  4. Restart the Extended Event Session

Code




	ALTER EVENT SESSION [performance]   
	ON SERVER  
	STATE = stop;  

	ALTER EVENT SESSION [performance]  
		ON SERVER 
		DROP TARGET package0.event_file;

	ALTER EVENT SESSION [performance]  
		ON SERVER 
		ADD TARGET package0.event_file
		(
			SET 
				  filename=N'C:\Microsoft\SQLServer\ExtendedEvents\TraceFiles\performance.xel'
				, max_file_size=(100)
				, max_rollover_files=(100)
		)

	ALTER EVENT SESSION [performance]   
	ON SERVER  
	STATE = start;  

GO 

Xpath Query – Compare Positional Versus Condition

Background

As always too much time on my hands.

I wanted to compare the query generated by XMLToolBox against one I tweaked gently.

Here is the post that introduces XMLToolBox.

 

Lab

Queries

XPath – Position


set nocount on;
go

declare @xml XML

set @xml=
'
<event name="sql_batch_completed" package="sqlserver" timestamp="2017-05-01T16:44:02.507Z">
  <data name="cpu_time">
    <value>250000</value>
  </data>
  <data name="duration">
    <value>226002957</value>
  </data>
  <data name="physical_reads">
    <value>16349</value>
  </data>
  <data name="logical_reads">
    <value>16197</value>
  </data>
  <data name="writes">
    <value>0</value>
  </data>
  <data name="row_count">
    <value>0</value>
  </data>
  <data name="result">
    <value>2</value>
    <text>Abort</text>
  </data>
  <data name="batch_text">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = ''A'' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </data>
  <action name="sql_text" package="sqlserver">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = ''A'' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </action>
  <action name="session_server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="session_id" package="sqlserver">
    <value>72</value>
  </action>
  <action name="server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="server_instance_name" package="sqlserver">
    <value>HRDBDEV</value>
  </action>
  <action name="is_system" package="sqlserver">
    <value>false</value>
  </action>
  <action name="database_name" package="sqlserver">
    <value>rbpivr1</value>
  </action>
  <action name="database_id" package="sqlserver">
    <value>10</value>
  </action>
  <action name="client_hostname" package="sqlserver">
    <value>WEBSERVER01</value>
  </action>
  <action name="client_app_name" package="sqlserver">
    <value>Microsoft® Windows® Operating System</value>
  </action>
  <action name="collect_system_time" package="package0">
    <value>2017-05-01T16:44:02.508Z</value>
  </action>
  <action name="attach_activity_id_xfer" package="package0">
    <value>E2EDC810-A11C-4C62-988D-2420F86D8EAD-0</value>
  </action>
  <action name="attach_activity_id" package="package0">
    <value>008E9A10-DF7E-4C85-9C85-95629D3B647F-1</value>
  </action>
</event>

'

select

		  [resultValueUsingPosition] 
			= cast
				(
					@xml.query('/event[@name="sql_batch_completed"]/data[7]/value/text()[1]' )
						as varchar(10)
				)

		, [resultTextUsingPosition] 
			= cast
				(
					@xml.query('/event[@name="sql_batch_completed"]/data[7]/text/text()[1]' )
						as varchar(30)
				)




 

XPath – Condition


set nocount on;
go

declare @xml XML

set @xml=
'
<event name="sql_batch_completed" package="sqlserver" timestamp="2017-05-01T16:44:02.507Z">
  <data name="cpu_time">
    <value>250000</value>
  </data>
  <data name="duration">
    <value>226002957</value>
  </data>
  <data name="physical_reads">
    <value>16349</value>
  </data>
  <data name="logical_reads">
    <value>16197</value>
  </data>
  <data name="writes">
    <value>0</value>
  </data>
  <data name="row_count">
    <value>0</value>
  </data>
  <data name="result">
    <value>2</value>
    <text>Abort</text>
  </data>
  <data name="batch_text">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = ''A'' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </data>
  <action name="sql_text" package="sqlserver">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = ''A'' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </action>
  <action name="session_server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="session_id" package="sqlserver">
    <value>72</value>
  </action>
  <action name="server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="server_instance_name" package="sqlserver">
    <value>HRDBDEV</value>
  </action>
  <action name="is_system" package="sqlserver">
    <value>false</value>
  </action>
  <action name="database_name" package="sqlserver">
    <value>rbpivr1</value>
  </action>
  <action name="database_id" package="sqlserver">
    <value>10</value>
  </action>
  <action name="client_hostname" package="sqlserver">
    <value>WEBSERVER01</value>
  </action>
  <action name="client_app_name" package="sqlserver">
    <value>Microsoft® Windows® Operating System</value>
  </action>
  <action name="collect_system_time" package="package0">
    <value>2017-05-01T16:44:02.508Z</value>
  </action>
  <action name="attach_activity_id_xfer" package="package0">
    <value>E2EDC810-A11C-4C62-988D-2420F86D8EAD-0</value>
  </action>
  <action name="attach_activity_id" package="package0">
    <value>008E9A10-DF7E-4C85-9C85-95629D3B647F-1</value>
  </action>
</event>

'



select

		  [resultValueUsingCondition] 
			= cast
				(
					@xml.query('/event[@name="sql_batch_completed"]/data[@name="result"]/value/text()[1]' )
						as varchar(10)
				)

		, [resultTextUsingCondition] 
			= cast
				(
					@xml.query('/event[@name="sql_batch_completed"]/data[@name="result"]/text/text()[1]' )
						as varchar(30)
				)


 

Compare Queries

Let us have the queries together and compare them.

 

Queried Together

Code



set nocount on;
go

declare @xml XML

set @xml=
'
<event name="sql_batch_completed" package="sqlserver" timestamp="2017-05-01T16:44:02.507Z">
  <data name="cpu_time">
    <value>250000</value>
  </data>
  <data name="duration">
    <value>226002957</value>
  </data>
  <data name="physical_reads">
    <value>16349</value>
  </data>
  <data name="logical_reads">
    <value>16197</value>
  </data>
  <data name="writes">
    <value>0</value>
  </data>
  <data name="row_count">
    <value>0</value>
  </data>
  <data name="result">
    <value>2</value>
    <text>Abort</text>
  </data>
  <data name="batch_text">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = ''A'' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </data>
  <action name="sql_text" package="sqlserver">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = ''A'' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </action>
  <action name="session_server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="session_id" package="sqlserver">
    <value>72</value>
  </action>
  <action name="server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="server_instance_name" package="sqlserver">
    <value>HRDBDEV</value>
  </action>
  <action name="is_system" package="sqlserver">
    <value>false</value>
  </action>
  <action name="database_name" package="sqlserver">
    <value>rbpivr1</value>
  </action>
  <action name="database_id" package="sqlserver">
    <value>10</value>
  </action>
  <action name="client_hostname" package="sqlserver">
    <value>WEBSERVER01</value>
  </action>
  <action name="client_app_name" package="sqlserver">
    <value>Microsoft® Windows® Operating System</value>
  </action>
  <action name="collect_system_time" package="package0">
    <value>2017-05-01T16:44:02.508Z</value>
  </action>
  <action name="attach_activity_id_xfer" package="package0">
    <value>E2EDC810-A11C-4C62-988D-2420F86D8EAD-0</value>
  </action>
  <action name="attach_activity_id" package="package0">
    <value>008E9A10-DF7E-4C85-9C85-95629D3B647F-1</value>
  </action>
</event>

'

select

		  [resultValueUsingPosition] 
			= cast
				(
					@xml.query('/event[@name="sql_batch_completed"]/data[7]/value/text()[1]' )
						as varchar(10)
				)

		, [resultTextUsingPosition] 
			= cast
				(
					@xml.query('/event[@name="sql_batch_completed"]/data[7]/text/text()[1]' )
						as varchar(30)
				)




select

		  [resultValueUsingCondition] 
			= cast
				(
					@xml.query('/event[@name="sql_batch_completed"]/data[@name="result"]/value/text()[1]' )
						as varchar(10)
				)

		, [resultTextUsingCondition] 
			= cast
				(
					@xml.query('/event[@name="sql_batch_completed"]/data[@name="result"]/text/text()[1]' )
						as varchar(30)
				)

Query Plan Comparison – Query Inline

 

Query Plan Comparison – Side by Side

Using SQL Server Management Studio (SSMS) v2016, we can run both queries individually and compare the saved Query Plan

Image

Tabulated
Item Position  Condition
 Cached Plan Size  152 KB  192 KB
 CompileCPU  25  118
 CompileMemory  1736  2664
 CompileTime  25  118
 EstimatedSubtreeCost  806.458  410.401
 MemoryGrant  16128  17920
 OptimizationLevel  FULL  FULL
 RetrievedFromCache  true  true

 

 

Execution Time – Comparison

Image

 

Explanation
  1. Consistently the query with hardcoded position came in around 40 ms while the conditional one came in around 160ms

Summary

It appears from this one test, generated queries that rely on an element’s fixed position are less taxing on the system.

Interestingly, positional queries are costed higher.

On the other hand, queries generated via Conditions are likely less performant.

 

 

XPath Query Tools – XmlToolBox

Background

Lately I have been working more and more with Extended Events and prior to that Cached Plans exploration.

And, has both are stored as XML, it makes sense to see what tools are available for accurately querying XML data.

 

Body

Here is the collected event.


<event name="sql_batch_completed" package="sqlserver" timestamp="2017-05-01T16:44:02.507Z">
  <data name="cpu_time">
    <value>250000</value>
  </data>
  <data name="duration">
    <value>226002957</value>
  </data>
  <data name="physical_reads">
    <value>16349</value>
  </data>
  <data name="logical_reads">
    <value>16197</value>
  </data>
  <data name="writes">
    <value>0</value>
  </data>
  <data name="row_count">
    <value>0</value>
  </data>
  <data name="result">
    <value>2</value>
    <text>Abort</text>
  </data>
  <data name="batch_text">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = 'A' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </data>
  <action name="sql_text" package="sqlserver">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = 'A' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </action>
  <action name="session_server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="session_id" package="sqlserver">
    <value>72</value>
  </action>
  <action name="server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="server_instance_name" package="sqlserver">
    <value>HRDBDEV</value>
  </action>
  <action name="is_system" package="sqlserver">
    <value>false</value>
  </action>
  <action name="database_name" package="sqlserver">
    <value>rbpivr1</value>
  </action>
  <action name="database_id" package="sqlserver">
    <value>10</value>
  </action>
  <action name="client_hostname" package="sqlserver">
    <value>WEBSERVER01</value>
  </action>
  <action name="client_app_name" package="sqlserver">
    <value>Microsoft® Windows® Operating System</value>
  </action>
  <action name="collect_system_time" package="package0">
    <value>2017-05-01T16:44:02.508Z</value>
  </action>
  <action name="attach_activity_id_xfer" package="package0">
    <value>E2EDC810-A11C-4C62-988D-2420F86D8EAD-0</value>
  </action>
  <action name="attach_activity_id" package="package0">
    <value>008E9A10-DF7E-4C85-9C85-95629D3B647F-1</value>
  </action>
</event>

 

Googled

Googled on XML Query XPath and found many tools.

In this post we will discuss XMLToolBox.

 

XMLToolBox

I liked XMLToolBox has unlike many other tools one does not have to start with a query, just paste the the XML body unto the canvas, and point at the XML element or attribute you want to generate the query on.

URL

Here is the Link for XMLToolbox.

Usage

Here is what happens when we :

  1. Copy and paste the XML noted above unto the XML field textarea
  2. Find the result element and click on the text element

Analysis

Here is the generated Xpath:

/event[@name=”sql_batch_completed”]/data[7]/text/text()

 

Interpretation

  1. Parse using event ( /event )
  2. Look for the attribute name matching sql_batch_completed
  3. Transverse to the 7th data element
  4. Find the text element within the the 7th element and return the text portion of that element

 

Summary

XMLToolBox is easy to use and capable.

Unfortunately, it lacks flexibility because it is based on the position of elements.

If the schema changes a bit, the XPath might return the wrong element as it would refer to the original position.