Limit query execution time

#1

Hello,
I ran a query that I thought would take an hour, but instead took 14
hours to run. The consequence was it bogged down our data warehouse
and the overnight build was adversely impacted.
Is there a local setting I can set to limit the execution time my
query will take? I dont want to have a server setting and impact other
queries, just the one I am running.
I know there will be people asking about the 14 hour build and what is
it doing and so forth. I will address that but I also look to these
situations as a learning opportunity.
Thanks in advance.
Rob

#2

rcamarda (robert.a.camarda (AT) gmail (DOT) com) writes:

Quote:

I ran a query that I thought would take an hour, but instead took 14
hours to run. The consequence was it bogged down our data warehouse
and the overnight build was adversely impacted.
Is there a local setting I can set to limit the execution time my
query will take? I dont want to have a server setting and impact other
queries, just the one I am running.
I know there will be people asking about the 14 hour build and what is
it doing and so forth. I will address that but I also look to these
situations as a learning opportunity.

In Query Analyzer you can set the Query timeout under Options->Connection.
There is a similar option in Mgmt Studio.

On SQL 2005, combine these with SET XACT_ABORT ON, to make sure that any
open transactions are rolled back. On SQL 2000 this does not work, so you
have to be extra careful. It is possible that the connection option
"Disconnect when query completes" can save you.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#3

On Aug 15, 10:02 am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

rcamarda (robert.a.cama... (AT) gmail (DOT) com) writes:
I ran a query that I thought would take an hour, but instead took 14
hours to run. The consequence was it bogged down our data warehouse
and the overnight build was adversely impacted.
Is there a local setting I can set to limit the execution time my
query will take? I dont want to have a server setting and impact other
queries, just the one I am running.
I know there will be people asking about the 14 hour build and what is
it doing and so forth. I will address that but I also look to these
situations as a learning opportunity.

In Query Analyzer you can set the Query timeout under Options->Connection.
There is a similar option in Mgmt Studio.

On SQL 2005, combine these with SET XACT_ABORT ON, to make sure that any
open transactions are rolled back. On SQL 2000 this does not work, so you
have to be extra careful. It is possible that the connection option
"Disconnect when query completes" can save you.

--
Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for the quick and flame free reply Erland. I did fail to
mention the version of SQL I am using: SQL 2005.
I found in Query > Query Options > Execution > General > Execution
Time Out (currently set to 0 seconds).
I'm guessing that if I set this to 600, my query would terminate at 10
minutes if it didn't complete?
rob

#4

rcamarda (robert.a.camarda (AT) gmail (DOT) com) writes:

Quote:

Thanks for the quick and flame free reply Erland. I did fail to
mention the version of SQL I am using: SQL 2005.
I found in Query > Query Options > Execution > General > Execution
Time Out (currently set to 0 seconds).
I'm guessing that if I set this to 600, my query would terminate at 10
minutes if it didn't complete?

I guess that it's faster just to change and try, than wait for me to
answer. :-)

But I remember now that there was a weird bug with the query timeout in SSMS 2005. But it appears affects connections to the local server only. See
https://connect.microsoft.com/SQLSer...dbackID=286298

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Limit query execution time

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Limit query execution time - 08-15-2007 , 08:58 AM

Re: Limit query execution time - 08-15-2007 , 09:02 AM

Re: Limit query execution time - 08-15-2007 , 09:09 AM

Re: Limit query execution time - 08-15-2007 , 04:18 PM