Changing the ODBC default time out without program changes

Hello
This may have been asked before, but I can't seem to find it.
I believe the default ODBC time out is 30 seconds. I know that you can
override that by changing your application call. Can it be overridden
without making program changes? Perhaps through a registry setting (though I
can't find that)? Some sort of config file? A funky entry in a mysterious
ini file?
Thank you!
Steve
If you are using a DSN or a external file you can change that without
changing yout code, if you hardcoded that therte is no way changing it
without recoding and compiling it.
SO what did you do ?
HTH, Jens SUessmeyer.
"Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
> Hello
> This may have been asked before, but I can't seem to find it.
> I believe the default ODBC time out is 30 seconds. I know that you can
> override that by changing your application call. Can it be overridden
> without making program changes? Perhaps through a registry setting (though
> I can't find that)? Some sort of config file? A funky entry in a
> mysterious ini file?
> Thank you!
> Steve
>
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.odbc:45049
Nothing. The application is just timing out. Please tell me how.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e6qqdZafFHA.3936@.tk2msftngp13.phx.gbl...
> If you are using a DSN or a external file you can change that without
> changing yout code, if you hardcoded that therte is no way changing it
> without recoding and compiling it.
> SO what did you do ?
> HTH, Jens SUessmeyer.
> "Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
> news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
>
|||Shoot..no response.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e6qqdZafFHA.3936@.tk2msftngp13.phx.gbl...
> If you are using a DSN or a external file you can change that without
> changing yout code, if you hardcoded that therte is no way changing it
> without recoding and compiling it.
> SO what did you do ?
> HTH, Jens SUessmeyer.
> "Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
> news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
>

Changing the ODBC default time out without program changes

Hello
This may have been asked before, but I can't seem to find it.
I believe the default ODBC time out is 30 seconds. I know that you can
override that by changing your application call. Can it be overridden
without making program changes? Perhaps through a registry setting (though I
can't find that)? Some sort of config file? A funky entry in a mysterious
ini file?
Thank you!
SteveIf you are using a DSN or a external file you can change that without
changing yout code, if you hardcoded that therte is no way changing it
without recoding and compiling it.
SO what did you do ?
HTH, Jens SUessmeyer.
"Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
> Hello
> This may have been asked before, but I can't seem to find it.
> I believe the default ODBC time out is 30 seconds. I know that you can
> override that by changing your application call. Can it be overridden
> without making program changes? Perhaps through a registry setting (though
> I can't find that)? Some sort of config file? A funky entry in a
> mysterious ini file?
> Thank you!
> Steve
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.odbc:45049
Nothing. The application is just timing out. Please tell me how.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e6qqdZafFHA.3936@.tk2msftngp13.phx.gbl...
> If you are using a DSN or a external file you can change that without
> changing yout code, if you hardcoded that therte is no way changing it
> without recoding and compiling it.
> SO what did you do ?
> HTH, Jens SUessmeyer.
> "Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
> news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
>|||Shoot..no response.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e6qqdZafFHA.3936@.tk2msftngp13.phx.gbl...
> If you are using a DSN or a external file you can change that without
> changing yout code, if you hardcoded that therte is no way changing it
> without recoding and compiling it.
> SO what did you do ?
> HTH, Jens SUessmeyer.
> "Steve" <pezguy@.mn.rr.com> schrieb im Newsbeitrag
> news:knEwe.4555$Dv3.57@.tornado.rdc-kc.rr.com...
>

Changing Explain Plan

Hi,
We have a stored proc that generally returns in about 4-5 seconds. Over the
course of a few days (2-5) the stored proc starts returning in about 50 sec
onds. The change is sudden, not a gradual degradation. The explain plan ha
s changed. If I then add a
hint to the main query (loop join), compile it, take the hint back out, comp
ile it (ie it is now exactly the same as it was), it then starts returning i
n 4-5 seconds once again.
We have automatic creation and update of statisics turned on. The profile o
f the data doesn't change that much day-to-day.
Why is it so? How to stop SQL Server from changing the plans for this query
?
TIA,
SJTHi SJT,
Sudden performance degrade might might be due to "Fragmetation". Can you
execute the DBCC SHOWCONTIG on all tables and identify the tables
which is fragmented. For all fragmented table you could run DBCC DBREINDEX
or DBCC INDEXDEFRAG to remove fragmetation.
See the details of DBCC SHOWCONTIG, DBCC DBREINDEX and DBCC INDEXDEFRAG in
boks online.
How to stop SQL Server from changing the plans for this query?
See "Execution Plan Caching and Reuse" in books online.
Thanks
Hari
MCDBA
"SJT" <SJT@.discussions.microsoft.com> wrote in message
news:E48CD7B7-28C0-4A13-99C7-5ED85FB7E5D8@.microsoft.com...
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over
the course of a few days (2-5) the stored proc starts returning in about 50
seconds. The change is sudden, not a gradual degradation. The explain plan
has changed. If I then add a hint to the main query (loop join), compile
it, take the hint back out, compile it (ie it is now exactly the same as it
was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this
query?
> TIA,
> SJT|||SJT,
instead of rewriting the query two times, see if "sp_recompile <name of
SP>" achieves the same thing.
Also, search Google for "parameter sniffing", and see if that is causing
your procedure's peformance fluctuation.
Hope this helps,
Gert-Jan
SJT wrote:
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over the cours
e of a few days (2-5) the stored proc starts returning in about 50 seconds. The cha
nge is sudden, not a gradual degradation. The explain plan has changed. If I then
add
a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it
is now exactly the same as it was), it then starts returning in 4-5 seconds once again.[vbco
l=seagreen]
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this que
ry?
> TIA,
> SJT[/vbcol]
(Please reply only to the newsgroup)

Changing Explain Plan

Hi,
We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add a
hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
Why is it so? How to stop SQL Server from changing the plans for this query?
TIA,
SJT
Hi SJT,
Sudden performance degrade might might be due to "Fragmetation". Can you
execute the DBCC SHOWCONTIG on all tables and identify the tables
which is fragmented. For all fragmented table you could run DBCC DBREINDEX
or DBCC INDEXDEFRAG to remove fragmetation.
See the details of DBCC SHOWCONTIG, DBCC DBREINDEX and DBCC INDEXDEFRAG in
boks online.
How to stop SQL Server from changing the plans for this query?
See "Execution Plan Caching and Reuse" in books online.
Thanks
Hari
MCDBA
"SJT" <SJT@.discussions.microsoft.com> wrote in message
news:E48CD7B7-28C0-4A13-99C7-5ED85FB7E5D8@.microsoft.com...
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over
the course of a few days (2-5) the stored proc starts returning in about 50
seconds. The change is sudden, not a gradual degradation. The explain plan
has changed. If I then add a hint to the main query (loop join), compile
it, take the hint back out, compile it (ie it is now exactly the same as it
was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this
query?
> TIA,
> SJT
|||SJT,
instead of rewriting the query two times, see if "sp_recompile <name of
SP>" achieves the same thing.
Also, search Google for "parameter sniffing", and see if that is causing
your procedure's peformance fluctuation.
Hope this helps,
Gert-Jan
SJT wrote:
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add
a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this query?
> TIA,
> SJT
(Please reply only to the newsgroup)

Changing Explain Plan

Hi,
We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
Why is it so? How to stop SQL Server from changing the plans for this query?
TIA,
SJTHi SJT,
Sudden performance degrade might might be due to "Fragmetation". Can you
execute the DBCC SHOWCONTIG on all tables and identify the tables
which is fragmented. For all fragmented table you could run DBCC DBREINDEX
or DBCC INDEXDEFRAG to remove fragmetation.
See the details of DBCC SHOWCONTIG, DBCC DBREINDEX and DBCC INDEXDEFRAG in
boks online.
How to stop SQL Server from changing the plans for this query?
See "Execution Plan Caching and Reuse" in books online.
Thanks
Hari
MCDBA
"SJT" <SJT@.discussions.microsoft.com> wrote in message
news:E48CD7B7-28C0-4A13-99C7-5ED85FB7E5D8@.microsoft.com...
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over
the course of a few days (2-5) the stored proc starts returning in about 50
seconds. The change is sudden, not a gradual degradation. The explain plan
has changed. If I then add a hint to the main query (loop join), compile
it, take the hint back out, compile it (ie it is now exactly the same as it
was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this
query?
> TIA,
> SJT|||SJT,
instead of rewriting the query two times, see if "sp_recompile <name of
SP>" achieves the same thing.
Also, search Google for "parameter sniffing", and see if that is causing
your procedure's peformance fluctuation.
Hope this helps,
Gert-Jan
SJT wrote:
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this query?
> TIA,
> SJT
--
(Please reply only to the newsgroup)