SQL Server Performance

Query Timeout Setting Advice

Discussion in 'General Developer Questions' started by ranjitjain, Aug 18, 2005.

  1. ranjitjain New Member

    Hi Experts,
    How to set the query Timeout to 10 seconds.
    I checked it using sp_configure and it shows 600 seconds for remote query timeout.
    I changed it using sp_configure 'query timeout',10
    The above command executed & displayed changed setting.

    But still when i ran a query in QA which takes more than 5 mins. doen't time's out

    Why is this happening. Is there any other setting i need to change..
    I hope you got what i wanted...?
    I need to cancel those queries running from QA to be time out in 10 second taking more than that...
    Thanks.
  2. satya Moderator

  3. Twan New Member

    Hi ya,

    in QA go to Tools, Options. Click on the Connections tab and set the timeout there to what you want. QA will set its own timeout for each connection, the one in MSSQL itself is just a default timeout

    Cheers
    Twan
  4. ranjitjain New Member

    Hi TWAN,
    I set the option in QA i.e. query timeout from 0 to 10 seconds...
    Still the query is not getting cancelled it runs more than that specified time...
    Is it something im missing still???????
  5. satya Moderator

    Have you tried 'query wait (sec)' using SP_CONFIGURE?
    query wait is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change query wait only when show advanced options is set to 1. The setting takes effect immediately (without a server stop and restart).

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. ranjitjain New Member

    Hi Satya,
    I ran sp_configure 'show advanced option',1
    sp_configure 'query wait',10
    reconfigure
    exec sp_configure


    it shows that query wait=10 & query timeout=10
    still the query runs for more than 10 seconds and the query is simple select query.
  7. ranjitjain New Member

    Hi Guys,
    I'm still in no mans land.
    The portal application calls DB from front end.
    The front end guys needs the query timeout error when it runs for more than 10 seconds.
    They dont want to make it from front end.
    So how to get it done.
    I tried sp_configure as i mentioned earlier but with no fruit.
    After reconfigure when i run a query in QA it still runs even after 10 seconds.

    Post your comments guys??????????
  8. satya Moderator

    Have you restarted SQL services after sp_configur settings?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. ranjitjain New Member

    Hi Satya,<br />Thanx alot,<br />i dint thought of restarting the services as you mentioned and i read in BOL of no need of restarting so i left as is.<br />Anyways i restarted the service and its running with timeout expired message.<br />Thanx alot.<br />I'm really lucky that im in this forum ..........[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  10. Adriaan New Member

    If you set the query time-out at the server, ALL remote queries against ALL databases will time-out after 10 seconds, not only queries from a specific front-end application against a specific database.
  11. ranjitjain New Member

    Hi Adriaan,
    That's true,
    But is there any way to set the query timeout only for a specific database at server level????
  12. Adriaan New Member

    I don't think so, check BOL for Setting Database Options.

    In the end, that's why it is available as a client-side setting ...
  13. ranjitjain New Member

    I checked in BOL about databse options adriaan,
    But of no help.
    So i prefered better for all rather than for none.
    anyhow, I'm still fighting to make it client side setting, lets seeee????
  14. satya Moderator

    Thats why I mentioned earlier:<br />If you are using the sp_configure system stored procedure to change the setting, you can change query wait only when show advanced options is set to 1. The setting takes effect immediately (without a server stop and restart).<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ranjitjain</i><br /><br />Hi Satya,<br />Thanx alot,<br />i dint thought of restarting the services as you mentioned and i read in BOL of no need of restarting so i left as is.<br />Anyways i restarted the service and its running with timeout expired message.<br />Thanx alot.<br />I'm really lucky that im in this forum ..........[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  15. ranjitjain New Member

    Hi Experts,
    Sorry to say i checked that in my QA window and was expiring after restarting because of the option i set in QA to 10 secs as said by adriaan.
    So that suggestion really worked for me.
    So whenever i change any value in QA option it works in QA window and not by sp_configure.
    I tried to set query wait & query timeout but both not expiring the query within span.
    What should i try now.
    I want query timeout especially in application.
  16. Adriaan New Member

    Your question was already answered: define the query time-out setting for the connection from the client side.
  17. ranjitjain New Member

    Hi Adriaan,
    Thats true that i need to do it from client side.
    But not possible for me this time.
    You gave me the solution to change the QA option of query timeout to 10 seconds which worked but now how to get that same timeout expired message through portal.
  18. Adriaan New Member

    Your portal must be running a client application of some kind.
  19. ranjitjain New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Your portal must be running a client application of some kind.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes Adriaan,<br />But Appl developer needs to add that timeout code in various places so they want configure at one central point i.e. at backend. So needs to accomplish this. But how still untraceble.[<img src='/community/emoticons/emotion-6.gif' alt=':(' />][xx(]
  20. Adriaan New Member

    Any decent appl developer knows how to create reusable code - objects, classes ... Reusable code is the central point they need to look at, not the backend.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page