StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>SELECT statements may block another SELECT statement. You're probably thinking that since both acquire only S locks, they should never block. But blocking occurs on various types of resources, not only locks. Typical example is memory constraints. I'll try to digg up a recent answer to a question here that had attached a deadlock graph that showed to SELECT statements, one waiting for the other for parallel exchange operator memory resources (buffers).</p> <p><strong>Updated</strong> Here is the link with deadlock info I talked about: <a href="https://stackoverflow.com/questions/2945135/i-have-data-about-deadlocks-but-i-cant-understand-why-they-occur">I have data about deadlocks, but I can't understand why they occur</a> If you study the deadlock graph, you'll notice the following resource in the wait list:</p> <pre><code><exchangeEvent id="Pipe894b0680" WaitType="e_waitPipeGetRow" nodeId="0"> <owner-list> <owner id="process824df048"/> </owner-list> <waiter-list> <waiter id="process86ce0988"/> </waiter-list> </exchangeEvent> </code></pre> <p>This is not a lock, is a 'e_waitPipeGetRow' resource, is owned by a SELECT and another SELECT is waiting for it. Some discussion about 'intra-query parallel resources' can be found here: <a href="http://blogs.msdn.com/b/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx" rel="nofollow noreferrer">Today's Annoyingly-Unwieldy Term: "Intra-Query Parallel Thread Deadlocks"</a>. While most discussions are going to focus on deadlock issues, that doesn't mean that ordinary blocking cannot occur on these resources. <a href="http://msdn.microsoft.com/en-us/library/ms177648.aspx" rel="nofollow noreferrer"><code>sys.dm_exec_requests</code></a> will have the proper info in <code>wait_type</code> and <code>wait_resource</code>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload