I have an ASP.NET Core application, using Entity Framework Core, I am accessing my SQL Server database.

All of the sudden, in the last couple of days, an issue arose where there is a specific table that is causing blocking queries.

My application processes orders, every order needs to have a specific number, which has to be unique per day.

Every time an order is placed, I add an entity to the table if no entity can be found for the specified date and if an entity can be found I update the order number.

Entity: OrderAtDay

public int CurrentAmountOfOrders { get; set; }
public DateTime Date { get; set; }

The blocking query occurs sometimes when I am checking if an existing entity exists, and sometimes it occurs when trying to update the entity with the increased number (when SaveChangesAsync is called).

When this issue occurs, there is no other option than to kill the blocking query with the following SQL queries:

SELECT 
    blocking_session_id AS BlockerSession, 
    session_id AS BlockedSession 
FROM 
    sys.dm_exec_requests 
WHERE 
    blocking_session_id <> 0;

If the previous query would return “57” for example as the blocking_session_id, I would then kill it with the following query (then sometimes there is multiple times the data can be accessed, before being blocked suddenly again):

Kill 57

Things I have tried:

• Resource Management: Making sure the server has plenty of resources
• Enabled READ_COMMITTED_SNAPSHOT
• Clear the entire plan cache using DBCC FREEPROCCACHE;
• Update statistics: EXEC sp_updatestats
• New SQL Server instance
• Clear the problematic table completely
• Drop and re-create the problematic table

Has anyone else ever faced this issue, or are there any ways to prevent / work around this behaviour?

I have multiple copies of the same application running for different users (+- 35 times) and no other users are facing this issue.

I think it is pretty strange that only this particular table in this particular database is facing this issue. Any help is much appreciated