Archive for category Extended events

Query hash and plan hash conversions

The query hash and query plan hash actions are two very useful actions present in SQL 2012 extended events. The data types for both these actions is uint64 (unsigned 64 bit integer). So if you’re monitoring an extended event watch window these actions will be presented as large integer values. You may be used to seeing query hash and query plan hash values in the sys.dm_exec_query_stats DMV. Here they are presented as binary(8) values. Unfortunately there is no native unsigned 64 bit integer data type in SQL Server so converting the binary to the integer value or the integer to the binary value is not trivially easy. Here’s a script showing possible conversions:

-- An example session capturing to an event file

CREATE EVENT SESSION evtQueryPerformance ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
	ACTION(sqlserver.query_hash)
	WHERE sqlserver.query_hash <> 0
)
ADD TARGET package0.event_file(SET filename=N'c:\extevt\evtQueryPerformance.xel',increment=(10),max_file_size=(50),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

ALTER EVENT SESSION evtQueryPerformance
ON SERVER
STATE = START

SELECT CAST(event_data AS XML) AS event_data
INTO #Events
FROM sys.fn_xe_file_target_read_file('c:\extevt\evtQueryPerformance*.xel', null, null, null) AS F;

-- Cast the query hash to decimal(20,0) to accommodate the largest possible uint64
-- ie 2^64-1 = 18446744073709551615
SELECT
	DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
	event_data.value('(/event/@timestamp)[1]', 'datetime2')) AS [timestamp],
	event_data.value('(/event/action[@name="query_hash"    ]/value)[1]', 'DECIMAL(20,0)' ) AS query_hash,
	event_data.value('(/event/data  [@name="duration"      ]/value)[1]', 'BIGINT'        ) AS duration,
	event_data.value('(/event/data  [@name="cpu_time"      ]/value)[1]', 'BIGINT'        ) AS cpu_time,
	event_data.value('(/event/data  [@name="physical_reads"]/value)[1]', 'BIGINT'        ) AS physical_reads,
	event_data.value('(/event/data  [@name="logical_reads" ]/value)[1]', 'BIGINT'        ) AS logical_reads,
	event_data.value('(/event/data  [@name="writes"        ]/value)[1]', 'BIGINT'        ) AS writes,
	event_data.value('(/event/data  [@name="row_count"     ]/value)[1]', 'BIGINT'        ) AS row_count,
	event_data.value('(/event/data  [@name="statement"     ]/value)[1]', 'NVARCHAR(4000)') AS statement
INTO #Queries
FROM #Events

-- Show the conversions

-- Bitmask for the topmost bit in a binary(8)

DECLARE @m BIGINT
SET @m = 0x8000000000000000

;WITH cte_extended_event AS
(
SELECT
	-- check topmost bit of extended event query hash
	-- @m = -9223372036854775808 so multiple by -1 to get the unsigned value
	CASE WHEN query_hash < CONVERT(DECIMAL(20,0),@m)*-1
	-- if topmost bit is not set convert to bigint and then convert to binary(8)
	THEN CONVERT(BINARY(8),CONVERT(BIGINT,query_hash))
	-- if topmost bit is set subtract topmost bit value, convert to bigint, set topmost bit and convert to binary(8)
	ELSE CONVERT(BINARY(8),CONVERT(BIGINT,query_hash - CONVERT(DECIMAL(20,0),@m)*-1)|@m)
	END AS dmv_query_hash,
	*
FROM #Queries
),
cte_query_stats_dmv AS
(
SELECT
	qs.query_hash,
	-- unsigned integer value of all bits except topmost bit then adding back topmost bit value if it was set
	CONVERT(DECIMAL(20,0),qs.query_hash & ~@m) + CONVERT(DECIMAL(20,0),qs.query_hash & @m) * -1 AS extevt_query_hash,
	SUBSTRING(st.text,qs.statement_start_offset/2+1,(CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2 + 1) AS stmttext
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
)
SELECT extevt.*, sample_dmv_query.*
FROM cte_extended_event extevt
OUTER APPLY (
	-- show one sample dmv sql statement for the given query hash
	SELECT TOP 1 *
	FROM cte_query_stats_dmv qs
	WHERE qs.query_hash = extevt.dmv_query_hash
) AS sample_dmv_query

queryhash

On the left hand side of the result set you’ll see the extended event integer query hash converted to the dmv binary(8) representation and on the right you’ll see the binary(8) dmv value converted to the integer representation.

1 Comment