10 second delay on every ODBC connection

From: Ken Franklin (KenFranklin_at_discussions.microsoft.com)
Date: 08/31/04 

Date: Mon, 30 Aug 2004 18:55:07 -0700

We have been encountering a perplexing problem at some of our customer sites.
 Our application is an app that has a server based component and a few client
based components. For the server based component, after the server has been
up for a day or so, every connection to SQL server will take 10 seconds
(always 10 seconds). This will only occur on very specific configs though.
The machines that seems to have the problem are running NT4 SP6a with SQL 6.5
SP5 and MDAC 2.6 SP2 Refresh.

This problem does not just manifest itself in our app. The "Test
Connection" button in the ODBC DSN config dialog also takes 10 seconds to
connect, so I don't believe that it is a coding issue with our app. I have
run a SQL trace at the time that this is happening and can see the connection
attempt occur, and then 10 seconds pass, and then the ODBC test stuff comes
through. If I do the same thing from a client machine it goes right through
no problem. When I reboot the server the problem goes away for a day or two,
even if I flood it with transactions trying to replicate the problem. I have
attempted to change protocols (TCP/named pipes), use NT auth versus SQL auth
with no difference. I have found a very few articles that suggest that the
reverse lookup added with the 2.6 MDAC can cause connections to take longer
(5 seconds) if your DNS is broken, but this does not seem to be the case here
because DNS works fine. But just to be sure I set up a host file anyway to
no avial.

I know that the obvious answer is to upgrade the OS and SQL server, and we
are upgrading all of our customers as we are alble. But time and money will
prevent this from happening as fast as it needs to. Any advice or
experiences would be appreciated.