SQL Server Query Store Plan XML with more than 128 levels of nesting - Part II

Remember this?
SQL Server Query Store Plan XML with more than 128 levels of nesting - Part I
http://sql-sasquatch.blogspot.com/2017/03/sql-server-query-store-plan-xml-with.html

Here I am getting into trouble again.

One of the reasons I'm digging into query store is to round up queries timing out in the optimizer.  Hoping to find ways for them to avoid that timeout and experience higher quality plans.


In theory this query would allow me to find the first query plan with plan_id higher than 53100 that experienced an optimizer timeout.

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT TOP (1) qp.plan_id FROM
(SELECT qsp.plan_id, qsq.query_hash,
        CONVERT(XML, query_plan) AS qplan 
FROM sys.query_store_plan qsp
JOIN sys.query_store_query qsq on qsq.query_id = qsp.query_id
) AS qp
WHERE qp.qplan.exist('//p:StmtSimple/@StatementOptmEarlyAbortReason[.="TimeOut"]') = 1
AND
qp.plan_id > 53100
ORDER BY qp.plan_id ASC
OPTION (MAXDOP 1);

Oops.  Got at least one of these between 53100 and the first plan_id to satisfy the query.

Identifying all of the highly nested plans, moving them to separate tables and removing the plans and runtime stats from query store would let me squeak by.  But I don't like moving a problem from one table to another.

Y'know, looking at the plan XML below it strikes me most of the nested levels in a highly nested plan will be in the RelOp node.

Well... what if I cut off the first part of the XML - up to RelOp - and cut off the end, from the start of ParameterList.  Those two pieces could be stitched together into well-formed XML, and without all of the operatores it should certainly fit within the 128 max nest levels.

Yep.  I tried and it works :-)

;WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT TOP (1) qp.plan_id FROM
(SELECT qsp.plan_id, qsq.query_hash,
        CASE WHEN PATINDEX('%<RelOp NodeId="0"%', query_plan) > 0 THEN
        CONVERT(XML, SUBSTRING(query_plan, 1, PATINDEX('%<RelOp NodeId="0"%', query_plan) - 1) +
                     SUBSTRING(query_plan, PATINDEX('%</QueryPlan>%', query_plan), LEN(query_plan) + 1) ) 
  ELSE CONVERT(XML, query_plan) 
  END AS qplan 
FROM sys.query_store_plan qsp
JOIN sys.query_store_query qsq on qsq.query_id = qsp.query_id
) AS qp
WHERE qp.qplan.exist('//p:StmtSimple/@StatementOptmEarlyAbortReason[.="TimeOut"]') = 1
AND
qp.plan_id > 53100
ORDER BY qp.plan_id ASC
OPTION (MAXDOP 1);