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:
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.