This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Translate SQL Hash value on the GUI to SQL_HANDLE value of DMV

Hi there

Could you please educate me how to translate SQL hash value shown on the GUI of DPA to the SQL_HANDLE column value in most of query-related DMV?

Thanks

Kelly

  • We don't currently persist the sql_handle in DPA. There is a feature request to do this and it certainly is on our radar.

    Would you mind voting these up? (gets more attention by the product team the more votes it has)

  • While I completely agree (I authored both of those feature requests emoticons_wink.png), if there is a way to convert from the SQL hash value that DPA stores to the sql_handle value, that would be very useful information. Of course, if the SQL hash is unrelated to the sql_handle, then there is no way to convert between them. But knowing that would be useful as well.

  • Thanks.

    There is a Query_Hash column value in SQL Server DMV. I am curious the relation between DPA SQL Hash and SQL Server Query_Hash value,

    assuming their aim are the same to uniquely identify a SQL Statement.

  • A hash implies one way. Having said that, there's no path backward.

    I like the idea of persisting and mapping the handle to the hash.

  • The work-around to do this, to query the DPA repository database, is like so.

    USE dpa_repository; SELECT * FROM (
    SELECT * FROM dpa_repository.ignite.CONSPH_1
    UNION ALL
    SELECT * FROM dpa_repository.ignite.CONSPH_2
    --UNION ALL
    --SELECT * FROM dpa_repository.ignite.CONSPH_3
    UNION ALL
    SELECT * FROM dpa_repository.ignite.CONSPH_4
    --UNION ALL
    --SELECT * FROM dpa_repository.ignite.CONSPH_5
    UNION ALL
    SELECT * FROM dpa_repository.ignite.CONSPH_6
    UNION ALL
    SELECT * FROM dpa_repository.ignite.CONSPH_7
    UNION ALL
    SELECT * FROM dpa_repository.ignite.CONSPH_8
    UNION ALL
    SELECT * FROM dpa_repository.ignite.CONSPH_9
    ) x
    WHERE PLAN_HASH_VALUE = 2292414176

    Wherein 'dpa_repository' is the name of your DPA repo database, and '2292414176' is an example Plan Hash # from DPA.  'CONSPH_#' are the names of some tables in the repo that store the plan hash (#) with the plan handle (binary).  Notice that I commented-out the tables numbered '3' and '5', because in my particular repo, they were not present.  Yours may vary, so check your table list in SSMS or use intellisense to make sure you're including/excluding the ones you need.

    With the above, I was able to get the [PLAN_HANDLE], which was '060032000f6c382800bf051ede00000001000000000000000000000000000000000000000000000000000000'.  When you copy-paste that into your DBCC FREEPROCCACHE command, make sure to prefix it with '0x', and no quotes, like so:

    DBCC FREEPROCCACHE
    (0x060032000f6c382800bf051ede00000001000000000000000000000000000000000000000000000000000000)

    Happy hunting!