Submit a ticketCall us

Training Class Getting Started with SolarWinds Backup - February 28

This course offers customers an introduction to SolarWinds Backup, focusing on configuring the backup technology, taking backups, data restoration and data security. It is a great primer and will get you up to speed quickly on SolarWinds Backup.
Register for class.

Home > Success Center > Database Performance Analyzer (DPA) > Is there a way to correlate the SQL IDs and Plan IDs shown by DPA back to particular columns in the SQL DMVs

Is there a way to correlate the SQL IDs and Plan IDs shown by DPA back to particular columns in the SQL DMVs

Table of contents
Created by Jamin Walters, last modified by Rodim Suarez on Mar 07, 2017

Views: 454 Votes: 0 Revisions: 5

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 


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; 

ignite.CONSPH_XX sph 
inner join ignite.conspt_XX spt
where sph.PLAN_HANDLE = 'value from DPA' 

You can use this PLAN_HANDLE value in a query of sys.dm_exec_query_stats.




Last modified