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

I'm trying to analyze and categorize thousands of queries from the Query Store.

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.

SET NOCOUNT ON;
DECLARE @error_msg NVARCHAR(256);
DECLARE @plan_id BIGINT;
DECLARE plan_cursor CURSOR FOR
SELECT plan_id FROM sys.query_store_plan
ORDER BY plan_id DESC;

OPEN plan_cursor;  
FETCH NEXT FROM plan_cursor INTO @plan_id;  

WHILE @@FETCH_STATUS = 0  
BEGIN  
   BEGIN TRY
       ;WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
       SELECT 1 FROM ( 
       SELECT CONVERT(XML, query_plan) qplan FROM sys.query_store_plan where plan_id = @plan_id) subQ
    WHERE subQ.qplan.exist('//p:TraceFlag/@Value[. = "3226"]') = 0
   END TRY
   BEGIN CATCH 
      SET @error_msg = error_message() 
      PRINT CONVERT(NVARCHAR(32), @plan_id) + '-' + @error_msg
   END CATCH 
   FETCH NEXT FROM plan_cursor INTO @plan_id 
END  

CLOSE plan_cursor; 
DEALLOCATE plan_cursor;

My results:

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

http://sql-sasquatch.blogspot.com/2017/03/sql-server-query-store-plan-xml-with_28.html