Correlating xEvents with DMVs
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.
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