Query Hash Formula Changes Between Versions

21Apr 2015 by Grant Fritchey 5 Comments

The question came up on SQL Server Central that someone wanted to track queries on their 2008R2 instance and on their 2014 instance in order to validate performance after an upgrade. The plan was to use query hash values. I was curious, so I set up a quick test. First, I spun up two VMs in Azure, both A1, both Enterprise, but one for 2008R2 and one for 2014. Then, I grabbed a generic query that runs against DMVs:

SELECT TOP 10
SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,
(CASE deqs.statement_end_offset
WHEN -1 THEN DATALENGTH(dest.text)
ELSE deqs.statement_end_offset
- deqs.statement_start_offset
END) / 2 + 1) AS querystatement,
deqp.query_plan,
deqs.query_hash,
deqs.execution_count,
deqs.last_elapsed_time,
deqs.last_logical_reads,
deqs.last_logical_writes,
deqs.last_worker_time,
deqs.max_elapsed_time,
deqs.max_logical_reads,
deqs.max_logical_writes,
deqs.max_worker_time,
deqs.total_elapsed_time,
deqs.total_logical_reads,
deqs.total_logical_writes,
deqs.total_worker_time
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.total_elapsed_time DESC;

I ran the query and captured execution plans on both servers. No changes, just defaults. The output is here:

0x40B8F425DDC3D692 –2014
0x958B3949E630C004 –2008R2

I don’t have access to the code at Microsoft, but I think it’s pretty safe to say that the formula for creating the hash value for a given query has changed between versions. This isn’t surprising, but it is a little disappointing. It would have meant an easy way to compare performance between queries on two different systems. Now, you’ll have to compare based on the T-SQL text which just isn’t as easy and clean.

5 thoughts on “Query Hash Formula Changes Between Versions”

  • If you’re going to resort to comparing text, you may as well hash it yourself (with something like SHA1 or MD5) and compare those. I get that it’s frustrating that it changed, but at least when you control the hashing function you know it’s not going to change.

    Reply
  • Scott R.

    Bummer that it changed. Fully agree with your point that query hash consistency / continuity across product versions has been a good thing for comparison purposes, and is a desired ongoing quality.

    It would be interesting to hear from the product team on the rationale of why the query hash algorithm / process was changed (for what benefit – and hopefully not due to oversight).

    I plan to repeat your test and extend it to also include SQL 2008 (the first version to support query hash) and SQL 2012, to confirm if the query hash is consistent across all pre-SQL 2014 versions.

    Question: Can you confirm the “identical-ness” of your test queries?

    – Were your test queries to the two systems run from the same version / maintenance level of SSMS (to rule out that different SSMS versions / maintenance levels may have introduced subtle input query text differences and resulting query hash differences)?

    – Using either the same query file or exact copy / paste of the query text (no input query text differences)?

    Thanks,

    Scott R.

    Reply

  • Hey! I could have sworn I’ve been to this website before but after checking through some
    of the post I realized it’s new to me. Anyhow, I’m definitely delighted I found
    it and I’ll be bookmarking and checking back frequently!

    Reply

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.