I've been keeping a log of expensive running queries, along with their query plans, in a table to allow us to monitor trends in performance and identify areas that need optimising.
However, it's come to the point where the query plans are taking up too much space (as we're storing the entire plan against each query).
I'm therefore attempting to normalise the existing data by extracting the QueryPlanHash and QueryPlan to another table.
CREATE TABLE QueryPlans
(
QueryPlanHash VARBINARY(25),
QueryPlan XML,
CONSTRAINT PK_QueryPlans PRIMARY KEY
(
QueryPlanHash
)
);
As the definition of the query_plan_hash
in sys.dm_exec_query_stats
is a binary field (and I'll regularly be inserting new data), I was using VARBINARY
for the data type in my new table.
However, the insert below fails...
INSERT INTO QueryPlans
( QueryPlanHash, QueryPlan )
SELECT queryplanhash, queryplan
FROM
(
SELECT
p.value('(./@QueryPlanHash)[1]', 'varchar(20)') queryplanhash,
QueryPlan,
ROW_NUMBER() OVER (PARTITION BY p.value('(./@QueryPlanHash)[1]', 'varchar(20)') ORDER BY DateRecorded) rownum
FROM table
CROSS APPLY QueryPlan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple[@QueryPlanHash]') t(p)
) data
WHERE rownum = 1
....with the error
Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.
The problem is that the query plan hashes are already in binary format, however stored as VARCHAR in the XML Query Plan e.g.
0x9473FBCCBC01AFE
and CONVERT to BINARY gives a completely different value
0x3078393437334642434342433031414645
I tried changing the value definition in the XQuery select to binary, but then it returned no values.
How would I extract the value of 0x9473FBCCBC01AFE
from an XML query plan as a VARBINARY
, rather than a VARCHAR
?