Not long ago I posted about a bugBlitzCache because Query Plan Hash in the XML was being truncated.
Well, yesterday I got an email that Adam Machanic has never gotten (I kid, I kid).
CLOSED AS FIXED
Well, sort of.
It’ll be fixed in vNext, and a CU for 2016. Anything before that, and you’re on your own. And it’ll only kick in for new plans, so any old plans with busted Query Plan Hashes will continue to have them.
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.
If you didn’t have a reason to upgrade SQL Server before, this is clearly it.
Thanks for reading, and thanks to everyone who voted!