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.

The Node Knows (A Month of Activity Monitoring, Part 20 of 30)

This post is part 20 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.


Query plans are packed with information about what’s going to happen, or what has happened. But they’re markedly quiet about what is happening.

This is probably best illustrated with an example. Consider: you’re sitting there at your desk one morning, enjoying a nice hot cup of coffee, when the phone rings. It’s Steve, the harried middle manager who sits on the 2nd floor. “Help!” Steve shouts into the phone, making you flinch and immediately move the headset away from your ear. “My report has been running for 10 minutes! I have a meeting in three minutes! Is it going to finish?”

You calmly open Management Studio, fire up Who is Active, and glance at the various columns. No blocking. No extreme waits. Nothing too interesting in the plan. Just a big query chugging along.

At this point there’s really not much you can do. But there is one hint that might help give you some more insight. Who is Active collects, along with CXPACKET waits, a node identifier. These are displayed along with the wait when @get_task_info = 2 is used. This can help you figure out approximately what your plan is up to right now.

Too see this in action, first create a blocking situation so that we have something to look at:

USE AdventureWorks
GO

BEGIN TRAN
GO

UPDATE TOP(10) Sales.SalesOrderDetail
SET
    OrderQty += 7
GO

Next, in a new window, fire up the following query:

USE AdventureWorks
GO

SELECT
    *
FROM
(
    SELECT
        sh.*,
        sd.ProductId,
        ROW_NUMBER() OVER
        (
            PARTITION BY sd.SalesOrderDetailId
            ORDER BY sd.ProductId
        ) AS r
    FROM
    (
        SELECT TOP(1000)
            *
        FROM Sales.SalesOrderDetail
        ORDER BY
            SalesOrderDetailId DESC
    ) AS sd
    INNER JOIN Sales.SalesOrderHeader AS sh ON
        sh.SalesOrderId = sd.SalesOrderId
) AS s
WHERE
    s.r = 1
GO

Assuming that you have a multicore machine, the plan for this query will have a few different parallel sections. Which is what we need for CXPACKET waits. Let’s see what Who is Active has to say about the situation:

EXEC sp_WhoIsActive
    @get_task_info = 2,
    @get_plans = 1

F20_01_CXPACKETs

The query has 10 tasks. One of them waiting on a lock; we’ll ignore that one since we’ve created the lock to “pause” things in this case. The other nine are all waiting on CXPACKET waits—but there are two different groups. The first group consists of eight tasks, all waiting on node 17. The second group has only a single task, and it’s waiting on node 0.

So what are these nodes? Bring up the plan, hover over any of the iterators therein, and you’ll see:

F20_02_node_id

Every iterator in the plan has an associated node identifier. These range from 0—for the leftmost node in the plan—on up, as you move to the right. CXPACKET waits are associated with exchange (parallelism) iterators, and the wait information includes the actual node identifier from the plan. By using this information you can begin to understand the flow of data through your larger plans, as task waits move from node to node.

This is certainly not a perfect solution, and chances are very good that you won’t be able to give Steve from the 2nd floor the exact amount of time remaining. But every bit of information helps, and in this case you may be able to come up with a reasonable estimate. Even if he ends up running to the meeting with only half of the report printed.

 

Homework

Yesterday’s task was to cause an error to occur in Who is Active’s blocked object resolution mode. Easily enough accomplished by creating a permission issue:

USE master
GO

CREATE LOGIN active_error
WITH PASSWORD = 'abcd1234!!!!'
GO

GRANT VIEW SERVER STATE
TO active_error
GO

EXECUTE AS LOGIN='active_error'
GO

EXEC sp_WhoIsActive
    @get_task_info = 2,
    @get_additional_info = 1
GO

Running this will cause a new node to appear inside of <block_info> in the [additional_info] column:

<query_error>The server principal "active_error" is not able to access the database "AdventureWorks" under the current security context.</query_error>

Today we'll take a break from the homework to celebrate the middle of the work week and two-thirds of this thirty-part series behind us.

Published Wednesday, April 20, 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

 

Michael Codanti said:

Adam,

Thanks for Who Is Active, and the documentation you are writing, I am not only learning more about Who Is Active but SQL Server as well. (Both of which are good things.)

April 20, 2011 11:37 PM
 

spe109 said:

Hi Adam,

I'd just like to echo what Michael said in a previous post. Thanks for all your hard work both in writing the tool and writing this series of posts.

Thanks

Paul.

April 21, 2011 4:24 AM
 

Adam Machanic said:

My pleasure :-)

April 21, 2011 10:15 AM
 

Meher said:

Hi Adam

I am using SQL Server 2005 SP3 and I got the following error message when I ran the following in a original query window:

EXEC sp_WhoIsActive

   @get_task_info = 2,

   @get_plans = 1

I ran the update statement with a begin tran in the first(original) query window and in a second query window ran the Select Statement with the Row_number clause in it. Then back in the original query window I executed the sp_whoISActive.

Msg 3930, Level 16, State 1, Procedure sp_WhoIsActive, Line 3614

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Warning: Null value is eliminated by an aggregate or other SET operation.

Msg 3998, Level 16, State 1, Line 1

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Is it because I did something wrong?

Thanks

Meher

April 21, 2011 5:01 PM
 

Adam Machanic said:

Hi Meher,

Your transaction was killed due to an exception -- probably a timeout on getting a query plan. I don't recommend running Who is Active in the middle of a transaction; it's not designed for that.

Best,

Adam

April 21, 2011 7:37 PM
 

Meher said:

Hi Adam,

Thanks. That explains it why I got the error.

Regards

Meher

April 21, 2011 10:03 PM
 

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

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