It all depends on how you are executing your queries really;
Imagine the following query:
SqlConnection myConnection = new SqlConnection("connection_string");
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandTimeout = some_long_time;
myCommand.CommandText = "database_killing_procedure_lol";
myConnection.Open() //Connection's now open
myCommand.ExecuteNonQuery();
Two things will happen; one is this method will queue until the command.ExecuteNonQuery() finishes. The second is that we will also tie up a connection from the connection pool for the duration of the method.
What happens if we timeout? Well, an exception is thrown - a SqlException with a Number property = -2. However, remember, in the code above, there is no exception management so all that will happen is the objects will go out of scope and we'll need to wait for them to be disposed. In particular, our connection wont be reusable until this happens.
It's one of the reasons why the following pattern is preferred:
using(SqlConnection myConnection = new SqlConnection("connection_string"))
{
using(SqlCommand myCommand = new SqlCommand())
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandTimeout = some_long_time;
myCommand.CommandText = "database_killing_procedure_lol";
myConnection.Open() //Connection's now open
myCommand.ExecuteNonQuery();
}
}
It means that, as soon as the query is finished, either naturally (it runs to completion) or through an exception (timeout or otherwise), the resources are given back immediately.
In your specific issue, having a large number of queries that take a long time to execute is bad for many reasons. In a web application, you potentially have many users contending for a limited number of resources; memory, database connections, cpu time and so on. Therefore, tying up any of these with expensive operations will reduce the responsiveness and performance of your web application, or limit the number of users you can serve simultaneously. Futher, if the database operation is expensive, you can tie up your database, too, further limiting perofrmance.
It's always worth attempting to bring the execution time for database queries down for this reason alone. If you can't, then you will have to be careful about how many of these types of queries you can run simultaneously.
EDIT:
So you are actually interested in what's happening on the SQL Server side... the answer is... it depends! The CommandTimeout is actually a client event - what you are saying is that if the query takes longer than n seconds, then I don't want to wait any more. SQL Server gets told that this is the case, but it still has to deal with what it's currently doing, so it can actually take some time before SQL Server finishes the query. It will attempt to prioritise this, but that's about it.
This is especially true with transactions; if you are running a query wrapped in a transaction, and you roll that back as part of your exception management, then you have to wait until the rollback is complete.
It's also very common to see people panic and start issuing KILL commands against the SQL Process id that the query is running under. This is often a mistake if the command is running a transaction, but is often okay for long running selects.
SQL Server has to manage it's state such that it remains consistent. The fact that the client is no longer listening means you have wasted work but SQL Server still has to clean up after itself.
So yes, the ASP.Net side of things will be all fine as it doesn't care, but SQL Server still has to finish the work it began, or reach a point where it can abandon that work safely, or rollback any changes in any transactions that were opened.
This obviously could have a performance impact on the database server depending on the query!
Even a long running SELECT or UPDATE or INSERT outside of a transaction has to finish. SQL Server will try and abandon it as soon as it can, but only if it's safe to do so. Obviously, for UPDATES and INSERT's especially, it has to reach a point where the database is still consistent. For SELECT's it will attempt to end as soon as it is able to.
