Wouldn't ya know it!! Some of my query plans exceed the 128 maximum levels of nesting allowed for the XML data type.
That results in errors when trying to convert the query store plan to XML. I want to convert the plans to XML in my queries in order to use exist(), value(), query() and nodes() xquery methods.
So I wrote a cursor to identify the plan_ids whose accompanying query plan XML had more than 128 levels of nesting.
In this SQL Server 2016 instance, trace flag 3226 is enabled globally - allowing me to use it as part of my check that convert to XML didn't result in an error.
OK... now I can identify the plans that have too many levels of nesting there's a good chance I can do something with them to circumvent the errors.
Yesterday I created auxiliary tables - one based on sys.query_store_plan and one based on sys.query_store_runtime_stats. I moved the identified plans and associated runtime stats to the auxiliary tables then used sp_query_store_remove_plan to remove the plan and its runtime stats from query store. But I've got a better idea I'll share soon...
SQL Server Query Store Plan XML with more than 128 levels of nesting - Part II