THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

"Planning" for Success (A Month of Activity Monitoring, Part 11 of 30)


Published Monday, April 11, 2011 11:00 AM by Adam Machanic



Alejandro Mesa said:


Great series and an awesome proc.

> Can you describe the problem?

Are you talking about the number of nested levels allowed in the xml data type?

We should use sys.dm_exec_text_query_plan if the plan has more than 128 levels of nested elements.

Here is a quick and dirty example.

USE Northwind;


DECLARE @sql nvarchar(MAX);

DECLARE @i int;

SET @i = 3;

SET @sql = N'




(SELECT TOP(1) OH.OrderID FROM dbo.Orders AS OH) AS T1(c1)


(SELECT TOP(1) OH.OrderID FROM dbo.Orders AS OH) AS T2(c1)

ON T2.c1 = T1.c1';

WHILE @i < 129


SET @sql = @sql + N'


(SELECT TOP(1) OH.OrderID FROM dbo.Orders AS OH) AS T' + LTRIM(@i) + N'(c1)

ON T' + LTRIM(@i) + N'.c1 = T' + LTRIM(@i - 1) + N'.c1';

SET @i = @i + 1;


EXEC sp_executesql @sql;




April 12, 2011 9:11 AM

Adam Machanic said:

Alejandro, that's the problem to which I was referring. But Northwind? Come on man! It's 2011 :-)

April 12, 2011 9:43 AM

Alejandro Mesa said:


April 12, 2011 10:33 AM

Michael Codanti said:

I get an error message about exceeding the nested levels, I don't get NULL in the query_plan column. And while I have a query that does it I can't post it.

April 13, 2011 10:03 PM

Alejandro Mesa said:


What version and service pack are you working with?



April 13, 2011 10:31 PM

Adam Machanic said:


Are you running a really old version of Who is Active, or have you modified the logic in some way? (You mentioned doing so in some prior communication we had.) I've had exception handling in there for quite some time so it should be impossible to receive the error unless something has been messed with or there is a bug in SQL Server (perhaps Alejandro knows of one that he's alluding to).

The follow up post to this one (#12) contains a query in the Homework section that exceeds the nesting level. I just tested it and it behaves as intended; the exception is caught and [query_plan] is NULL. Give it a try with an unmodified recent build of Who is Active and see if you can repro the issue.

April 13, 2011 10:42 PM

Adam Machanic said:

This post is part 12 of a 30-part series about the Who is Active stored procedure. A new post will run

April 13, 2011 10:43 PM

Adam Machanic said:


I just re-read your message, and ran another test on this end. Are you saying that you get an error message in the column itself? If so, that's a different story. It's expected behavior, that I should probably include in this post or a followup. The query plan functions won't always throw an error for the exception handling to catch. If they don't, the [query_plan] column winds up NULL. If they do, the error is reported in that column in an XML fragment. This is done so that the user can get some information about why no plan has been shown. (I don't ever like to swallow errors.)

Please let me know what, exactly, you're seeing.



April 13, 2011 10:55 PM

Michael Codanti said:


I am running SQL Server 2008 R2 CU6.


Yes, I am seeing the error message, in XML form, in the query_plan column. (So Who Is Active is running fine and doing exactly what it is supposed to.) But I hate seeing that error, as it is usually a plan I want/need to look at.

April 15, 2011 12:19 AM

Michael Codanti said:


I just ran the query from day #12 when running Who Is Active I got this in the query_plan column:

<error message="XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels." />

Note: The only way I could get it was to modify the query so that it got blocked... (Since query_plan is never populated for sleeping spids.)

April 15, 2011 12:34 AM

Adam Machanic said:

Hi Michael,

I agree, it would be much nicer to see the plan. Unfortunately, there's not much that can be done about the nesting limitation until Microsoft fixes it. (FYI it is an arbitrary limitation set to avoid problems with XML indexing -- and IMHO it's completely unnecessary, especially given how few people use XML indexing.) I may be able to bring back the plan in the additional_info column or something along those lines, but it would be quite a hassle to have to copy and paste it, save it to a .SQLPLAN file, etc. I'm not sure whether it's worth the effort to implement that.


April 15, 2011 10:43 AM

Adam Machanic said:

This post is part 30 of a 30-part series about the Who is Active stored procedure. A new post will run

April 30, 2011 11:45 AM

DBAGeek said:

Hello Adam,

Great series and perfect procedure, must have for every DBA :-)


why you don't use sys.dm_exec_query_plan to return compiled plan ?

November 10, 2011 8:14 PM

Adam Machanic said:

Hi DBAGeek,

The sys.dm_exec_text_query_plan function is much more flexible--it includes the ability to pass statement start and end offsets, and the ability to get back plans that exceed XML nesting depth limitations. Without these two features the plan collection facilities of Who is Active would be quite limited.


November 11, 2011 10:31 AM

Martin Smith said:

February 10, 2016 4:33 AM

Adam Machanic said:


At this point I'm guessing you'll have to open some similar ticket for the "New and Improved JSON Query Plan Feature," coming soon to SQL Server. (So rumor has it! Even if I started the rumor myself.)

February 10, 2016 4:58 PM
New Comments to this post are disabled

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement