3

Wihin my C# code I am using the CommandTimeout function to ensure that any query that executes longer than 30s is terminated both from the server and database. However when listing the currently running queries on the database the query that was set to cancel after 30s runs well beyond 30s 

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlCommand sqlCommand = new SqlCommand(query, connection);

    //Set Timeout to 30s
    sqlCommand.CommandTimeout = 30;
    SqlDataAdapter da = new SqlDataAdapter(sqlCommand);

    da.Fill(response);
    connection.Close();
    da.Dispose();
}

enter image description here

Why is the query still running in the DB? Is my only option right now is to send another query from the server to kill the query (KILL [session_id]) after 30s?

EDIT: 300Mb of data is being returned for this query.

Jebathon
  • 4,310
  • 14
  • 57
  • 108
  • 9
    Command timeout is a client side setting. Meaning that is how long the client will wait. The sql engine doesn't care, it will keep running and running even if the client has long abandoned the thread. – Sean Lange Aug 01 '18 at 15:31
  • 2
    Possible duplicate of [What Happens To a Query If It Times Out?](https://stackoverflow.com/questions/5810786/what-happens-to-a-query-if-it-times-out) – Liam Aug 01 '18 at 15:32
  • @DanGuzman in a earlier question said in the comments with multiple upvotes "The SqlCommmand.CommandTimout property does send a cancel command (attention event) to the server to cancel the executing query on the server" https://stackoverflow.com/questions/51633878/send-command-to-sql-server-to-kill-query-mid-execution-if-it-reaches-x-time – Jebathon Aug 01 '18 at 15:33
  • Half of you guys are telling me it kills in db side the other half client side – Jebathon Aug 01 '18 at 15:33
  • 3
    Your C# will signal to the DB that it doesn't want to wait anymore. The SQL box will stop that query when it thinks it can. This **may** be instantly, it **may not** depending on what the SQL box is doing what your query is, etc. I'd be very careful on just `kill`ing it. If SQL doesn't want to cancel that query it likely has a very good reason. – Liam Aug 01 '18 at 15:34
  • Actually, I'm testing this using a variant of the posted code, and the client-side code does not appear to time out after the specified time. Shutting down the program, on the other hand, cancels the SQL execution server-side. So I don't think the the explanations given in these comments adequately explain what's happening here. – StriplingWarrior Aug 01 '18 at 15:41
  • Sounds like you might have a deadlock. Run a SQL trace – Liam Aug 01 '18 at 15:48
  • 1
    Unrelated tips: `SqlCommand` and `SqlDataAdapter` are both `IDisposable` so each should be in a `using` block. This will make the `da.Dispose()` redundant, and the `connection.Close()` already is redundant, since exiting the connection's using block will call Dispose, which calls Close. – Richardissimo Aug 01 '18 at 19:20

1 Answers1

6

There are a number of posts on StackOverflow indicating that SqlCommand.CommandTimeout won't affect the behavior of SqlDataAdapter.Fill. Instead, you supposedly have to set the SqlDataAdapter's SelectCommand.CommandTimeout property.

However, there are other posts which seem to indicate that even this doesn't work. This one in particular makes me think that the query will only be canceled if the timeout occurs before the query starts yielding results. Once results start coming in, it appears to ignore all timeouts.

My recommendation would be to reconsider using SqlDataAdapter. Depending on your use case, maybe a library like Dapper would work better for you?

You may also want to consider reporting this as a defect to the .NET team. I've had mixed success in the past reporting such errors; it depends on whether the team wants to prioritize fixing the issue.

Update

It looks like this may be the intended, documented behavior, as Marc Gravell points out here.

lol: from the documentation (https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(v=vs.110).aspx)

For example, with a 30 second time out, if Read requires two network packets, then it has 30 seconds to read both network packets. If you call Read again, it will have another 30 seconds to read any data that it requires.

So: this timeout resets itself every Read. So: the only way it'll trip is if any single Read operation takes longer than 2s. As long as the SQL Server manages to get at least one row onto the pipe in that time: it won't timeout via either API.

StriplingWarrior
  • 151,543
  • 27
  • 246
  • 315
  • Thanks for the reply. Setting the SelectCommand.CommandTimeout does not change anything. I feel you are right that the timeout is being ignored because the results are being filled in by the adapter continuously. When running the query in SQLSMS the rows are returned one by one and not all at once. I'm not sure if Dapper will make a difference – Jebathon Aug 01 '18 at 17:10
  • @BDillan: [Looks like](https://github.com/StackExchange/Dapper/issues/927) you're right: Dapper probably won't make a difference. – StriplingWarrior Aug 01 '18 at 19:23
  • @BDillan: **However**, it does look like Dapper respects a cancellation token... sort of. if you use a cancellation token on a dapper CommandDefinition, and pass it to a QueryAsync method, Dapper will attempt to cancel the query after a specified period of time, even if it's pulling data from the server at that time. The weird thing is that dapper's attempt to cancel the query appears to be subject to the command timeout that you specify. So if the query starts returning values right away, you'll get a SqlException (Timeout Expired) after `commandTimeout + cancellationToken`. – StriplingWarrior Aug 01 '18 at 19:46