Identifying Execution Plans with Probe Residual I am trying to find out Execution Plans that has Probe Residual.  

Need to know the following 


Which physical and logical operator has this Probe Residual  
What is the Cost% of that operator in the query  
Associated Execution plan  
Query Text


Following is a try that I gave – but I am stuck for getting other details. How to get these details?

Note: I am using SQL Server 2012

WITH XMLNAMESPACES
(
    DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT
    DECP.cacheobjtype,
    DECP.objtype,
    DECP.plan_handle,
    DEQP.objectid,
    DEQP.query_plan,
    DEST.[text]
FROM sys.dm_exec_cached_plans AS DECP
CROSS APPLY sys.dm_exec_query_plan(DECP.plan_handle) AS DEQP
CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) AS DEST
WHERE
    1 = DEQP.query_plan.exist(
        '//RelOp[
            @PhysicalOp = "Hash Match"
            ]')


A Probe Residual Example



References from blogs/articles of Grant Fritchey and Rob Farley below


Probe Residual on Hash Match - Home Of The Scary DBA
Probe Residual when you have a Hash Match – a hidden cost in execution plans
What you want to do is to use nodes() Method (xml Data Type) in a cross apply to shred on RelOp nodes so you then can use value() Method (xml Data Type) to get the property values you are looking for.

You are specifically looking for the RelOp nodes that have a Hash/ProbeResidual node so you should use that in a predicate in the XQuery parameter to the nodes() function. 

WITH xmlnamespaces(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT DEQP.query_plan,
       DEST.text,
       RO.X.value('@PhysicalOp', 'nvarchar(50)') as PhysicalOp,
       RO.X.value('@LogicalOp', 'nvarchar(50)') as LogicalOp,
       RO.X.value('@EstimatedTotalSubtreeCost', 'float') as EstimatedTotalSubtreeCost
FROM sys.dm_exec_cached_plans AS DECP
  CROSS APPLY sys.dm_exec_query_plan(DECP.plan_handle) AS DEQP
  CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) as DEST
  CROSS APPLY DEQP.query_plan.nodes('//RelOp[Hash/ProbeResidual]') as RO(X);