SQL Server Query Plan XML: QueryPlanHash Length

Question

This is definitely a bug. Details were on the old Connect site, and are no longer available, as it has been retired in favor of an indeterminate number of other solutions, both third and first party.

While testing some changes to sp_BlitzCache (full disclosure, I'm one of the authors), I came across what I thought was a bug in our code.

At one point, we're matching the Query Plan Hash to get query cost. We do that about like so: statement.value('sum(/p:StmtSimple[xs:hexBinary(substring(@QueryHash, 3)) = xs:hexBinary(sql:column("b.QueryHash"))]/@StatementSubTreeCost)', 'float')

This has, as far as I've seen, worked. However, In one weird case, the substring in the XML was throwing a NULL value, and the plan was showing a cost of 0, despite it being rather high.

Digging into the execution plan, I noticed that the Query Plan Hash for the one problem hash was 17 characters long, while the rest are 18. Here are examples:

QueryPlanHash="0x4410B0CA640CDA89"
QueryPlanHash="0x2262FEA4CE645569" 
QueryPlanHash="0xED4F225CC0E97E5" -- Problem!
QueryPlanHash="0xBF878EEE6DB955EA"
QueryPlanHash="0x263B53BC8C14A452"
QueryPlanHash="0x89F5F146CF4B476F"
QueryPlanHash="0xEF47EA40805C8961"
QueryPlanHash="0xB7BE27D6E43677A5"
QueryPlanHash="0x815C54EC43A6A6E9"

Query Plan Hash is listed as a binary 8 -- presumably this should always be the same length, but what does a guy like me know about binary values?

Playing with the XQuery a little bit, I found that by changing the substring to start at the second position, it would come up with a valid (albeit incorrect) hash value.

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT   
        QueryPlanCost = statement.value('sum(/p:StmtSimple/@StatementSubTreeCost)', 'float'),
        q.n.value('substring(@QueryPlanHash, 2)', 'binary(8)')
FROM    #statements s
CROSS APPLY s.statement.nodes('/p:StmtSimple') AS q(n)
OPTION(RECOMPILE);

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT   
        QueryPlanCost = statement.value('sum(/p:StmtSimple/@StatementSubTreeCost)', 'float'),
        q.n.value('substring(@QueryPlanHash, 3)', 'binary(8)')
FROM    #statements s
CROSS APPLY s.statement.nodes('/p:StmtSimple') AS q(n)
OPTION(RECOMPILE);

I'm running SQL Server 2016, SP1 (13.0.4001).

Has anyone run into this before?

Is 17 characters a valid length for a binary 8 value?

Answer 1

I think this is happening because that one hash is an odd number of characters. A valid VARBINARY is going to need to have an even number of "pairs" to correctly represent the data. So... you should be able to solve this by removing the 0x, putting a '0' at the beginning, grabbing the right 18 characters, and then casting it to VARBINARY.

CONVERT(VARBINARY(MAX), RIGHT('0' + SUBSTRING('0xED4F225CC0E97E5', 3, 20), 18), 2)

If you want something more robust, good luck, because you'd need to divide by 2 as an integer, and get the modulo of 2, and then "do the right thing" to figure out how big your data should be.