Suspended/Update processess

Question

Hello,

We have a lot of processes stuck in Suspended/Update status..
Actually there lots of processes which are accessing same table and trying to update.. so we have accumulated lots 100s of processes in suspended/update status.

Also, i can see from sys.sysprocesses, each session has multiple session - one is suspended/update and all others are sleeping/AWAITING COMMAND

Please help how to get rid of it.

Answer 1

Those that are AWAITING COMMAND could be for instance open and idle query windows in SQL Server Management Studio. The one with cmd = UPDATE may be blocked by something. It may also be that the UPDATE statement takes a very long time, and needs to read a lot pages from disk.

With the very small amount of information you have shared, it is very difficult to say something with certainty. I can offer speculations, but they can be way off. What I do note, though, is that all processes come from the same client process. With only six rows, that could still be a single instance of SSMS, but it could also be a case of an application that does not close its connections properly.

You would need to give us more information so that we can give an accurate answer. One way would be to run my beta_lockinfo in archive mode and follow the instructions to bulk out the data and share the data with us. But beware that this may disclose table names and actual data, and this may not be appropriate to share.

Answer 2

Hi @Biltz ,

When the process is in suspended state, it's either waiting for I/O (reading or writing data, can be also tempdb related), or waiting for blocking to end.
for cmd=update, the text of the request can be retrieved by using sys.dm_exec_sql_text with the corresponding sql_handle for the request.
I/O can most often be improved by adding indexes(Of course from the information you gave, I don't know if your process needs to be indexed)

try to troubleshooting performance problems in your SQL Server, https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/cc966540(v=technet.10)?redirectedfrom=MSDN

sys.dm_os_waiting_tasks tell us where SQL Server is waiting at the moment.
sys.dm_exec_session_wait_stats tell us where a session is spending its waits

Sleeping / Awaiting Command usually because the SQL server is waiting for the next command from the client.

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.