SQL XML datatype instance nest nodes limit error

Note: This problem almost always relates to #2 below.

1) Use http://manpages.ubuntu.com/manpages/bionic/man1/tidy.1.html to format your XML into a human readable form.

Make sure you have no data structures which nest deeply, >128 levels.

2) If your data looks fairly flat, no deep >128 level nesting, likely problem is infinite recursion in your data structure due to circular references.

First determine if you have references by design or accident.

Then walk through one reference chain with an editor, to ensure the reference actually terminates.

Note: I find no tool (quick searching) which can analyze a .xml file looking for circular references. This is why I suggested just using an editor search.

If your data file is complex + this problem can reoccur, you may have to write a tool that checks for circular references.

This would be a fairly simple tool to write, just take a bit of time to get it working.

Hi david,

Thanks for the explanation any suggestion how I could a filter like this -AND OBJECT_NAME(id.object_id, db.database_id) = 'Your_Table_Name' to filter on particular table. To the query above.

Thank you

If I am not mistaken, it just is like below using value():

WHERE qsp.QueryPlan.value(N'(//MissingIndex/@Table)[1]','NVARCHAR(256)') = 'your_table_name'

Select allOpen in new window

Or you can use the exist():

WHERE qsp.QueryPlan.exist('//MissingIndex[@Table == "your_table_name"]') = 1

Select allOpen in new window

Typing syntax by memory so please forgive any type-o's.

Hi Kevin,

I have got how to make the 'where' clause but where would I insert this in the SQL statement everywhere I insert it seems to give a syntax or user-defined function or aggregate error of some sort.

For example:

Msg 4121, Level 16, State 1, Line 1
Cannot find either column "qsp" or the user-defined function or aggregate "qsp.QueryPlan.value", or the name is ambiguous.


Thank you.

You got that error using exist() syntax I showed as well?  You can use that as part of the ON criteria when joining to qsp derived table in the first place and see if that helps.  If not, you may need to put the where condition inside that derived query.

Hi Kevin,

This my code now:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT qsqt.query_sql_text,
rts.plan_id,
rts.NumExecutions,
rts.MinDuration,
rts.MaxDuration,
rts.AvgDuration,
rts.AvgReads,
rts.AvgWrites,
qsp.QueryPlan,
qsp.QueryPlan.value(N'(//MissingIndex/@Table)[1]',
'NVARCHAR(256)') AS TableName,
qsp.QueryPlan.value(N'(//MissingIndex/@Schema)[1]',
'NVARCHAR(256)') AS SchemaName,
qsp.QueryPlan.value(N'(//MissingIndexGroup/@Impact)[1]',
'DECIMAL(6,4)') AS ProjectedImpact,
ColumnGroup.value('./@Usage',
'NVARCHAR(256)') AS ColumnGroupUsage,
ColumnGroupColumn.value('./@Name',
'NVARCHAR(256)') AS ColumnName
FROM sys.query_store_query AS qsq
JOIN sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
JOIN (   SELECT query_id,
CAST(query_plan AS XML) AS QueryPlan,
plan_id
FROM sys.query_store_plan) AS qsp
ON qsp.query_id = qsq.query_id AND qsp.QueryPlan.exist('//MissingIndex[@Table = "Room"]') = 1
JOIN (   SELECT qsrs.plan_id,
SUM(qsrs.count_executions) AS NumExecutions,
MIN(qsrs.min_duration) AS MinDuration,
MAX(qsrs.max_duration) AS MaxDuration,
AVG(qsrs.avg_duration) AS AvgDuration,
AVG(qsrs.avg_logical_io_reads) AS AvgReads,
AVG(qsrs.avg_logical_io_writes) AS AvgWrites
FROM sys.query_store_runtime_stats AS qsrs
GROUP BY qsrs.plan_id) AS rts
ON rts.plan_id = qsp.plan_id
CROSS APPLY qsp.QueryPlan.nodes('//MissingIndexes/MissingIndexGroup/MissingIndex/ColumnGroup') AS t1(ColumnGroup)
CROSS APPLY t1.ColumnGroup.nodes('./Column') AS t2(ColumnGroupColumn);

Select allOpen in new window

Still get the error:

Msg 6335, Level 16, State 102, Line 1
XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.

Any other suggestions on where to filter to get rid this error message.

Thank you

It seems that is a known issue with large query plans.
See Brent's article on the topic here: https://www.brentozar.com/archive/2017/06/biggest-query-plans-dont-show-dmvs/

Hi Kevin,

Then how I do filter this so it doesn't show the biggest query plans, I figure there would be away.

Thank you.

Hi Kevin,

Not familiar with the dmv function, can you provide an example of it being used to filter.

Cheers

Hi Kevin,

Thank you very much for your assistance filtering effectively now:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT qsqt.query_sql_text,
rts.plan_id,
rts.NumExecutions,
rts.MinDuration,
rts.MaxDuration,
rts.AvgDuration,
rts.AvgReads,
rts.AvgWrites,
qsp.QueryPlan,
qsp.QueryPlan.value(N'(//MissingIndex/@Table)[1]',
'NVARCHAR(256)') AS TableName,
qsp.QueryPlan.value(N'(//MissingIndex/@Schema)[1]',
'NVARCHAR(256)') AS SchemaName,
qsp.QueryPlan.value(N'(//MissingIndexGroup/@Impact)[1]',
'DECIMAL(6,4)') AS ProjectedImpact,
ColumnGroup.value('./@Usage',
'NVARCHAR(256)') AS ColumnGroupUsage,
ColumnGroupColumn.value('./@Name',
'NVARCHAR(256)') AS ColumnName
FROM sys.query_store_query AS qsq
JOIN sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
JOIN (   SELECT query_id,
CAST(query_plan AS XML) AS QueryPlan,
plan_id
FROM sys.query_store_plan) AS qsp
ON qsp.query_id = qsq.query_id --AND qsp.QueryPlan.exist('//MissingIndex[@Table = "Room"]') = 1
JOIN (   SELECT qsrs.plan_id,
SUM(qsrs.count_executions) AS NumExecutions,
MIN(qsrs.min_duration) AS MinDuration,
MAX(qsrs.max_duration) AS MaxDuration,
AVG(qsrs.avg_duration) AS AvgDuration,
AVG(qsrs.avg_logical_io_reads) AS AvgReads,
AVG(qsrs.avg_logical_io_writes) AS AvgWrites
FROM sys.query_store_runtime_stats AS qsrs
Where qsrs.count_executions > 10 AND qsrs.avg_logical_io_reads > 10
GROUP BY qsrs.plan_id) AS rts
ON rts.plan_id = qsp.plan_id
CROSS APPLY qsp.QueryPlan.nodes('//MissingIndexes/MissingIndexGroup/MissingIndex/ColumnGroup') AS t1(ColumnGroup)
CROSS APPLY t1.ColumnGroup.nodes('./Column') AS t2(ColumnGroupColumn);