4

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 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
1
  • 1
    It should be noted that it may not be just the client program not consuming the dataset fast enough, this is also caused by bottlenecks in the end-to-end network connection as well. IOW, if you have a connection bandwidth of 100Mb(its) and a dataset returned that is 80MB(ytes) then you can be sure that it is going to take at least 10 seconds of transmission time alone. (However, with asynchronous streaming, not all of that time may be in ASYNCH_NETWORK_IO state, as the server's time/states are usually charged first). Feb 3, 2023 at 12:50

2 Answers 2

13

Does such a query (that is suspended due to an ongoing ASYNC_NETWORK_IO) cause blocking on this table?

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.

0
3

Your query is running and once in a while it will go into a "suspended" state with the wait type ASYNC_NETWORK_IO.

If your query is a SELECT, then yes it can block other writers i.e. people wanting to modify the table. This answer assumes that you are not using read commited snapshot or snapshot isolation levels and you are operation under pessimistic isolation levels.

2
  • So after a while it will go from suspended to running or is it done running?
    – variable
    Feb 2, 2023 at 9:29
  • @variable: Yes for most wait types it will be like this. But e.g. LCK_* waits you will wait until the lock is released. That is you can wait for many hours without running or making progress.
    – xhr489
    Feb 2, 2023 at 9:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.