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.

How Queries Are Processed (A Month of Activity Monitoring, Part 13 of 30)

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


Today and tomorrow we’ll take a break from Who is Active to get into some important background information on how things work internally in the query processor. The DMVs—and, therefore, Who is Active—reveal fairly deep information about what’s going on with your query, but unless you understand the basics it’s going to be difficult to properly interpret that data.

Most of the time when people teach this subject, they start off with an image like the following:

F13_01_boring

That’s all well and good, except that it’s a bit on the dull side.

A nicer approach, in my opinion, is to teach this topic using a metaphor. And others seem to agree: Joe Sack uses a grocery store checkout line, Rob Farley created a metaphor involving airport check-in lines, and Robert Davis has recommended something to do with running out of toilet paper while using a public bathroom stall. (I didn’t ask.)

 

My metaphor is a diner. The Resource Diner. It’s open 24 hours a day, 7 days a week (99.999% of the time, when the staff is doing its job properly), and it’s where workers go when they’re hungry. And workers are always hungry. But I’m getting ahead of myself a bit.

F13_01_Restaurant

It all begins with a query. You send it to SQL Server, and it gets parsed, and bound, and optimized, and the output is a plan. A plan is a program for the query processor to run. The query processor needs to run this program on physical CPUs, using physical memory, and perhaps physical disk drives. But it doesn’t talk to these various physical components directly; rather, it uses abstractions. Most of these abstractions are provided by SQLOS, some by the storage engine, and still others by the Windows kernel itself. One of the main abstractions is that over operating system threads. The abstracted version is called a worker, and the worker executes tasks, which are essentially segments of the plan. A serial plan will have one task, and a parallel plan may have many. (See my SQL University series for more information.)

While executing a task, the worker can really do only two things: consume CPU time, and wait for some resource that it needs to use in order to consume CPU time.

And that is why workers are always hungry.

When a worker gets hungry, it visits the restaurant. It already knows what it wants to eat, so it doesn’t bother with the menu. It goes straight to the cashier and makes its request.

F13_02_cashier

“I’ll have a page lock with extra schema stability on the side.”

Sometimes the resource is already available, sitting there warming under the heat lamps. The worker can take it and get right back to consuming CPU time.

F13_03_HeatLamp

But sometimes the resource isn’t ready yet. Perhaps an exclusive lock on the resource is being held by someone else, or perhaps the page needs to be read off the disk, or perhaps some memory needs to be granted.

F13_04_empty

When a worker needs a resource, it can’t go off and do something else in the meantime. It wants to consume the resource it needs. So the “order” is sent off to be prepared, and meanwhile the worker has to wait.

F13_05_cooking_waiting

Eventually, the worker is called over the loudspeaker. “Order up! One page lock, extra schema stability on the side!” (This is called being signaled.) The worker can now get its resource from the cashier! But lo and behold, other workers have come into the restaurant while our worker was waiting. They’re getting orders from the cashier. So our worker will have to wait some more. This is known as being in the runnable queue.

F13_06_cashier

After a long and arduous wait, our heroic worker finally gets its resource and can chow down. Ahh, life is good.

F13_07_yum

Now that the metaphor has been laid out, let’s take another look at the oh-so-dull technical image from above:

F13_01_boring

Here’s what’s going on:

  • Time 0 is when the worker gets hungry. It needs a resource. Let’s say that it’s a certain page.
    • If it’s already available (e.g. the page is in the buffer cache), there is no wait. The worker can immediately continue its work.
    • If the page isn’t in the cache, it needs to be read off disk. The worker stops working, gets off of its scheduler, and starts waiting.
  • Time 1 is when the food is ready. The page has now been read off of the disk. The worker is ready to get back on the scheduler and continue its work, but some other process may have started working on the scheduler in the meantime. And there may be a line of other workers, all waiting for the scheduler. If there is a line, the worker has to wait yet again—but this isn’t a resource wait anymore. It’s now a signal wait.
  • Time 2 is when the worker finally gets back to the cashier. It takes its resource and gets to work again on the scheduler.

The important intervals here are between Time 0 and Time 1 (resource wait time) and between Time 1 and Time 2 (signal wait time). The resource wait time is impacted by however long it takes to read the page off of the disk, grant the memory, fulfill the lock request, or whatever. The signal wait time is almost entirely impacted by how busy the scheduler happens to be.

 

Homework

Yesterday I asked you to generate a lot of tempdb allocations using a read-only query. This is a fairly simple thing to do: simply create a query that needs to do a sort or hash, and make sure it has a bad estimate. This will force it to spill. Here’s one example, that uses hints to force bad index selection and an extremely suboptimal memory grant:

DECLARE
    @i INT = 1,
    @j INT = 2000000000

SELECT
    *    
FROM Production.Product
CROSS JOIN Production.TransactionHistory WITH (INDEX (2))
WHERE
    TransactionId BETWEEN @i AND @j
ORDER BY
    TransactionId
OPTION (FORCE ORDER, OPTIMIZE FOR (@j = 1))

I plan to get a bit deeper into memory grants later this month. Stay tuned for more on that topic.

Today we’ll celebrate the middle of the work week by taking a break from the homework.

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

 

Gil said:

Adam, I really appreciate both the sp_whoisactive tool, and these terrificly creative and informative posts about it and related background.

I'm sure there are many people who are getting a lot out of them, but haven't bothered to mention it to you.

Keep up the great work!

April 13, 2011 4:16 PM
 

Phil Nolan said:

I'd second that!  Great work Adam, keep it up :)

April 14, 2011 4:33 AM
 

Adam Machanic said:

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

April 14, 2011 9:52 PM
 

Adam Machanic said:

Thank you both for the kind words!

April 15, 2011 10:47 AM
 

Adam Machanic said:

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

April 20, 2011 10:35 AM
 

Erin Stellato said:

I love this analogy.  Loving the series (a little behind, catching up :)

April 26, 2011 7:48 PM
 

Armando Prato said:

As usual, great stuff Adam.  

April 28, 2011 9:22 AM
 

Adam Machanic said:

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

April 28, 2011 9: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
 

Juan Ruiz said:

I love these posts!!!, de lo mejor que he visto sobre SQL Server.

Gracias Adam!, god bless you!

September 23, 2011 3:19 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