Identifying Execution Plans with Probe Residual

Question

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

Need to know the following

1. Which physical and logical operator has this Probe Residual
2. What is the Cost% of that operator in the query
3. Associated Execution plan
4. 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

1. Probe Residual on Hash Match - Home Of The Scary DBA
2. Probe Residual when you have a Hash Match – a hidden cost in execution plans

---

Answer 1

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);