Submit a ticketCall us

Looking to compare latest NPM features with previous versions of NPM?
The NPM new feature summary offers a comparison of new features and improvements offered with this release.

 

 

 

 

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: 146 Votes: 0 Revisions: 5

Updated March 7, 2017

Overview

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. 

Environment

DPA 9.0 to 10.1.313 

Detail

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; 

select 
PLAN_HANDLE
,QUERY_PLAN_XML 
,PIECE 
from 
ignite.CONSPH_XX sph 
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 sys.dm_exec_query_stats.
 

 

 

 

Last modified
23:05, 6 Mar 2017

Tags

Classifications

Public