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.

The post Query Hash Formula Changes Between Versions appeared first on Home Of The Scary DBA.