Limit query execution time

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

Re: Limit query execution time

rcamarda (robert.a.camarda@gmail.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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Limit query execution time

On Aug 15, 10:02 am, Erland Sommarskog wrote:
> rcamarda (robert.a.cama...@gmail.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...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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

Re: Limit query execution time

rcamarda (robert.a.camarda@gmail.com) writes:
> 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/SQLServer/feedback/ViewFeedbac k.aspx?FeedbackID=286298

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx