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
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.
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!
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.