Blocking issue

SQL Server Administration
1

Hi all,

Hope all are staying safe!,

we have a 3rd partly application that is running on SQL Server 2014 standard edition, the application is getting hanged every ones in a while because of blocking issues.I did some investigation and found there are 2 stored procedures that are conflicting

  1. a process that excutes a delete stored procedure which removes a record in a table(TableA) is being blocked
    by another process that is trying to insert into the same table(TableA),there is no complexity in both stored procedures,
    the table in question is a very small table with about 300 records, i am puzzled on seeing a blocking issue with such a small table.
    The table doesn't have a CLUSTERED INDEX it has a non-clustered index on an primary key(which is identity column),
    also the delete storedprocedure where clause have columns are not indexed, and hence the execution plan shows table scan(i think this fine given the table size)

i used the sp_whoisactive to capture the blocking information and locks xml info

for the insert procedure

for the delete sp

i see that the blocking is happening because the insert statement is having a"IX" lock on the table,and is blocking the delete, i see that the insert statement some time takes more than 25 secs(not sure why it does), which i think is blocking the delete process and then it clears after a while, but by this time the application hangs and the only way to clear this to restart the application processes

from the above insert stored procedure xml there is a "IX" lock on the table which is causing the blocking on the delete , but am not sure why it takes 10 to 25 secs to just insert a record, any help greatly appreciated, the DB server has other applications running also and don't see any resource contention(as per my understanding)

Thanks

Vijay

2

Cluster the table on the primary key. That would be the first thing to try.

3

are there any triggers on the table? Cascade Delete on FKs?

4

Thanks for the information, the table does not have any triggers, its the insert i think is causing the problem, and its taking a table lock on the object because of its size being small, is there something i can check to see why the insert takes about 25 secs sometimes
Thanks

5

That's due to blocking. If you give SQL a unique clustering key, it can do the UPDATEs somewhat faster, which will shorten the lock time for the INSERTs and might even get rid of it.

Worse case you might have to add a lot of freespace (that is, reduce the FILLFACTOR a lot) to force the table to take more than one page. Data mods, UPDATE or INSERT, require an exclusive lock on the page, and with there being only a very pages in the table, that can cause a lot of blocking.

6

Thank you Scott!,i will try that