Way Back When
I blogged about the new USE HINT that logs query plans that use it to a new XE session. The use hint and session both share the same name — query_plan_profile.
There are currently some rather unfortunate limitations to the event in the GUI. Right now, there’s no query plan tab so you can easily view the plan, you have to collect the text of the query separately, the session doesn’t collect any plan-identifying hashes by default, and the Select operator is missing from the plan.
Plus, in typical Microsoft fashion, information is presented alphabetically, instead of grouping related attributes.
If one undertook the task of deciphering the different memory portions of the query plan, one would find themselves quite disappointed.
If You Wanna Get Some Basic Data Out
And you created the same Event that I did, you can do some of this:
|
1 2 3 4 5 6 7 8 9 10 |
DROP TABLE IF EXISTS #query_plan_profile CREATE TABLE #query_plan_profile ( event_data XML ); INSERT #query_plan_profile WITH (TABLOCK) (event_data ) SELECT CONVERT(XML, event_data) AS event_data FROM sys.fn_xe_file_target_read_file('c:\temp\query_plan_profile*.xel', NULL, NULL, NULL); |
And some of this:
|
1 2 3 4 5 |
SELECT ... qpph.ph.value('xs:hexBinary((.)[1])', 'VARBINARY(64)') AS plan_handle FROM #query_plan_profile AS qpp CROSS APPLY qpp.event_data.nodes('/event/data[@name = "showplan_xml"]/value/*') AS qpqp (qp) CROSS APPLY qpp.event_data.nodes('/event/action[@name = "plan_handle"]/value') AS qpph (ph) |
In case you were wondering — yes, this is the weirdest bit of XQuery I think I’ve ever written:
|
1 |
qpph.ph.value('xs:hexBinary((.)[1])', 'VARBINARY(64)') AS plan_handle |
So weird that I asked Mikael Eriksson about it, and he Swede-shamed me into doing things the right way:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT qpp.event_data.value('(/event/@name)[1]', 'NVARCHAR(256)') AS event_name, qpp.event_data.value('(/event/@timestamp)[1]', 'NVARCHAR(256)') AS event_time, qpp.event_data.value('(event/data[@name = "source_database_id"]/value)[1]', 'INT') AS database_id, qpp.event_data.value('(event/data[@name = "object_type"]/text)[1]', 'NVARCHAR(100)') AS object_type, qpp.event_data.value('(event/data[@name = "object_name"]/value)[1]', 'NVARCHAR(1000)') AS object_name, qpp.event_data.value('(event/data[@name = "object_id"]/value)[1]', 'INT') AS object_id, qpp.event_data.value('(event/data[@name = "duration"]/value)[1]', 'BIGINT') / 1000 AS duration_ms, qpp.event_data.value('(event/data[@name = "cpu_time"]/value)[1]', 'BIGINT') / 1000 AS cpu_time_ms, qpp.event_data.value('(event/data[@name = "estimated_rows"]/value)[1]', 'BIGINT') estimated_rows, qpp.event_data.value('(event/data[@name = "estimated_cost"]/value)[1]', 'FLOAT') estimated_cost, qpp.event_data.value('(event/data[@name = "serial_ideal_memory_kb"]/value)[1]', 'BIGINT') serial_ideal_memory_kb, qpp.event_data.value('(event/data[@name = "requested_memory_kb"]/value)[1]', 'BIGINT') requested_memory_kb, qpp.event_data.value('(event/data[@name = "used_memory_kb"]/value)[1]', 'BIGINT') used_memory_kb, qpp.event_data.value('(event/data[@name = "ideal_memory_kb"]/value)[1]', 'BIGINT') ideal_memory_kb, qpp.event_data.value('(event/data[@name = "granted_memory_kb"]/value)[1]', 'BIGINT') granted_memory_kb, qpp.event_data.value('(event/data[@name = "dop"]/value)[1]', 'BIGINT') dop, qpqp.qp.query('.') showplan_xml, qpp.event_data.query('(event/action[@name = "sql_text"]/value)') sql_text, qpp.event_data.value('(event/action[@name = "query_plan_hash_signed"]/value)[1]', 'BIGINT') query_plan_hash_signed, qpp.event_data.value('(event/action[@name = "query_hash_signed"]/value)[1]', 'BIGINT') query_hash_signed, qpp.event_data.value('xs:hexBinary((/event/action[@name = "plan_handle"]/value/text())[1])', 'VARBINARY(64)') AS plan_handle FROM #query_plan_profile AS qpp CROSS APPLY qpp.event_data.nodes('/event/data[@name = "showplan_xml"]/value/*') AS qpqp (qp) |
If you want to aggregate things a bit, you can do this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
WITH query_plan_profile AS ( SELECT qpp.event_data.value('(/event/@name)[1]', 'NVARCHAR(256)') AS event_name, qpp.event_data.value('(/event/@timestamp)[1]', 'NVARCHAR(256)') AS event_time, qpp.event_data.value('(event/data[@name = "source_database_id"]/value)[1]', 'INT') AS database_id, qpp.event_data.value('(event/data[@name = "object_type"]/text)[1]', 'NVARCHAR(100)') AS object_type, qpp.event_data.value('(event/data[@name = "object_name"]/value)[1]', 'NVARCHAR(1000)') AS object_name, qpp.event_data.value('(event/data[@name = "object_id"]/value)[1]', 'INT') AS object_id, qpp.event_data.value('(event/data[@name = "duration"]/value)[1]', 'BIGINT') / 1000 AS duration_ms, qpp.event_data.value('(event/data[@name = "cpu_time"]/value)[1]', 'BIGINT') / 1000 AS cpu_time_ms, qpp.event_data.value('(event/data[@name = "estimated_rows"]/value)[1]', 'BIGINT') estimated_rows, qpp.event_data.value('(event/data[@name = "estimated_cost"]/value)[1]', 'FLOAT') estimated_cost, qpp.event_data.value('(event/data[@name = "serial_ideal_memory_kb"]/value)[1]', 'BIGINT') serial_ideal_memory_kb, qpp.event_data.value('(event/data[@name = "requested_memory_kb"]/value)[1]', 'BIGINT') requested_memory_kb, qpp.event_data.value('(event/data[@name = "used_memory_kb"]/value)[1]', 'BIGINT') used_memory_kb, qpp.event_data.value('(event/data[@name = "ideal_memory_kb"]/value)[1]', 'BIGINT') ideal_memory_kb, qpp.event_data.value('(event/data[@name = "granted_memory_kb"]/value)[1]', 'BIGINT') granted_memory_kb, qpp.event_data.value('(event/data[@name = "dop"]/value)[1]', 'BIGINT') dop, qpqp.qp.query('.') showplan_xml, qpp.event_data.query('(event/action[@name = "sql_text"]/value)') sql_text, qpp.event_data.value('(event/action[@name = "query_plan_hash_signed"]/value)[1]', 'BIGINT') query_plan_hash_signed, qpp.event_data.value('(event/action[@name = "query_hash_signed"]/value)[1]', 'BIGINT') query_hash_signed, qpp.event_data.value('xs:hexBinary((/event/action[@name = "plan_handle"]/value/text())[1])', 'VARBINARY(64)') AS plan_handle, qpp.event_data FROM #query_plan_profile AS qpp CROSS APPLY qpp.event_data.nodes('/event/data[@name = "showplan_xml"]/value/*') AS qpqp (qp) ) SELECT DB_NAME(qpp.database_id) AS database_name, qpp.query_plan_hash_signed, qpp.query_hash_signed, qpp.plan_handle, COUNT(qpp.query_plan_hash_signed) AS count_plans, COUNT(qpp.query_hash_signed) AS count_queries, COUNT(DISTINCT qpp.query_plan_hash_signed) AS count_distinct_plans, COUNT(DISTINCT qpp.query_hash_signed) AS count_distinct_queries, SUM(qpp.duration_ms) AS total_duration, AVG(qpp.duration_ms) AS avg_duration, SUM(qpp.cpu_time_ms) AS total_cpu, AVG(qpp.cpu_time_ms) AS avg_cpu, MIN(qpp.estimated_rows) AS min_rows, MAX(qpp.estimated_rows) AS max_rows, MIN(qpp.estimated_cost) AS min_cost, MAX(qpp.estimated_cost) AS max_cost, MIN(qpp.serial_ideal_memory_kb) AS min_serial_ideal_memory_kb, MAX(qpp.serial_ideal_memory_kb) AS max_serial_ideal_memory_kb, MIN(qpp.requested_memory_kb) AS min_requested_memory_kb, MAX(qpp.requested_memory_kb) AS max_requested_memory_kb, MIN(qpp.used_memory_kb) AS min_used_memory_kb, MAX(qpp.used_memory_kb) AS max_used_memory_kb, MIN(qpp.ideal_memory_kb) AS min_ideal_memory_kb, MAX(qpp.ideal_memory_kb) AS max_ideal_memory_kb, MIN(qpp.granted_memory_kb) AS min_granted_memory_kb, MAX(qpp.granted_memory_kb) AS max_granted_memory_kb, MIN(qpp.dop) AS min_dop, MAX(qpp.dop) AS max_dop FROM query_plan_profile AS qpp GROUP BY DB_NAME(qpp.database_id), qpp.query_plan_hash_signed, qpp.query_hash_signed, qpp.plan_handle; |
If you want to check other useful DMV data, you can do this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
WITH query_plan_profile AS ( SELECT qpp.event_data.value('(/event/@name)[1]', 'NVARCHAR(256)') AS event_name, qpp.event_data.value('(/event/@timestamp)[1]', 'NVARCHAR(256)') AS event_time, qpp.event_data.value('(event/data[@name = "source_database_id"]/value)[1]', 'INT') AS database_id, qpp.event_data.value('(event/data[@name = "object_type"]/text)[1]', 'NVARCHAR(100)') AS object_type, qpp.event_data.value('(event/data[@name = "object_name"]/value)[1]', 'NVARCHAR(1000)') AS object_name, qpp.event_data.value('(event/data[@name = "object_id"]/value)[1]', 'INT') AS object_id, qpp.event_data.value('(event/data[@name = "duration"]/value)[1]', 'BIGINT') / 1000 AS duration_ms, qpp.event_data.value('(event/data[@name = "cpu_time"]/value)[1]', 'BIGINT') / 1000 AS cpu_time_ms, qpp.event_data.value('(event/data[@name = "estimated_rows"]/value)[1]', 'BIGINT') estimated_rows, qpp.event_data.value('(event/data[@name = "estimated_cost"]/value)[1]', 'FLOAT') estimated_cost, qpp.event_data.value('(event/data[@name = "serial_ideal_memory_kb"]/value)[1]', 'BIGINT') serial_ideal_memory_kb, qpp.event_data.value('(event/data[@name = "requested_memory_kb"]/value)[1]', 'BIGINT') requested_memory_kb, qpp.event_data.value('(event/data[@name = "used_memory_kb"]/value)[1]', 'BIGINT') used_memory_kb, qpp.event_data.value('(event/data[@name = "ideal_memory_kb"]/value)[1]', 'BIGINT') ideal_memory_kb, qpp.event_data.value('(event/data[@name = "granted_memory_kb"]/value)[1]', 'BIGINT') granted_memory_kb, qpp.event_data.value('(event/data[@name = "dop"]/value)[1]', 'BIGINT') dop, qpqp.qp.query('.') showplan_xml, qpp.event_data.query('(event/action[@name = "sql_text"]/value)') sql_text, qpp.event_data.value('(event/action[@name = "query_plan_hash_signed"]/value)[1]', 'BIGINT') query_plan_hash_signed, qpp.event_data.value('(event/action[@name = "query_hash_signed"]/value)[1]', 'BIGINT') query_hash_signed, qpp.event_data.value('xs:hexBinary((/event/action[@name = "plan_handle"]/value/text())[1])', 'VARBINARY(64)') AS plan_handle, qpp.event_data FROM #query_plan_profile AS qpp CROSS APPLY qpp.event_data.nodes('/event/data[@name = "showplan_xml"]/value/*') AS qpqp (qp) ) SELECT qpp.event_name, qpp.event_time, qpp.database_id, qpp.object_type, qpp.object_name, qpp.object_id, qpp.duration_ms, qpp.cpu_time_ms, qpp.estimated_rows, qpp.estimated_cost, qpp.serial_ideal_memory_kb, qpp.requested_memory_kb, qpp.used_memory_kb, qpp.ideal_memory_kb, qpp.granted_memory_kb, qpp.dop, qpp.showplan_xml, qpp.sql_text, qpp.query_plan_hash_signed, qpp.query_hash_signed, qpp.plan_handle, deqs.* FROM query_plan_profile AS qpp LEFT JOIN sys.dm_exec_query_stats AS deqs ON deqs.sql_handle = qpp.plan_handle; |
Will This Take Off?
Well, I don’t know.
Part of me hopes so, because one of my wish list items has always been having the additional “actual plan” information to analyze in BlitzCache, and this looks like a close bet.
It’s tempting, but I think I’m going to hold off to see if it gets any traction first. I tried to aid traction with sp_BlitzQueryStore, but I don’t think that really worked, both because of the slow trickle to 2016+, and the weariness of turning the feature on for various reasons.
I’m hopeful that it’ll get better someday, but I think I’d have to run into a few clients using this before I invest a lot of time in it.
Thanks for reading!
2 Comments. Leave new
Great post, Erik.
A (somewhat) on-topic question, what is the difference between “Granted_memory_kb” and “used_memory_kb”? In simpler terms, it seems like SQL Server has “given” “Granted_memory_kb” of RAM while the query itself has used only “used_memory_kb”. But would this mean the unused “Granted_memory_kb” would be unavailable for any other SQL use?
Santa – I cover that in my Mastering Query Tuning class.