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
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
wait_resource is database.dbo.table1.PK_table1
table 1 has PK as Clustered index created on col1 which is PK 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?