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
Best Answer
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 aHash/ProbeResidual
node so you should use that in a predicate in the XQuery parameter to thenodes()
function.