Can I abort a specific sql statement after a given timespan?

Question

I would like to be able to abort a specific sql statement if it takes longer than a specified number of seconds. How can I do that?

For example, I would like to do something like:

SET NextStatementTimeOutSeconds = 60
SELECT * FROM MyTable
IF @@LastStatementTimedOut = 1
    PRINT 'Statement timed out after 60 seconds'
ELSE
    PRINT 'Statement completed in less than 60 seconds'

Please note that I made up NextStatementTimeOutSeconds and @@LastStatementTimedOut in order to illustrate what I would like to do.

---

Thanks for the suggestions. I'm thinking about using such a timeout inside a single sql script/batch like the one above, if possible.

Background: We have several stored procedures that I'd like to be faster than X seconds. So I'd call them one after each other; and if one takes more than X seconds, I'd just abort and print a statement like 'sproc 13 takes too long'. The data varies and I'm trying different index changes, so I'd like to time-test all sprocs after such changes in order to make sure that all of them are below X seconds.

Answer 1

One possibility could be that you could monitor all active connections and kill any that are active for your timeout.

I've never done anything like that before though.

Answer 2

Typically the timeout is set on the command that issues the SQL statement. If that's in C# for example, then you set the IDbCommand.CommandTimeout property and it'll throw a SQLException after the timeout expires if the statement hasn't completed. This will then rollback any transactions.

In scheduled tasks on SQL Server, I'd expect that when you configure the job, there will be a value in an up/down control somewhere that allows you to change the timeout of the job. I think the terminology in SQL Server is a "Execution Timeout" but I don't know how to set it I'm afraid.

The point is - look at the thing running the statement, rather than the actual statement itself.

Answer 3

You can use the query governor to limit the time a query can run. Downside is, it is a global option affecting all queries launched against a specific database.

Use the query governor cost limit option to specify an upper limit on the time period in which a query can run. Query cost refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration.

If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost exceeding that value. Specifying 0 (the default) for this option turns off the query governor, and all queries are allowed to run without any time limitation.

If you use sp_configure to change the value of query governor cost limit, the changed value is server wide. To change the value on a per-connection basis, use the SET QUERY_GOVERNOR_COST_LIMIT statement.

The query governor cost limit option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change query governor cost limit only when show advanced options is set to 1. The setting takes effect immediately (without a server restart).

Answer 4

If you're using SQL Server, you can add a linked server to the database where the query will run. You can set the timeout for the linked server like:

EXEC master.dbo.sp_serveroption @server=N'linkedserver', 
    @optname=N'query timeout', @optvalue=N'1'

Then you can run the query like:

select * from openquery(linkedserver,'select * from ...')

You can use a TRY...CATCH block to capture the timeout when it occurs:

begin try
select * from openquery(linkedserver,'select * from ...')
end try
begin catch 
print @@error
end catch

The @@error will be 7320 for "Query timeout expired".