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 ), 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 (<br /> SELECT * FROM dpa_repository.ignite.CONSPH_1<br /> UNION ALL<br /> SELECT * FROM dpa_repository.ignite.CONSPH_2<br /> --UNION ALL<br /> --SELECT * FROM dpa_repository.ignite.CONSPH_3<br /> UNION ALL<br /> SELECT * FROM dpa_repository.ignite.CONSPH_4<br /> --UNION ALL<br /> --SELECT * FROM dpa_repository.ignite.CONSPH_5<br /> UNION ALL<br /> SELECT * FROM dpa_repository.ignite.CONSPH_6<br /> UNION ALL<br /> SELECT * FROM dpa_repository.ignite.CONSPH_7<br /> UNION ALL<br /> SELECT * FROM dpa_repository.ignite.CONSPH_8<br /> UNION ALL<br /> SELECT * FROM dpa_repository.ignite.CONSPH_9<br />) x <br />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 <br />(0x060032000f6c382800bf051ede00000001000000000000000000000000000000000000000000000000000000)
Happy hunting!
