Query Time Limit?

dgolds

I know I can use SET QUERY_GOVERNOR_COST_LIMIT to prevent a query from executing if its estimated execution time exceeds the value of the specified time in seconds, but is there some way to have a query stop executing (and raise an error) if the time exceeds a specified time For example, if the query takes longer than 20 seconds, have it stop executing and raise an error

Thanks,

-Dave

Satya SKJ

It prevents query from executing based on the cost rather than controlling resources.

BOL

'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 indefinitely.

"Query cost" refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration.

Arnie Rowland

If you are referring to having the ability to control any one specific query -that is not possible.

QUERY_GOVERNOR_COST_LIMIT effects ALL queries executed on the server. It prevents a query from executing IF the estimated time exceeds the limit. Say the QUERY_GOVERNOR_COST_LIMIT was 120 seconds, the estimated plan was 119 seconds -the query would then execute EVEN if the resulting time required exceeded 120 seconds. As far as I am aware, there is no way to have an executing query abort at some predetemined elapsed time value.

Of course, from an application, it might be possible to spawn a thread that executed a query against SQL Server, and then the parent thread could abort the executing thread after a predetermined time. But that could be messy...