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.
Robrcamarda (robert.a.camarda@.gmail.com) writes:
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Aug 15, 10:02 am, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
rcamarda (robert.a.cama...@.gmail.com) writes:
Quote:
Originally Posted by
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/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|||rcamarda (robert.a.camarda@.gmail.com) writes:
Quote:
Originally Posted by
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/SQLSe...edbackID=286298
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
No comments:
Post a Comment