SQLPlan query_plan_hash / query_hash do not match sys.dm_exec_query_stats ??
-
Altough I get to see equal values from both sources for the columns query_plan_hash and query_hash , the query does not evaluate them as being equal !
Microsoft SQL Server 2014 - 12.0.2402.0 (X64)
Aug 13 2014 11:36:34
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
;WITH XMLNAMESPACES( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' )
, cteStmtSimple
AS ( SELECT cp.plan_handle
, qp.query_plan
, qp.dbid
, xmlStmtSimple.value('@CardinalityEstimationModelVersion' , 'int' )AS CardinalityEstimationModelVersion
, xmlStmtSimple.value('@StatementEstRows', 'float') AS StatementEstRows
, xmlStmtSimple.value('@StatementOptmLevel', 'varchar(255)') AS StatementOptmLevel
, xmlStmtSimple.value('@StatementOptmEarlyAbortReason', 'varchar(255)') AS StatementOptmEarlyAbortReason
, xmlStmtSimple.value('@StatementSubTreeCost', 'float') AS StatementSubTreeCost
, xmlStmtSimple.value('@StatementType', 'varchar(255)') AS StatementType
, xmlStmtSimple.value('@QueryHash', 'varchar(255)') AS QueryHash
, xmlStmtSimple.value('@QueryPlanHash', 'varchar(255)') AS QueryPlanHash
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan( cp.plan_handle ) AS qp
CROSS APPLY qp.query_plan.nodes( '//StmtSimple' ) AS t( xmlStmtSimple )
WHERE qp.query_plan.exist( '//StmtSimple' ) = 1 )
SELECT D.name DbName, D.create_date as DbCreateDate, D.compatibility_level DbCompatLvl
, P.*
, eqs.query_hash
, case cast(eqs.query_hash as varchar(255) ) when P.QueryHash then 'OK' else '--' end QH
, eqs.query_plan_hash
, case cast(eqs.query_plan_hash as varchar(255) ) when P.QueryPlanHash then 'OK' else '--' end QpH
, eqs.last_rows
, eqs.total_rows / eqs.execution_count as Avg_Rows
FROM cteStmtSimple P
inner join sys.databases D
on D.database_id = P.dbid
and D.compatibility_level > 110
left join sys.dm_exec_query_stats eqs
on eqs.plan_handle = P.plan_handle
WHERE P.CardinalityEstimationModelVersion is not null
OPTION( RECOMPILE )
I would have expected to be able to use columns query_hash and query_plan_hash in the join with eqs, but apparently the engine is not considering the to be equal 🙁
Clearly I must be missing something :crazy:
QueryHash QueryPlanHash query_hash QH query_plan_hash QpH
0x49A7E7AB370A6393 0xE252C107E7F50FBE 0x49A7E7AB370A6393 -- 0xE252C107E7F50FBE --
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
No, query_hash and query_plan_hash are not the same thing.
MSDN - sys.dm_exec_query_stats
query_hash
Binary(8)
Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values.
query_plan_hash
binary(8)
Binary hash value calculated on the query execution plan and used to identify similar query execution plans. You can use query plan hash to find the cumulative cost of queries with similar execution plans.
Will always be 0x000 when a natively compiled stored procedure queries a memory-optimized table.
-
I know you the values of query_hash and query_plan_hash are different and the columns serve different purposes.
My points are:
- why is query_hash of the sqlplan not equal with the query_hash of the dmv
- why is query_plan_hash of the sqlplan not equal with the query_plan_hash of the dmv
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I'm assuming it's the conversion from a binary(8) to varchar that's causing the problem. If you output cast(eqs.query_hash as varchar(255) ) in the select, does it look different?
You might want to use convert rather than cast and use a different style to get it matching the output from the first query. E.g.:
convert(varchar(255),eqs.query_hash,1) -
HowardW (9/10/2014)
I'm assuming it's the conversion from a binary(8) to varchar that's causing the problem. If you output cast(eqs.query_hash as varchar(255) ) in the select, does it look different?You might want to use convert rather than cast and use a different style to get it matching the output from the first query. E.g.:
convert(varchar(255),eqs.query_hash,1)You nailed it, Howard ! Thanks !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply