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.

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • 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