SQL Server / Different SQL Versions different query hashes

Posted by Barac in SQL Monitoring, SQL Server on Oct 9th, 2017 | 0 comments

Recently I did upgrade from SQL Server 2012 towards version 2016, and I had some PRTG maps in place for two really important queries. After upgrade monitoring failed. Query hash has been changed for the exact same query. Honestly, I did not pay much attention to that previously since the majority of those PRTG maps checking same versions of SQL Server. Also, I was under impression that query optimizer computes the query hash during query compilation on base on query logic. In that case, if query hash is changed that also means query has been changed.

I am calculating query speed on the base of total query working time and execution count

SELECT
SUM(sts.total_worker_time) as total_worker_time,
SUM(sts.execution_count) as execution_count,
(SUM(sts.total_worker_time) / SUM(sts.execution_count) / 1000) AS avg_cpu_time_ms
FROM
(SELECT
exec_sts.*
FROM sys.dm_exec_query_stats AS exec_sts
CROSS APPLY sys.dm_exec_sql_text(exec_sts.sql_handle) as txt) as sts
WHERE sts.query_hash=0x8C14D07E56CFA72F --SQL Server 2012 query hash
GROUP BY sts.query_hash

If you want numeric values you can use CAST.

CAST((cast(SUM(sts.total_worker_time) as NUMERIC(15,2)) / cast(SUM(sts.execution_count) as NUMERIC(15,2))/1000) as NUMERIC(15,2)) AS avg_cpu_time_ms

And I get results similar to

Old query hash: 0x8C14D07E56CFA72F

New query hash: 0x6FF0F9C022127E58 – SQL Server 2016 query hash

I am fetching those results and creating a graph using PRTG tool.

About Me

My name is Zoran, currently living in Auckland, New Zealand. I am a data specialist with more than 15 years of hands-on experience in database administration and optimisation. I am also, Certified Microsoft Trainer (MCT) and Microsoft Certified Solutions Expert (MCSE) with a Master‘s degree in Information Technology. Sharing knowledge and contributing to the SQL Server community is my passion. I am also an organiser of the Auckland SQL User Meetup Group. As well I am active blogger and speaker at different SQL events such as SQL Saturdays, Meetups etc.

SQL Server / Different SQL Versions different query hashes

Similar Posts:

Leave a Reply Cancel reply

About Me

Email Subscription

Index Usage Report

Auckland SQL Group

MSDN PROFILE

Archives

SQL SERVER UPDATES

Recent Posts

Tag Cloud