patternsqlModerate
Does a query that is suspended due to an ongoing ASYNC_NETWORK_IO cause blocking?
Viewed 0 times
duequeryasync_network_ioblockingthatdoescauseongoingsuspended
Problem
I am looking at the dm_exec_sessions and requests DMVs on a production server. There are some queries with status 'suspended', a duration of 12 minutes, and a wait type of
I understand this is because the client application isn't quick enough to fetch the data (or the result is so large that the client program is taking time to consume it).
Does such a query (that is suspended due to an ongoing
The query is a
For example: Does
The locks held by the query are as follows:
ASYNC_NETWORK_IO.I understand this is because the client application isn't quick enough to fetch the data (or the result is so large that the client program is taking time to consume it).
Does such a query (that is suspended due to an ongoing
ASYNC_NETWORK_IO) cause blocking on this table?The query is a
SELECT and I cannot see this query blocking any other query. Hence my question to ask is whether it can potentially block anything.For example: Does
ASYNC_NETWORK_IO mean that execution is ongoing or execution has finished and the client app is pulling the data? In the latter case, I don't see the reason why this query could potentially block other queries given that it has already produced its result.The locks held by the query are as follows:
- Page lock request mode
S
- Object lock request mode
IS
Solution
Does such a query (that is suspended due to an ongoing ASYNC_NETWORK_IO) cause blocking on this table?
It might or might not.
For example: does ASYNC_NETWORK_IO mean that execution is ongoing or execution has finishes and the client app is pulling the data?
You say the session running the query is "suspended" not "sleeping". A suspended query has not finished, by definition. If it were finished, it would have a status of "done" (very briefly) or "sleeping".
In the later case I don't see reason why this query could potentially block other queries given that it has already produced its result.
Well, the session is holding locks. Those locks would block any other session needing an incompatible lock. In other words, the suspended session could block someone else, it just isn't at the moment.
The important point is that it is the locks that would block, not the
You might ask at this point why the session is holding locks.
You might have a mental model where SQL Server produces a result, stores it somewhere, then ends the query and waits for the client to consume the result. That's not (generally) how it works. It would be inefficient and impractical to always store arbitrarily large results on the server.
Instead, SQL Server streams result rows to the client as they become available from the execution plan, which you can think of as a 'pipeline' of data. When the client stops receiving rows, the pipeline stops, and execution is suspended until the client starts asking for rows again.
If the query was holding locks when the session was suspended, these locks continue to be held as long as the suspended status continues.
Side notes:
There is a small amount of buffering between the server and client (a few network packets worth) but this isn't normally sufficient to hold the entire result and the query doesn't complete until the buffers have been emptied by the client reading rows anyway.
A query won't always hold locks when suspended. It depends on what the execution plan looks like and what it was doing when SQL Server tried to put the next result row in the buffer and found no room.
For example, some plans might have a final sort that will indeed consume all result rows in order to sort them. Data access has finished by the time the sort is ready to produce output, so there often won't be any need to hold locks (depending on isolation level etc.)
For more on how execution plans stream rows one at a time, see my article Iterators, Query Plans, and Why They Run Backwards.
It might or might not.
ASYNC_NETWORK_IO itself isn't capable of blocking anyone else (ok, it can if your system runs out of worker threads, but that's an edge case). Read on.For example: does ASYNC_NETWORK_IO mean that execution is ongoing or execution has finishes and the client app is pulling the data?
You say the session running the query is "suspended" not "sleeping". A suspended query has not finished, by definition. If it were finished, it would have a status of "done" (very briefly) or "sleeping".
In the later case I don't see reason why this query could potentially block other queries given that it has already produced its result.
Well, the session is holding locks. Those locks would block any other session needing an incompatible lock. In other words, the suspended session could block someone else, it just isn't at the moment.
The important point is that it is the locks that would block, not the
ASYNC_NETWORK_IO status directly.You might ask at this point why the session is holding locks.
You might have a mental model where SQL Server produces a result, stores it somewhere, then ends the query and waits for the client to consume the result. That's not (generally) how it works. It would be inefficient and impractical to always store arbitrarily large results on the server.
Instead, SQL Server streams result rows to the client as they become available from the execution plan, which you can think of as a 'pipeline' of data. When the client stops receiving rows, the pipeline stops, and execution is suspended until the client starts asking for rows again.
If the query was holding locks when the session was suspended, these locks continue to be held as long as the suspended status continues.
Side notes:
There is a small amount of buffering between the server and client (a few network packets worth) but this isn't normally sufficient to hold the entire result and the query doesn't complete until the buffers have been emptied by the client reading rows anyway.
A query won't always hold locks when suspended. It depends on what the execution plan looks like and what it was doing when SQL Server tried to put the next result row in the buffer and found no room.
For example, some plans might have a final sort that will indeed consume all result rows in order to sort them. Data access has finished by the time the sort is ready to produce output, so there often won't be any need to hold locks (depending on isolation level etc.)
For more on how execution plans stream rows one at a time, see my article Iterators, Query Plans, and Why They Run Backwards.
Context
StackExchange Database Administrators Q#322920, answer score: 13
Revisions (0)
No revisions yet.