By Mark Sinkinson


2014-04-23 14:31:56 8 Comments

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?

2 comments

@MMJ 2015-08-18 17:18:58

How would I extract the value of 0x9473FBCCBC01AFE from an XML query plan as a VARBINARY, rather than a VARCHAR?

I faced something like that using HeidiSQL to query on CASD tables, and solved with fn_varbintohexstr(), like this:

SELECT master.dbo.fn_varbintohexstr(table.hexfield) FROM table;

With HeidiSQL, the value was wrong like '0x3F3F3F3F3F3F3F3F' and became correct like '0x158B1DB75616484695684007CE98E21C'.

OBS: Works since MSSQL 2008! Hope it helps!

@Erik 2015-08-18 17:32:43

Note the caveats to using fn_varbintohexstr() mentioned here.

@Aaron Bertrand 2014-04-23 14:43:03

You need to use a specific style when you expect to keep the same binary value when converting from a string. Otherwise SQL Server tries to encode the string the same way it would encode 'bob' or 'frank'.

That said, your input string doesn't look correct - there is either a byte missing or one byte too many. This works fine if I drop the trailing E:

SELECT CONVERT(VARBINARY(25), '0x9473FBCCBC01AF', 1);
------------ the ,1 is important ---------------^^^

Result is binary:

----------------
0x9473FBCCBC01AF

@Mark Sinkinson 2014-04-23 14:46:52

Ah, the ,1 was what I was missing. That was easier than I was expecting! Thanks!

@Mark Sinkinson 2014-04-23 14:52:01

Not sure about the missing/extra byte. In the 2666 records I have, there's 183 that fail the TRY_CONVERT

@Aaron Bertrand 2014-04-23 14:58:36

May need to append a character (say, 0) to any string with an odd character count. That changes the value, but should always change the same value the same way (and I don't suspect you'll have any collisions with or without the 0).

@Mark Sinkinson 2014-04-24 10:29:41

Is that not a bug? The queryplanhash in the xml is explicitly set to that value...Surely a TRY_CONVERT to a BINARY should not return NULL

@Mark Sinkinson 2014-04-24 12:31:00

From comparing the values saved in my table to the xml it's actually a leading 0 that is missing. So the value should be 0x09473FBCCBC01AF. I can fix those up with a simple REPLACE, but I'm sure it's a bug...

@Aaron Bertrand 2014-04-24 14:53:43

@Mark it's not clear to me exactly where this QueryPlan value comes from or how it got stored in the table - it could have lost that leading 0 in a variety of ways. I could find no evidence of truncated values on my system, but I don't know how to perform a full repro.

Related Questions

Sponsored Content

4 Answered Questions

1 Answered Questions

INSERT/SELECT xml column from one table to another

1 Answered Questions

[SOLVED] Insert String Value To Binary(64) Column

0 Answered Questions

0 Answered Questions

Way to import extended event data into table efficiently

1 Answered Questions

[SOLVED] SQL Server Query Plan XML: QueryPlanHash Length

1 Answered Questions

1 Answered Questions

[SOLVED] Convert Byte Array From XML to VARBINARY

2 Answered Questions

1 Answered Questions

Sponsored Content