Sql-server – How to fix below blocking problem on SQL server 2014

blockingperformancesql serversql server 2014

I am having a strange blocking problem which i am not able to understand.

During a slighter more volume due to some app pooling issues we see sleeping spids with open transactions, so those SP's tend to be lead blocker and blocks few delete statements.

Yes we are aware on sleeping SPID and working with app team to fix the issue, but i am not able to understand why on below

SP's which leave their session open say EXEC spleadblocker does not access any of the table underneath within its code i.e

it blocks a simple statement like below within an SP say spblocked

DELETE from dbo.table1 WHERE Col1=@col1
OPTION (QUERYTRACEON 9481)

spleadblocker does not have any thing to do with table "table1" and should not blocking therefore above statement

Waittype is LCK_M_X

and wait_resource is database.dbo.table1.PK_table1

table 1 has col 1 as PK /Clustered index created , which is of data
type bigint not null column

Why an SP with no correlation to this table will create blocking and is there a way i can avoid that PK scan from that delete statement to avoid being blocked?

UPDATE After more research below is what we found, say the very first SP which comes SP1leablocker for spid 66 and had its transaction open with status sleeping for about a min. This session block the delete statement above and yes they have table 1 in SP1leablocker code, so explains the blocking

Later, on above spid 66, multiple times we see different SP's running and creates a blocking chain for that delete statement of around 10 mins because that SPID 66 gets connection from various other SP's with status sleeping and open tran

Is there a way to avoid blocking on DELETE statement ?

Best Answer

As stated by a previous user the lock type indicates that your query tries to acquire an Exclusive lock on a resource. Is your predicate (Col1=@col1) supported by in index ?

Some options for a query referencing a specific table to acquire locks on other tables can be: triggers, fks and fk cascading.

More info is required. See this post for more ideas.

Have you tried to capture the blocked process report ? See this post for how to run a server side trace to capture the "Blocked Process Report Event" constantly in the background so that when your issue occurs you do not miss it and have the data needed for analysis

Related Question