Query Plan Hash in sys.dm_exec_query_stats vs Query Plan Hash in the query plan - by Erik Darling

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


42
0
Sign in
to vote
ID 3129546 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 3/18/2017 6:18:06 AM
Access Restriction Public

Description

This issue is fully detailed here: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/780

The basic gist of it is that in sys.dm_exec_query_stats, a Query Plan Hash will show up as 0x0346CE4D0DB617C1 or 0x00ED538D26FCD990, but in the query plan, they'll show up as 0x346CE4D0DB617C1 or 0xED538D26FCD990, sans the leading zeroes after the 0x.
Sign in to post a comment.
Posted by Erik Darling on 3/28/2017 at 4:07 PM
Thanks for the prompt response on this! Look forward to seeing it in vNext.
Posted by Microsoft on 3/28/2017 at 3:23 PM
Hi Erik,

Thanks for bringing up the discrepancy. We have implemented zero-extending "QueryHash" and "QueryPlanHash" attributes in the XML plan to match the output of DMVs, you will see it in the next SQL vNext CTP and in the upcoming Cumulative Update for SQL Server 2016 SP1 (CU3), scheduled for release at the end of May 2017. We have not, however, modified the XML schema due to backwards compatibility issues, the attributes in question will remain strings in there.

Please keep in mind that these attributes will be generated with leading zeros only for new plans. If you have any plans stored in Query Data Store - those will remain unchanged until purged and regenerated.

Thanks,
Sergey Ten
Microsoft Corp.
Posted by Jose Mariano Alvarez on 3/20/2017 at 11:05 AM
Column query_hash in sys.dm_exec_query_stats is Binary(8).
https://msdn.microsoft.com/en-us/library/ms189741.aspx

QueryHash and QueryPlanHash are string.
http://schemas.microsoft.com/sqlserver/2004/07/showplan/sql2016sp1/showplanxml.xsd

<xsd:attribute name="QueryHash" type="xsd:string" use="optional"/>
<xsd:attribute name="QueryPlanHash" type="xsd:string" use="optional"/>

I think, you should solve datattype conversion.