SQL DB Performance: most of the querys comes in suspended mode

Question

HI
When i checked the activity monitor, most of the querys comes in suspended mode. how can i over come this situation, what are the possible reasons behind this.

thanks and regards

Antony maxwin

Answer 1

yes, i am actually experiancing this problem. most the time users get difficulty to get result.

OK, so you have a performance problem, and that is not really surprising given the screenshot you posted.

Seems like you need to do some query tuning. If you are on SQL 2016 or later, enable Query Store for the database. Then after a day (or at the end of the day, if you enabled it in the morning), find the database in Object Explorer, expand it, and you will find a Query Store node. Open Top Resource Consuming Queries. You will find a wealth of information there.

If you want to shoot from the hip: in the screenshot you posted, I quite much expect that the query with all those CXPACKET waits will be in the Top 5. As for the two blocked processes, they are not blocked directly by the process with the CXPACKET waits, but I would not be surprised that they are waiting for other processes that in their turn are waiting for this parallel guy. In any case, if you want to get a head start, you could start with that parallel query, presuming that you captured it at the time.

Answer 2

Hi @Antony Maxwin ,

The threads with CXPACKET waits are actually done with whatever work they had to do and are now waiting for other active threads to complete. Generally speaking, the CXPACKET wait type is normally for SQL Server and it is an indicator that SQL Server is using a parallel plan in executing a query
To reduce CXPACKET waits, you can set cost threshold for parallelism according to this doc

About LCK_M_U is when a thread is waiting to acquire an update lock on a resource and LCK_M_IX is when a thread is waiting to acquire an Intent Exclusive lock on a resource, there is at least one other lock in an incompatible mode granted on the resource to a different thread

Collecting data from sp_WhoIsActive in a Table is a good technique for tracking down blocking and locking issues. For more details, the sys.dm_tran_locks view for this kind of thing

https://stackoverflow.com/questions/694581/how-to-check-which-locks-are-held-on-a-table

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

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.

Answer 3

It is completely normal to have "Suspended" processes, and not an indication of a problem. The server can only do so many things at the same time, and time slices its processing among all the processes.

Are you having an actual problem?