Submit a ticketCall us

AnnouncementsFace your biggest database issues head-on

Our new eCourse helps you navigate SQL Server performance blocks by teaching you how to recognize and deal with the three DBA Disruptors: Performance Hog, Blame Shifter, and Query Blocker. Register today to learn how to defend your environment and fend off menacing disruptions.

Register for your free eCourse.

Home > Success Center > Database Performance Analyzer (DPA) > DPA - Knowledgebase Articles > 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_ret on Mar 07, 2017

Views: 734 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

Tags

Classifications

Public
/*]]>*/