469,647 Members | 1,815 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,647 developers. It's quick & easy.

FYI: a weird performance problem with MSDE and possibly SQL Server 2000

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.
Jul 23 '05 #1
0 1032

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

15 posts views Thread by alex4groups | last post: by
1 post views Thread by Diana Finley | last post: by
6 posts views Thread by baramee | last post: by
1 post views Thread by Sebastian Santacroce | last post: by
14 posts views Thread by Developer | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.