THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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)

This post is part 11 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.


Viewing the text of the currently running statement is a big part of the debugging battle. Another part is being able to quickly see the associated query plan.

Due to performance concerns Who is Active does not show the query plan by default; plans for big queries can sometimes span several megabytes of XML, so accessing the plan can be expensive. Enabling plan retrieval is simple: Use the @get_plans option.

@get_plans has two modes:

  • Setting the option to 1 will get the plan for the currently running statement
  • Setting the option to 2 will get the entire plan for all queries in the currently running batch

These settings align, more or less, with the default statement text retrieval mode and the @get_full_inner_text option.

Consider the following batch:

SELECT
    *
FROM sys.databases

WAITFOR DELAY '10:10:10'

SELECT
    *
FROM sys.tables
GO

Running this, then running Who is Active with @get_plans set to 1, results in the addition of the [query_plan] column to the output:

F11_01_xml_plan

The query plan is an XML showplan, and the next step for seeing the graphical version of the plan is to click on the XML instance. This will bring up a new tab in Management Studio, but what happens next depends on which version of SSMS you’re running. If you’re still using the SQL Server 2005 version, you’ll be greeted with a screen full of XML:

F11_02_xml

To see the plan rendered in a nice graphical format, save the XML to a file with the .SQLPLAN extension, close the file in Management Studio, then re-open it. Or, if you’re using the SQL Server 2008 version of Management Studio, forget all of the XML file stuff and just click. Either way you’ll be greeted with:

F11_02_graphic

The other option is to see the plan for the entire batch. Or, phrased another way, all of the plans for each of the statements in the batch. It’s as easy as...

EXEC sp_WhoIsActive
     @get_plans = 2

F11_02_full

Notes

  • Plan collection, like statement text collection, may time out due to a lock. (See the Homework section below.)
  • Both the plan and the statement text may be unavailable due to the presence of “non query” statements in the batch. Various statement types apply, and it’s not documented, but the most common problem I see is when a stored procedure uses ALTER TABLE to add a primary key to a temporary table. This disables caching for the entire stored procedure, making it impossible for Who is Active to retrieve either the text or the plan.
  • A few people have asked whether the plans returned by Who is Active are the “actual” or “estimated” plans. The answer depends on how you define these terms. "Actual plan" usually refers to the query plan that has the actual row counts, number of executions for each iterator, and various other information that can only be collected once a query has completed. Who is Active cannot return that version of the plan. But the plan it returns is indeed the "actual" plan that's running at the time; in other words, the plan will not be recompiled into some other plan by the time Who is Active can get it.

 

Homework

Making text collection time out is relatively simple. If a stored procedure is created inside of an explicit outer transaction, it will also be compiled within the transaction. Therefore, the cached plan will be bound to the transaction and will block the dynamic management functions until the transaction is completed. To see this in action, run the following batch and then run Who is Active in another window.

USE tempdb
GO

BEGIN TRAN
GO

CREATE PROC xyz
AS
BEGIN
    SELECT *
    FROM sys.tables
   
    WAITFOR DELAY '10:10:10'
END
GO

EXEC xyz
GO

Another thing that can cause query plan collection to go wrong is due to a limitation of SQL Server’s XML data type. Can you describe the problem? And can you write a query to make it happen? (Note, Who is Active will not actually fail or throw an exception in this case; the primary symptom will be a NULL in the [query_plan] column.)

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Alejandro Mesa said:

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;

GO

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;

GO

--

AMB

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:

LOL!!!

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:

Michael,

What version and service pack are you working with?

--

AMB

April 13, 2011 10:31 PM
 

Adam Machanic said:

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.

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:

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

April 13, 2011 10:55 PM
 

Michael Codanti said:

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.

April 15, 2011 12:19 AM
 

Michael Codanti said:

Adam,

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.

--Adam

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

ps.

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.

--Adam

November 11, 2011 10:31 AM

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement