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

Antony Maxwin 276 Reputation points
2021-12-07T05:59:08.18+00:00

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.

155582-image.png

thanks and regards

Antony maxwin

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,265 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 98,911 Reputation points
    2021-12-08T22:12:25.687+00:00

    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.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. YufeiShao-msft 7,036 Reputation points
    2021-12-07T06:38:32.23+00:00

    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.


  2. Tom Phillips 17,711 Reputation points
    2021-12-07T13:11:12.51+00:00

    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?