This problem was discovered with MSDE2000 SP2 and under WinXP SP2. We
are unsure whether it is more widespread as it has only been seen on
one machine to date.
The problem is related to name resolution. If you attempt to connect
to a local database with a connect string using
server=.
rather than
server=(local)
or
server=MachineName
then the symptoms are that serious performance degradation on queries
will occur. The problem is related to name resolution and a registry
trace reveals excessive registry queries into the DNS and tcpip
subkeys of the hive.
Typically, application functions that took around 2 seconds were seen
to take nearly six times longer than normal. No CPU usage or disk I/O
occurred during the wait period.
This problem is unusual because in the past I have seen connection
performance problems involving name resolution - i.e, the connection
open itself takes a long time - but in this case, with a permanently
open connection, each query experiences performance degradation.
If you are having performance problems, you might like to check if you
are using this syntax for the server keyword in your connection string
and change it, to see if this helps.
It's got something to do with NetBIOS, because if you change the
client netlib so SQL Server is no longer listening on Named Pipes,
just TCP/IP, then the server=. syntax is no longer valid, and you are
unable to connect using it.
Note: we have tried, on the affected machine, clearing all DNS caches
and Netbios caches, and the issue occurs on the machine even when
entirely standalone with an IP address of 127.0.0.1. We also know that
the problem is entirely software-based, as swapping the operating
system drive to an identical piece of hardware causes the problem to
migrate to the new machine. However, this is as far as we have been
able to go with this. Since the workaround is relatively
straightforward, I guess we'll have to consign it to the pile of
Unsolved Microsoft Mysteries, of which there are many more, alas.
