Query Timeout

Question

Query Timeout

SQL33

SSCrazy

Points: 2044
More actions
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..

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

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

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 1

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

SQL33 SSCrazy Points: 2044 More actions · Answer 2

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

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 3

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

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 4

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

SQL33 SSCrazy Points: 2044 More actions · Answer 5

SQL33

SSCrazy

Points: 2044

March 18, 2011 at 12:12 pm

#1300722

Great ... thanks!

mca.vimal Ten Centuries Points: 1050 More actions · Answer 6

hi all

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

please told me

Vimal Kumar
9999797952

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 7

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

mca.vimal Ten Centuries Points: 1050 More actions · Answer 8

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

mca.vimal Ten Centuries Points: 1050 More actions · Answer 9

minimummaximumconfig_valuerun_value

0100

-2147483648214748364700

0111

0100

08640000

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

0214748364700

-12147483647-1-1

03276700

0111

0100

021474836472020

0100

02147483647600600

0100

0111

0100

0111

0100

1753999920492049

03276700

0100

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

Vimal Kumar
9999797952

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

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

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 11

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)