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

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

Alejandro Mesa — Tue, 12 Apr 2011 13:11:24 GMT

Adam,

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

FROM

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

INNER JOIN

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

ON T2.c1 = T1.c1';

WHILE @i < 129

BEGIN

SET @sql = @sql + N'

INNER JOIN

(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;

END

EXEC sp_executesql @sql;

AMB

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

Adam Machanic — Tue, 12 Apr 2011 13:43:28 GMT

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

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

Alejandro Mesa — Tue, 12 Apr 2011 14:33:49 GMT

LOL!!!

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

Michael Codanti — Thu, 14 Apr 2011 02:03:31 GMT

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.

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

Alejandro Mesa — Thu, 14 Apr 2011 02:31:48 GMT

Michael,

What version and service pack are you working with?

AMB

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

Adam Machanic — Thu, 14 Apr 2011 02:42:56 GMT

Michael,

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.

The Almighty Transaction (A Month of Activity Monitoring, Part 12 of 30)

Adam Machanic — Thu, 14 Apr 2011 02:43:54 GMT

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

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

Adam Machanic — Thu, 14 Apr 2011 02:55:12 GMT

Michael,

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.

Thanks,

Adam

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

Michael Codanti — Fri, 15 Apr 2011 04:19:05 GMT

Alejandro,

I am running SQL Server 2008 R2 CU6.

Adam,

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.

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

Michael Codanti — Fri, 15 Apr 2011 04:34:17 GMT

Adam,

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

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.)

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

Adam Machanic — Fri, 15 Apr 2011 14:43:41 GMT

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.

--Adam

Twenty Nine Days of Activity Monitoring (A Month of Activity Monitoring, Part 30 of 30)

Adam Machanic — Sat, 30 Apr 2011 15:45:08 GMT

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

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

DBAGeek — Fri, 11 Nov 2011 01:14:00 GMT

Hello Adam,

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

ps.

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

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

Adam Machanic — Fri, 11 Nov 2011 15:31:15 GMT

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.

--Adam