Blocking query

Question

Hi All, Seeing blocking on production server. There is one particular sql statement which is causing the blocking all the time. Please guide and on how to resolve or minimize the blocking for this statement. I always see this as a head blocker. These sql statement is executed quite a lot.

Why it is holding exclusive lock for long time? what could be the reasons? I used another query to check the wait info of 1284 in sysprocesses. I see below. any thoughts? At db level maxdop is set as 4. cost threshold for parallelism =5

Can anyone explain what this mean?

Captured locking info using sp_whoisactive for all spids involved in blocking. Attached a zip file. locks of 817.txt Regards, Sam

Answer 1

Based on the information provided, it seems like you are experiencing blocking due to a particular SQL statement that is executed frequently. There could be various reasons why this statement is holding an exclusive lock for a long time. Here are a few possible reasons: nullnullnull

To minimize the blocking for this statement, here are a few things you can try: nullnullnull

Regarding the wait info of 1284 in sysprocesses, this means that the process is waiting for a lock to be released. The number 1284 indicates the wait type (in this case, it is a LCK_M_X wait type, which means an exclusive lock is being held). It is also important to note that the maxdop setting of 4 and the cost threshold for parallelism of 5 are database level settings that control how parallelism is used in query processing. However, they may not necessarily be directly related to the blocking issue you are experiencing. Finally, reviewing the locking information using sp_whoisactive can help identify the source of the blocking and can guide you in optimizing the SQL statement or modifying the database configuration to reduce the blocking. I hope this helps!

Answer 2

The running query is inserting data into a temp table from a SELECT query. Most of the tables in the query has a NOLOCK hint, which suggests that the programmer doesn't care if the result is correct or not. Very, very bad. But at least it is not blocking other queries.

But supposedly the process is running a multi-statement transaction, and the locks held were taken by previous operations in the transaction.

Can anyone explain what this mean?

The image depicts locks on the table LEAD_MATCH_REQUEST_BATCH. There are two key locks, that is two locked rows. There is also an intent lock on page level, which is normal. (An intent lock is always on page and table and signals that the process holds a real lock on lower level.) There is no intent lock on table level, but there is an exclusive lock. I don't use sp_WhoIsActiveMySelf, so I'm uncertain if these are locks all held by one process, or by many. Whatever, it looks a little funny. There is also a Sch-S, schema-stability lock which I would assume comes from one of these NOLOCK queries.

In any case, if that query in 1284-query-HEAD-BLOCKER.txt often figures in these situation, there is all reason to take a closer look at the query plan and tune it.

As I mentioned, I don't use sp_WhoIsActive myself. The reason for this is that I have my own monitoring procedure beta_lockinfo. Adam's procedure is very popular, and much more widely used than mine. I find the output from beta_lockinfo to be easier to digest and understand, but I am obviously partial. And it is a matter of habit. Nevertheless, you may want to try it, you find it on https://www.sommarskog.se/sqlutil/beta_lockinfo.html. It has a mode where you can export data, but I believe sp_WhoIsActive has this as well.