Connect Item: Query Plan Hash in sys.dm_exec_query_stats vs Query Plan Hash in the query plan

SQL Server

Shifty Henry said to Bugs

You know those moments when you’re looking at something for so long, and it’s still not working, and you’re pretty sure you’re losing that final grip on your sanity?

Well, that feeling was multiplied this week by an odd bug that I found, where Query Plan Hashes in system views had leading zeroes after the 0x that were stripped out in the plan XML.

This is just a quick blog post to get some attention to the issue.

The Connect Item is here.

The GitHub issue with more information is here.

If any of you bold and brave people feel like upvoting, or checking their system for similar issues, thanks in advance.

And thanks for reading!

Previous Post
sp_BlitzCache Tip: Cutting Through Heavy Dynamic SQL Use
Next Post
[Video] Office Hours 2017/03/15 (With Transcriptions)

6 Comments. Leave new

  • Stephen Morris
    March 20, 2017 3:33 am


  • It seems like binary constants are not a standard thing in SQL. The ansi SQL standard doesn’t speak about them and Oracle uses something like 0b1234 instead of 0x1234. (So Microsoft may choose to do whatever they want)

    The docs on binary constants don’t mention behavior for an odd number of hexadecimal digits after the leading 0x.

    When interpreting strings, SQL Server seems to forgive one missing extra zero when a literal binary string has an odd number of digits:

    select ‘same’ where 0x01 = 0x1
    — returns ‘same’, SQL Server automatically pads one extra leading zero for binary strings with an odd number of digits

    select 0x1 + 0x1
    — returns 0x0101

    It seems like they’re hoping for the same sort of tolerance by apps that consume their reported handles.

    • Erik Darling
      March 21, 2017 5:06 pm

      It’s not just limited to one missing zero though. It happens with all leading zeroes (up to three I’ve noticed so far) that are discarded after the 0x. If it were just one I’d be thrilled.

  • Wow, that makes all the difference doesn’t it? That’s terrible. (upvoted)


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.