Sudden increase in blocked processes
Our production system got stuk on a sudden increase of blocked processes. The blocked object was a stored procedure:
- lock type = LCK_M_X
- Resource = OBJECT:17:2009162303:0 [COMPILE]
- command = EXECUTE
- status = suspended, blocked
The number of blocked processes went up to 300.
The procedure is called about 7 times a second, but we have other procedures that are called 70 times a second.
I checked the internet on this subject, but there was not much to find. I checked the SQL Profiler and saw no CacheMisses for this stored procedure, so it is not that this procedure is normally recompiled each time. The user that executes the stored procedure is a dbo, and the stored procedure is owned by the dbo.
I expect that there was a single recompilation, and that other processes were blocked by this. After the recompilation each queued procedure call was trying to get access to it with a LCK_M_X, causing other still to be blocked. Then there were too many queued processes for the SQL Server to recover from the recompilation.
My question is, how can I find the cause of this, and how can I prevent this. And what is the LCK_M_X lock, is this done for before each procedure call? Or only for recompilations?
- lock type = LCK_M_X
- Resource = OBJECT:17:2009162303:0 [COMPILE]
- command = EXECUTE
- status = suspended, blocked
The number of blocked processes went up to 300.
The procedure is called about 7 times a second, but we have other procedures that are called 70 times a second.
I checked the internet on this subject, but there was not much to find. I checked the SQL Profiler and saw no CacheMisses for this stored procedure, so it is not that this procedure is normally recompiled each time. The user that executes the stored procedure is a dbo, and the stored procedure is owned by the dbo.
I expect that there was a single recompilation, and that other processes were blocked by this. After the recompilation each queued procedure call was trying to get access to it with a LCK_M_X, causing other still to be blocked. Then there were too many queued processes for the SQL Server to recover from the recompilation.
My question is, how can I find the cause of this, and how can I prevent this. And what is the LCK_M_X lock, is this done for before each procedure call? Or only for recompilations?
ASKER
The problem occured on a Production Environment, so we decided to restart the server when this happended. We cannot reproduce the situation and have a look with the profiler. We could only wait for the next time this happens. I could only check the profiles during a normal situation, and saw no CacheMisses. Does a execution for a stored procedure requires a LCK_M_X each time?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
LCK_M_X indicates a wait for an exclusive lock.