suspended sessions through application.

Question

hello,
We have an application that is developed in MS Access. I have seen instances where even though user has opened the application but not doing any work, it will still show me a "suspended" session at the database level and these sessions will remain like that for a long time like 2-3 hours. When i checked with users they are not doing any activity. my question is do these suspended sessions consume resources like IO/CPU/memory? other thing is why is sp_who2 'active' show me these sessions when they are suspended for a long time and they are not actually active. How do i eliminate them?

Answer 1

Hi @SQLServerBro ,

my question is do these suspended sessions consume resources like IO/CPU/memory?

"suspended" means that this process is "waiting" for other processes to execute. The waiting system resource may be Disk I/O or the lock of the database.
In other words, this problem depends on other processes.

This article may help you have a better understanding.

How do i eliminate them?

If the cause is a lock, it will show up in BlkBy.
Then you can kill it.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The wait type ASYNCH_NETWORK_IO means that SQL Server is waiting for the client to consume the data. Normally when you see this it can be a slow network connection, or a client that performs a lot of processing for every row.

In this case, it seems that it is even worse - the clients have gone to sleep in the middle of the result set. I know that there used to be an issue with Access that if there are very many objects from a query that populates a dropdown, Access will only read about 400 rows initially, and only read the rest if the user moves that far. And in the meanwhile, SQL Server is kept waiting. I have not heard of this issue for a long time, so maybe it has been fixed. But the symptoms you describe match this very well.

As you may guess, this is not a fantastic situation. Although, it depends on the situation how bad it is. If the data is read from permanent tables, there can be locks held which can block other processes. But if the data comes from temp tables or you have enabled READ_COMMITTED_SNAPSHOT, locks should not be a problem. Nor for that matter if the queries are non-streaming.

CPU? Nah, they're stalled. But, memory, yes, those output buffers are consuming some memory, although I don't know exactly how many.

I would certainly look into getting rid of these guys. But that's a process that involves getting the Access people to clean up their act. I don't know how politically hot that can be for you.

Answer 3

Hi @SQLServerBro,

This wait type(ASYNC_NETWORK_IO) indicate that the SQL Server has result sets to send to the application but the application does not process them. This may indicate a slow network connection. But more often the problem is with the application code, it is either blocking while processing the result set or is requesting a huge result set.
About Reducing SQL Server ASYNC_NETWORK_IO wait type, please see:
https://www.sqlshack.com/reducing-sql-server-async_network_io-wait-type/

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.