This situation was caused by SQL Server processing a big hit from a particular user. In the table that was related to the big hit, if another user tried to access the same table during a specific timeframe, that would make it not able to respond accordingly because of a lock or something related from the other hit, this would cause the hit in progress to not be able to respond. Then, IIS would time it out at 5 minutes. If during that timeframe, the user tries to click several times, all those similar hits would then be released at the same time, as soon as SQL Server unlocks the resources from the initial hit that triggered that specific command.

So, basically, depending on the hits on the Web site, for those that requires a specific table, if that table is being used heavily from another hit, for as long as SQL Server has not completed the related query, upcoming hits that need that specific table might fall into that situation.

For some kind of reason, the SQL Client Data Provider from .NET, during the IIS hits in progress of those not being able to be processed, would simply release all the queue with a timeout message.

I simulated the situation exactly like that. I started an update command on 4 million records. This usually runs for 30 minutes. Some hits on the Web site were not able to be processed. As soon as I click on Cancel from SSMS, all the hits in progress obtained the timeout error.

So, how do we resolve that issue? On the Web site, the same situation is not about someone updating 4 million records but about one query that would fall into a non optimized approach I would say, causing interferences with other hits. This is the situation I talked about recently as to know why SQL Server would react like that, especially considering some very powerful hardware we have. I also have the NOLOCK clause at every SQL select command.