SQL Server & ADO NET : how to automatically cancel long running user query?

Question

I have a .NET Core 2.1 application that allows users to search a large database, with the possibility of using lots of parameters. The data access is done through ADO.NET. Some of the queries generated result in long running queries (several hours). Obviously, the user gives up on waiting, but the query chugs along in SQL Server.

I realize that the root cause is the design of the app, but I would like a quick solution for now, if possible.

I have tried many solutions, but none seem to work as expected.

What I have tried:

• CommandTimeout

  CommandTimeout works as expected with ExecuteNonQuery but does not work with ExecuteReader, as discussed in this forum

  When you execute command.ExecuteReader(), you don't get this exception because the server responds on time. The application doesn't respond because it reads data to the memory, and the ExecuteReader() method doesn't return control until all the data is read.

• I have also tried using SqlDataAdapter, but this does not work either.

• SQL Server query governor

  SQL Server's query governor works off of the estimated execution plan, and while it does work sometimes, it does not always catch inefficient queries.

• SQL Server execution time-out

  Tools > Options > Query Execution > SQL Server > General

  I'm not sure what this does, but after entering a value of 1, SQL Server still allows queries to run as long as they need. I tried restarting the server instance, but that did not make any difference.

Again, I realize that the cause of this problem is the way that the queries are generated, but with so many parameters and so much data, fine tuning a solution in the design of the application may take some time. As of now, we are manually killing any spid associated with this app that has run over 10 or so minutes.

EDIT:

I abandoned the hope of finding a simple solution. If you're having a similar issue, here is what we did to address it:

We created a .net core console app that polls the database for queries running over a certain allotted time. The app looks at the login name and the amount of time it's been running and determines whether to kill the process.

Answer 1

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.cancel?view=netframework-4.7.2

Looking through the documentation on SqlCommand.Cancel, I think it might solve your issue.

If you were to create and start a Timer before you call ExecuteReader(), you could then keep track of how long the query is running, and eventually call the Cancel method yourself.

(Note: I wanted to add this as a comment but I don't have the reputation to be allowed to yet)