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.

 

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.

 

Extended Events – Data Analysis – XML Indexes

Background

Once one starts looking at Extended Events and realize that data is stored in XML, one will likely think will it be beneficial to index the collected XML column.

 

 

Data Model

Image

 

Code

Steps

The basic steps is that we use sys.fn_xe_file_target_read_file to read the XML file and place it in the eventStaging table.

We then use XPath to parse data from the eventStaging table into the event table.

 

Load Data From Extended Event File Into Staging Table

Code



	truncate table [extendedEvent].[eventStaging];

	insert into [extendedEvent].[eventStaging] WITH (TABLOCK)
	(
		  [objectName]	
		, [eventData]	
	)
	SELECT
 
		  [objectName] = tblFNXE.[object_name]
		, [eventData] = cast(tblFNXE.event_data as xml)
 
	FROM sys.fn_xe_file_target_read_file
		(

				case
				when (@folderXEL like '%xel%') then @folderXEL
				else @folderXEL + '' + '*.xel'
				end

			, case
					when (@folderXEM is null) then null
					when (@folderXEM like '%xe%') then @folderXEM
					else isNull(@folderXEM, @folderXEL) + '' + '*.xem'
				end	

			, null

			, null

		) tblFNXE

Load Data From Staging Table into DB Column Table

Code




select top ( 
				  isNull(@maxNumberofRecords, 1E10)
			   )
 
			  [objectName] = isNull(cte.[objectName], '')
 
			, [eventData] = cast(cte.eventData as xml)
			--, [XMLFragment] = [event].[node].query('.')
 
 
			--collect system time
			, [systemTSAsUTC]
				= 	(
						[event].[node].value
							(
								'(action[@name="collect_system_time"])[1]'
								, 'datetime'
							)
					)

			-- collect system time to locale time
			, [systemTSAsLocale] 
				 = dateadd
					(
						  minute
						, @timeDifferenceBetweenUTCAndSystemTimeInMinutes
						, (
							[event].[node].value
							(
									'(action[@name="collect_system_time"])[1]'
								, 'datetime'
							)
						)
					)

			--is system
			, [isSystem] 
				= case 
						[event].[node].value
						(	
							  '(action[@name="is_system"])[1]'
							, 'char(10)'
						)
							when 'true' then 'Y'
							else 'N'
				end
				
			--session ID
			, [sessionID] 
				= [event].[node].value
				  (
					  '(action[@name="session_id"])[1]'
					, 'int'
				  )

			-- database name
			, [dbname] 
				= [event].[node].value
				  (
					  '(action[@name="database_name"])[1]'
					, 'sysname'
				  )
 
			-- client Host Name
			, [clientHostName] 
				= [event].[node].value
				  (
					  '(action[@name="client_hostname"])[1]'
					, 'sysname'
				  )
 
			-- username
			, [username] 
				= [event].[node].value
				  (
					  '(action[@name="session_server_principal_name"])[1]'
					, 'sysname'
				  )

			-- client App Name
			, [clientAppName] 
				= [event].[node].value
				  (
					  '(action[@name="client_app_name"])[1]'
					, 'sysname'
				  )
 
			-- SQL Text
			, [sqlText] 
				= [event].[node].value
				  (
					  '(action[@name="sql_text"])[1]'
					, 'nvarchar(max)'
				  )

			-- Result
			, [result]
				= [event].[node].value
				  (
					  '(data[@name="result"]/text/text())[1]'
					, 'sysname'
				  )

			-- Error Number
			, [errorNumber]
				= [event].[node].value
				  (
					  '(data[@name="error_number"])[1]'
					, 'int'
				  )

			-- severity
			, [severity]
				= [event].[node].value
				  (
					  '(data[@name="severity"])[1]'
					, 'int'
				  )
 
			-- Message
			, [message] 
				= [event].[node].value
				  (
					  '(data[@name="message"])[1]'
					, 'nvarchar(max)'
				  )


 			, [attachActivityID]
				= [event].[node].value
				  (
					  '(event/action[@name="attach_activity_id"]/value)[1]'
					, 'varchar(50)'
				  )


			--duration
			, [duration] 
				= [event].[node].value
				  (
					  '(data[@name="duration"]/value/text())[1]'
					, 'bigint'
				  )

			--cputime
			, [cputime] 
				= [event].[node].value
				  (
					  '(data[@name="cpu_time"]/value/text())[1]'
					, 'bigint'
				  )
	from   [extendedEvent].[eventStaging] cte
 
	cross apply cte.eventData.nodes(N'//event') event(node)

Benchmark

XML Indexes

Statistics I/O & Timing

Image

Trial #1

Trial #2

Trial #3

Tabulate

 

Trial# Step IO Stats Time Taken
#1  
Import data from xml file into staging table  182 secs.
 Table ‘xml_index_nodes_373576369_256000’. Scan count 0, logical reads 24969927, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 Table ‘Worktable’. Scan count 3, logical reads 268066, physical reads 0, read-ahead reads 0, lob logical reads 143904, lob physical reads 0, lob read-ahead reads 5.
 Table ‘eventStaging’. Scan count 0, logical reads 167819, physical reads 0, read-ahead reads 0, lob logical reads 4, lob physical reads 0, lob read-ahead reads 0.
Copy Data from staging table to actual table  207 secs.
 Table ‘eventFile’. Scan count 0, logical reads 72752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 Table ‘event’. Scan count 0, logical reads 420862, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.
 Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 3400, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 Table ‘xml_index_nodes_373576369_256000’. Scan count 3707011, logical reads 15613316, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.
 Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 Table ‘eventStaging’. Scan count 0, logical reads 109128, physical reads 0, read-ahead reads 0, lob logical reads 3, lob physical reads 0, lob read-ahead reads 2.
#2  
Import data from xml file into staging table  169 secs.
Table ‘xml_index_nodes_373576369_256000’. Scan count 0, logical reads 24969927, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 3, logical reads 268066, physical reads 0, read-ahead reads 0, lob logical reads 143904, lob physical reads 0, lob read-ahead reads 5.
Table ‘eventStaging’. Scan count 0, logical reads 167819, physical reads 0, read-ahead reads 0, lob logical reads 4, lob physical reads 0, lob read-ahead reads 0.
Copy Data from staging table to actual table  196 secs.
 Table ‘eventFile’. Scan count 0, logical reads 72752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
Table ‘event’. Scan count 0, logical reads 420862, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 3400, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘xml_index_nodes_373576369_256000’. Scan count 3707011, logical reads 15613315, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 Table ‘eventStaging’. Scan count 0, logical reads 109128, physical reads 0, read-ahead reads 0, lob logical reads 3, lob physical reads 0, lob read-ahead reads 2.
#3  
Import data from xml file into staging table  169 secs.
Table ‘xml_index_nodes_373576369_256000’. Scan count 0, logical reads 24969927, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 3, logical reads 268066, physical reads 0, read-ahead reads 0, lob logical reads 143904, lob physical reads 0, lob read-ahead reads 5.
Table ‘eventStaging’. Scan count 0, logical reads 167819, physical reads 0, read-ahead reads 0, lob logical reads 4, lob physical reads 0, lob read-ahead reads 0.
Copy Data from staging table to actual table  199 secs.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘eventFile’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘event’. Scan count 0, logical reads 428556, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.
Table ‘xml_index_nodes_373576369_256000’. Scan count 3707011, logical reads 15613333, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 Table ‘eventStaging’. Scan count 1, logical reads 12130, physical reads 0, read-ahead reads 0, lob logical reads 3, lob physical reads 0, lob read-ahead reads 2.

 

 

Query Plan

Insert Data from Extended Event Into Staging DB

Image

Explanation
  1. Maintaining XML Indexes is most expensive
    • Clustered Index Insert (xml_index_nodes_*)
  2. Table Spools
    • Reading File from XML File into staging table
    • Prepare data in preparation for XML Index maintenance

 

No XML Indexes

Image

Trial #1

Trial #2

Trial #3

Tabulate

 

Trial# Step IO Stats Time Taken
#1  
Import data from xml file into staging table  41 secs.
Table ‘eventStaging’. Scan count 0, logical reads 167819, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.
 Table ‘Worktable’. Scan count 1, logical reads 74512, physical reads 0, read-ahead reads 0, lob logical reads 143904, lob physical reads 0, lob read-ahead reads 5.
Copy Data from staging table to actual table  178 secs.
 Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 Table ‘eventFile’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘event’. Scan count 0, logical reads 428556, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.
Table ‘eventStaging’. Scan count 1, logical reads 12130, physical reads 0, read-ahead reads 0, lob logical reads 77, lob physical reads 0, lob read-ahead reads 2.
#2  
Import data from xml file into staging table  19 secs.
Table ‘eventStaging’. Scan count 0, logical reads 167819, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 1, logical reads 74512, physical reads 0, read-ahead reads 0, lob logical reads 143904, lob physical reads 0, lob read-ahead reads 5.
Copy Data from staging table to actual table  137 secs.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘eventFile’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘event’. Scan count 0, logical reads 428556, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.
Table ‘eventStaging’. Scan count 1, logical reads 12130, physical reads 0, read-ahead reads 0, lob logical reads 77, lob physical reads 0, lob read-ahead reads 2.
#3  
Import data from xml file into staging table  35 secs.
Table ‘eventStaging’. Scan count 0, logical reads 167819, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 1, logical reads 74511, physical reads 0, read-ahead reads 0, lob logical reads 143904, lob physical reads 0, lob read-ahead reads 5.
Copy Data from staging table to actual table  115 secs.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘eventFile’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘event’. Scan count 0, logical reads 428556, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.
Table ‘eventStaging’. Scan count 1, logical reads 12130, physical reads 0, read-ahead reads 0, lob logical reads 77, lob physical reads 0, lob read-ahead reads 2.

 

 

 

Summary

Having XML Indexes on the staging table resulted in much more IO.

We ran two trials and timed 182 and 169 seconds.

Without indexes, our timing was 41 and 19 seconds.

Querying the XML Column was only slightly more performant with Indexes.

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

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.

Active Directory – Change User’s Password – Resolution

 

Preface

In a previous post, I spoke of a SMK ( Shaking My Head ) moment I was having.

I couldn’t change a password assigned to a newly created Service Account.

The post is here.

 

 

Problem Identification

Thankfully, I have friends in high places or at least friends who are not so dim.

As Ron was leaving for the day, I said to him you gonna hate me for bothering you.

But, what is with my inability to change my password.

He said it is a Group Policy thing.

I said I checked the Group Policy (GP) and I did not see that.

 

Group Policy Report

Code

Using gpresult we can generate Group Policy Reports.

Generate HTML Output

Script


set "_user=LAB\sbc"
If not exist "d:\temp" md "d:\temp"
gpresult /USER %_user% /F /H d:\temp\grResultUser.html

 

Output

accountandpasswordpolicies

Generate Textual Output

Script


set "_user=LAB\svcSQL"

gpresult /V /USER %_user% | more

Output

rsop-minimumpasswordage

Explanation

Underneath \Policies\Windows Settings \ Account Policies / Password Policy

There  goes a Winning GPO stating “Minimum password age” is 5 days.

 

Conclusion

I still did not get it, and so Ron had to explain it.

A password has to be at least 5 days old, prior to anyone having the ability to change it.

The password was only created yesterday and so I have to a wait a few more days.

 

MSFT’s Recommendation

Cristian Dobre

Link

cristiandobre

 

Confirm Our Last Password Date

Let us confirm our last password date

Code – Credit

As always, I can not write this code.

Stealing this time from Homework

The specific post is titled “How to get the last password change for a user in Active Directory” and it is credited to Alessandro Tani.

It is available here.

Code


Import-Module ActiveDirectory

$ADUser="svcDBHRDB"

$formatDate="yyyy-MM-dd HH:mm"
$now=Get-Date -format $formatDate

"Current Date & TIme is {0}" -f $now

Get-ADuser $ADUser -properties PasswordLastSet | Format-List

 

Output

getaduseroutput-20161201-0838am

Errors

Error – Import-Module : The specified module ‘ActiveDirectory’ was not loaded because no valid module file was found in any module directory.

Please read this QA:

Import-Module : The specified module ‘activedirectory’ was not loaded because no valid module file was found in any module directory
Link

 

References

  1. Security Policy Settings Reference > Account Policies > Password Policy > Minimum password age
    Link
  2. Alessandro Tani
    • How to get the last password change for a user in Active Directory
      Link
  3. Nirmal Sharma
    • When was the Last Password Changed for a User Account in Active Directory
      Link

 

SQL Server Best Practice Analyzer (BPA) – v2012 – Against SQL Server v2014 and Beyond

Background

There are a couple of things one needs to do to get “SQL Server Best Practice Analyzer” (BPA) v2012 to work against MS SQL Server v2014 and above.

In an earlier post we discussed how we determined that we need to have “SQL Server Shared Management Objects” (SMO) installed.

 

Guidelines

  1. Copy SQL2012BPA
    • Copy SQL2012BPA from C:\Windows\System32\BestPractices\v1.0\Models
    • To C:\ProgramData\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models\
    • Special Thanks to Rob “Barkz” Barker
      • If you do not see “C:\ProgramData\Microsoft\Microsoft Baseline Configuration Analyzer 2“, please make sure that you have installed “Microsoft Baseline Configuration Analyzer 2
      • Also, make sure that System Folders are not hidden
  2. Download the following “MS SQL Server Microsoft® SQL Server® 2012 Feature Pack” components
    • Microsoft® SQL Server® 2012 Shared Management Objects
    • Microsoft® SQL Server® 2012 Analysis Management Objects
  3. Modify the Powershell in this way
    • Microsoft® SQL Server® 2012 Shared Management Objects [ SQL Server SMO ]
      • $SMOAvailable = [System.Reflection.Assembly]::Load(“Microsoft.SqlServer.Smo, Culture=Neutral,Version=11.0.0.0, PublicKeyToken=89845dcd8080cc91″)
        • Original
          • if($SqlServerVersion -ne 11) # Not Equal
        • Revised
          • if($SqlServerVersion -lt 11) # Less Than
      • Microsoft® SQL Server® 2012 Analysis Management Objects [ SQL Analysis AMO ]
        • $AMOAvailable = [System.Reflection.Assembly]::Load(“Microsoft.AnalysisServices, Culture=Neutral,Version=11.0.0.0, PublicKeyToken=89845dcd8080cc91”)
          • Original
            • if($SqlServerVersion -ne 11)
          • Revised
            • if($SqlServerVersion -lt 11)

 

 

Download

Versioning

Which Versions of SQL Server Feature pack do we need?

Took a good look at the code and this is how it reads…

SMO Assembly Reference

Code


$SMOAvailable = [System.Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Culture=Neutral,Version=11.0.0.0, PublicKeyToken=89845dcd8080cc91")

AMO Assembly Reference – AnalyisServices.ps1

Code


# AMO Assembly Reference
$AMOAvailable = [System.Reflection.Assembly]::Load("Microsoft.AnalysisServices, Culture=Neutral,Version=11.0.0.0, PublicKeyToken=89845dcd8080cc91")


Explanation

The code is specifically looking for Version=11.0.0.0.

SQL Server version 11 is MS SQL Server v2012.

Links

Here are the components of “Microsoft® SQL Server® 2012 Feature Pack” that we need.

Product Version x64 x32
 Microsoft® SQL Server® 2012 Shared Management Objects ( SharedManagementObjects.msi )
Link Link
Microsoft® SQL Server® 2012 Analysis Management Objects ( SQL_AS_AMO.msi )
Link Link

 

 

Please download and install.

 

Scripts

Modified the PowerShell scripts in the following way

Original


if($SqlServerVersion <strong>-ne</strong> 11)
{
	Get-LogText $cError "Engine Rules Execution Interrupted - The instance being scanned exist for SQL Server version which is not supported." >> $EngineLogFile
	AddElementToDocument $XmlDoc $tns $SqlServerVersion "SufficiencyCheck" "SQLServerVersion"
	$XmlDoc
	exit
}

Revised


if($SqlServerVersion <strong>-le</strong> 11)
{
	Get-LogText $cError "Engine Rules Execution Interrupted - The instance being scanned exist for SQL Server version which is not supported." >> $EngineLogFile
	AddElementToDocument $XmlDoc $tns $SqlServerVersion "SufficiencyCheck" "SQLServerVersion"
	$XmlDoc
	exit
}



 

Github

Here is the Github repository for the Scripts.

 

Commendation

Rob “Barkz” Barker

Special commendation to Mr. Rob Barker’s ( Barkz ) work:

GETTING SQL SERVER 2012 BPA WORKING ON WINDOWS SERVER 2012 R2
Link