SQL Server Query Store Plan XML with more than 128 levels of nesting - Part I
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.
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 :-)