How to set Query Timeout

[sparkme]

Can anyone tell me how to set query timeout.

I have a stored procedure running with select statement I want to include a timeout option if the query runs for more than 3 minutes ,only for this procedure not at server level for all procedures.

Can anyone tell me how to do this

Thanks
Sparkme

 

[genomon]

Got this from BOL:Syntax
object.QueryTimeout [= value]

Parts
object

Expression that evaluates to an object in the Applies To list

value

Long integer that specifies the number of seconds

Data Type
Long

Modifiable
Read/write



Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes.

 

[SQLSister]

If you have a stored proc running for more than 3 minutes you need to seriously look at changing the code or indexes etc. to improve performance. How angry are your users going to be if they never can find anything inthe search or if the search often takes 2.5 minutes?

"NOTHING is more important in a database than integrity." ESquared

 

[SQLDenis]

you could try setting the query governor cost limit to 180 seconds


SET QUERY_GOVERNOR_COST_LIMIT 180

But if your query runs for 3 minutes then you have serious issues with your DB





Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[TysonLPrice]

SQLDenis,

I wanted to try that and it looks like it estimates the cost and then will either run or not. Not that it stops it after a specified time.

SET QUERY_GOVERNOR_COST_LIMIT 100
select * from object_data nolock

Server: Msg 8649, Level 17, State 1, Line 1
The query has been canceled because the estimated cost of this query (246) exceeds the configured threshold of 100. Contact the system administrator.

 

[SQLDenis]

that is true, it is an estimation
why run the query for 3 minutes and then stop if you know already it will take more than 3 minutes?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[TysonLPrice]

why run the query for 3 minutes and then stop if you know already it will take more than 3 minutes?"

It's "smarter" then I thought.

This runs:

SET QUERY_GOVERNOR_COST_LIMIT 100
select top 100 * from object_data nolock

This runs:

SET QUERY_GOVERNOR_COST_LIMIT 100
select top 10000 * from object_data nolock

This runs:

SET QUERY_GOVERNOR_COST_LIMIT 100
select top 1000000 * from object_data nolock

This won't:

SET QUERY_GOVERNOR_COST_LIMIT 100
select top 10000000 * from object_data nolock

 

[TysonLPrice]

How would you go about trapping that error to send it back to an application? This doesn't work:

SET QUERY_GOVERNOR_COST_LIMIT 100
select top 100000000 * from object_data nolock
PRINT '@@error is ' + ltrim(str(@@error)) + '.'