Updated March 7, 2017
DPA displays a hash value for SQL server plans and text. These are not the long values of VARBINARY data needed to look up information from SQL server's DMVs. This article is going to cover how and where in DPA we can get the longer VARBINARY values.
DPA 9.0 to 10.1.313
There are two separate values that we will cover
The SQL_handle. DPA displays a hash value and not the full SQL_handle. While DPA's hash is based on the SQL handle and some other information about the SQL statement, there is not a way to reverse this to get the SQL_handle at this time. The SQL handle is displayed on the Current tab in DPA but, as of version 10.1.313 is not stored in the repository.
If you can click into the instance you are looking for the SQL_HANDLE and SQL hash (value in DPA) are both displayed. there is a feature request to store the SQL_HANDLE https://thwack.solarwinds.com/ideas/5202
The query_plan_hash is a value generated by DPA. The
sys.dm_exec_query_stats has a VARBINARY value that is used. We do not display this but we store it in the repo.
Try this query.
Note that the _XX needs to be your monitored server ID.
select id, name from cond;
inner join ignite.conspt_XX spt
on sph.PLAN_HASH_VALUE = spt.PLAN_HASH_VALUE
where sph.PLAN_HANDLE = 'value from DPA'
You can use this PLAN_HANDLE value in a query of