Correlating xEvents with DMVs

Cross post with http://aka.ms/sqlserverteam

Since the inception of xEvents there has been the need to occasionally correlate data from xEvent collections and DMVs. Specifically, using the query_hash and query_plan_hash actions and correlate with DMVs such as sys.dm_exec_requests and sys.dm_exec_query_stats for example. The blog post on Correlating XE query_hash and query_plan_hash to sys.dm_exec_query_stats in order to retrieve Execution plans for high Resource statements is a good example.

However, sometimes correlating this information is not easy. And that’s because the xEvent actions above are not the same data types as the respective columns in the DMVs, and the community spoke about that by opening Connect items.

Back in SQL Server 2016 and 2014 SP2 we introduced new xEvent actions query_hash_signed and query_plan_hash_signed, that allow you to join the DMVs with xEvents such as query_thread_profile, sp_statement_completed and sql_statement_completed.

These exist side-by-side with the “non-signed” events so not to break any existing user implementations using these.

Let’s see an example creating the xEvent session below:

CREATE EVENT SESSION [QueriesXE] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,
    sqlserver.query_plan_hash_signed,sqlserver.server_instance_name,
    sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\QueryProfileXE.xel',
    max_file_size=(50),max_rollover_files=(2))
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)
GO

Then run a query that’s captured in the session:

USE AdventureWorks2016CTP3
GO
ALTER EVENT SESSION [QueriesXE] ON SERVER STATE = START
GO
SELECT *
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
ORDER BY Style DESC
OPTION (MAXDOP 1)
GO
ALTER EVENT SESSION [QueriesXE] ON SERVER STATE = STOP
GO

When opening the xel file, we can see the following:

Use the new signed query plan hash and run below to see this query’s stats:

SELECT * FROM sys.dm_exec_query_stats
WHERE CAST(query_plan_hash AS BIGINT) = -2366658896559316405

Or adding the cached plan:

SELECT * FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE CAST(query_plan_hash AS BIGINT) = -2366658896559316405

The above correlation can provide valuable insights during troubleshooting processes, especially when a more prolonged yet lightweight xEvent trace is collected, and upon analyzing it, more information from DMVs/DMFs is needed.

Pedro Lopes (@sqlpto) – Senior Program Manager

Cross post with http://aka.ms/sqlserverteam

Cancel reply