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