One of our customers has a very high performance server with SQL Server 2012, capable of processing large amounts of data in a very short time. Unfortunately, the network does not seem to be as efficient. As a client, users can set filters and query a view using LLBLGen (ORMapper). The view has many fields but is a simple set of INNER JOIN. Over long periods it returns large amounts of data. Client side the operator can also manually cancel the query in progress. Everything works properly under normal conditions.
The particular problem is when a query is in progress from the client and, manually, the operator decides to interrupt it: in these conditions the SQL process remains alive and blocks the tables until the client application is closed. In detail the condition is that:
- The query goes into "async_network_io" within a few seconds and stays there for tens of seconds. In other words, processing is much faster than transferring information.
- If the query is cancelled BEFORE it goes into the "async_network_io" state, the deletion is successful.
- If the query is cancelled DURING the "async_network_io" state, the process seems to go to block.
We are able to systematically re-create it, but we have no idea how to intervene and whether it is a SQL, network or LLBLGen problem. We also tried to update the project's .NET Framework (the original was with .NET 2.0), but the result seems the same.
Any advice on this?