April 17, 2012 at 3:50 am
Folks,
Can we increase the query Execution Time and what is the execution time of query.by default it is 30 secs.
April 17, 2012 at 4:22 am
I assume you mean you want to increase application timeout, not query execution time.
It'll be changed in the application (because SQL has no concept of a timeout). In .net code it's a properly of the SQLCommand object, don't know what it is in other languages, check with your developers they should know.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2012 at 6:32 am
If you mean timeout values, you can change that in your connection configuration for the application. As Gail mentioned, it's not usually an SQL Server property. There is a "remote query timeout" property for SQL Server, but as per http://msdn.microsoft.com/en-us/library/ms177457(v=sql.105).aspx, it's an outbound property, and has no effect on incoming queries.
Your developers should know where/how to set the timeout on their connections.
I recommend against setting it above 30 seconds for any application that has user-interaction. Not a "law", just based on my experience with UI optimization.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2012 at 8:43 am
I agree. Unless this is an extremely complicated report there really shouldn't need to be queries that take more than 30 seconds. More often than not when devs start exploring query timeouts it is due to poor performing sql. I would start there and see if you can make the sql faster instead of the other way around.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2012 at 9:44 am
there is an execution time-out option in SQL.
click Tools --> Options... and then choose 'Query Execution' from the resulting popup window.
you will see an option that says 'Execution time-out' which, in my case, shows 0 seconds. 0 indicates an unlimited wait (no time-out).
April 17, 2012 at 9:59 am
davidandrews13 (4/17/2012)
there is an execution time-out option in SQL.click Tools --> Options... and then choose 'Query Execution' from the resulting popup window.
you will see an option that says 'Execution time-out' which, in my case, shows 0 seconds. 0 indicates an unlimited wait (no time-out).
That's not SQL Server's query timeout. That's Management Studio's query timeout (the client tool)
SQL Server does not have any timeout for queries sent to it. It will happily run forever. It's the client tools that set a maximum amount of time they are willing to wait
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2012 at 11:41 pm
SQLDBA ARJUN (4/17/2012)
Folks,Can we increase the query Execution Time and what is the execution time of query.by default it is 30 secsl.
Hi
Arjun are you sure about increasing query exic time. Just confirm it
Ali
MCTS SQL Server2k8
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply