Post reply

Query Timeout

  • March 18, 2011 at 9:51 am

    #97807

    Good morning all.. everything I read seems to indicate that query timeouts when set to "0" means infinite... no timeout. Yet, when I query a table the query runs for about 30 seconds then reports that it has reached the timout threshold. When I check the timout settings in query properties it is set to 0. When I set it to the max allowed (32000 in SQL 2000) the timeout is greatly longer, but still eventually times out. I want it at infinite .. how can I implement this setting for query analyzer?

    Thanks..

  • March 18, 2011 at 10:18 am

    #1300634

    My bet is that the timeout is from a .NET or client application. The default .NET connection timeout is 30 seconds. You can test this by running the same query from Query Analyzer and watching it spin for longer than 30 seconds. That will verify what you are seeing.

    You can set the timeout in the .NET 'Command' object. cmd.Timeout = x is the syntax.

    Jim

    Jim Murphy
    http://www.sqlwatchmen.com
    @SQLMurph

  • March 18, 2011 at 10:38 am

    #1300648

    There is no .net or any other app being used against the database yet.. I only have the tables right now and am using SQL2000 query analyzer or SQLK25 query analyzer to query the table and getting the timeout with them and when using enterprise manager to query.

    [Jim].[dba].[Murphy] (3/18/2011)

    My bet is that the timeout is from a .NET or client application. The default .NET connection timeout is 30 seconds. You can test this by running the same query from Query Analyzer and watching it spin for longer than 30 seconds. That will verify what you are seeing.

    You can set the timeout in the .NET 'Command' object. cmd.Timeout = x is the syntax.

    Jim

  • March 18, 2011 at 10:50 am

    #1300656

    Then the server settings you are configuring are the right ones. Setting the timeout in SQL Server to -1 will prevent timeouts.

    Run: sp_configure ...and look at the time out settings there. Take not as to if the timeout settings is active for the run_value column. If not, then you need to do a... RECONFIGURE...to activate the changed setting.

    It's possible that you are changing the timeout setting, but it won't take effect until a RECONFIGURE is performed.

    Also, there are different timeout settings; remote login timout (s), remote query timeout (s), etc. Is this a remote query or are you connecting to your local SQL Server instance?

    Jim

    Jim Murphy
    http://www.sqlwatchmen.com
    @SQLMurph

  • March 18, 2011 at 10:54 am

    #1300663

    Oh, the 'query wait (s)' property is an Advanced Option. So you can turn on advanced options by running:

    sp_configure 'show advanced options', 1

    RECONFIGURE

    ... then you can see all of the options.

    Jim Murphy
    http://www.sqlwatchmen.com
    @SQLMurph

  • March 18, 2011 at 12:12 pm

    #1300722

    Great ... thanks!

  • March 26, 2011 at 2:57 am

    #1303938

    hi all

    i run this query twise is there any Draback of this......

    please told me

    Vimal Kumar
    9999797952

  • March 26, 2011 at 9:12 am

    #1303949

    No drawbacks. sp_configure With no parameters, just shows you the existing settings in SQL Server and if it is active or not (Runnable column).

    Passing parameters tp sp_configure changes the settings, well, sort of. The settings are not active until reconfigure is run. You can run both of these as much as you want and it shouldn't be a problem.

    The only side effect, is not in running the statements to perform the reconfiguration over and over. But changing a value is changing the way SQL Server operates, and this could be a problem if you change to settings which are not good in your environment. Therefore, you should always test on another system before reconfiguring settings. Also, it is wise to research each setting you intend to change and don't just try them, especially on a production system.

    Jim

    Jim Murphy
    http://www.sqlwatchmen.com
    @SQLMurph

  • March 27, 2011 at 3:13 am

    #1304033

    dear

    i am run

    sp_configure 'show advanced options', 1

    RECONFIGURE

    twice in my SQL 2005 Server. if is there any problem will be occur in my Database ...

    Vimal Kumar
    9999797952

  • March 27, 2011 at 3:20 am

    #1304035

    minimummaximumconfig_valuerun_value

    0100

    -2147483648214748364700

    -2147483648214748364700

    -2147483648214748364700

    -2147483648214748364700

    0111

    0100

    0100

    08640000

    0100

    0100

    03276755

    0100

    -12147483647-1-1

    0100

    0214748364710331033

    0999900

    0111

    0100

    010000

    032767100100

    03276700

    032767100100

    03276700

    704214748364700

    0200

    0100

    5000214748364700

    06400

    025644

    16214748364721474836472147483647

    021474836476553665536

    1283276700

    036500

    512214748364710241024

    0214748364700

    0111

    5123276740964096

    0100

    0214748364700

    136006060

    0100

    0100

    0214748364700

    -12147483647-1-1

    03276700

    0111

    0100

    021474836472020

    0100

    02147483647600600

    0100

    0100

    0111

    0100

    0111

    0111

    0100

    0100

    1753999920492049

    03276700

    03276700

    0100

    0100

    now i run sp_configure then i got these values please told me how can i got my previous .

    Vimal Kumar
    9999797952

  • March 27, 2011 at 3:48 am

    #1304042

    Query wait is not a query timeout. SQL Server does not time queries out, the connecting application (in this case query analyser) does.

    Query wait is how long an executing query waits for memory resources while executing. The default is usually a good setting, don't fiddle with it unless you know what you are doing. If you have multiple queries waiting for memory then you have serious server problems that are not going to be fixed with a config change.

    Now, what's the EXACT error that you are getting?

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 27, 2011 at 2:09 pm

    #1304117

    SQL33 (3/18/2011)

    Good morning all.. everything I read seems to indicate that query timeouts when set to "0" means infinite... no timeout. Yet, when I query a table the query runs for about 30 seconds then reports that it has reached the timout threshold. When I check the timout settings in query properties it is set to 0. When I set it to the max allowed (32000 in SQL 2000) the timeout is greatly longer, but still eventually times out. I want it at infinite .. how can I implement this setting for query analyzer?

    Thanks..

    I'm going to make a slightly different suggestion than the others. Messing around with timout settings means only one thing... there's some slow and usually resource intensive code involved. My recommendation is to find out what that code is and fix it. Seriously.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply