We have been using SQL Server 2014 Enterprise Edition for one of our applications and have been experiencing severe blocking the past few days. This application is supported by a vendor and hence we don't know much about the details of the product and its code.
For a work-around, I suggested them to enable Read Committed Snapshot on the database, which would reduce the blocking as readers won't block writers and vice versa. However, some blocking is still occurring and they are not clearing themselves.
This blocking is involving one sequence object, which generates unique numbers and is allocated to every session for maintaining a unique number. The moment I kill that spid, everything starts working normally and the blocks get cleared in few seconds.
I couldn't find any posts about sequence objects and blocking on any forums.
There are two types of locks held - one is
LCK_M_X and other one is
LCK_M_U. Basically there are two tables, which is updated and selected at the same time; which seems to be causing the DB blocking however query with longest time, which is displayed in result set (
sp_whoisactive) is the query responsible to fetch the next sequence number. Blocking happens only when next seq and update runs simultaneously.
It's not using a table, they have used a
SEQUENCE object only to maintain unique incremental number with cache value of 25. This seems to be flaw with application architecture only however I was trying to provide some solution in my environment to avoid DB blocking. Not sure, if anything else can be done other than enabling read committed snapshot (without changing code).
SEQUENCE, the blocking is on the tables rather than the