slow sql connections

I had a wierd problem today whilst we were doing some testing, we have a web application which was producing random results and no consistent performance, we though initally it was SQL being slow, but looking at profiler it was returning the queries in around 11ms once it actually got the request.

After bodging our way around some firewalls to rule them out I eventually knocked up a rough and ready vbs script that echoed the current time then attempted to do an ADODB connection to the database and then echo the time out again..

set myconn = createObject("ADODB.Connection")

wscript.echo now()
myconn.open ="DRIVER={SQL Server};SERVER=10.0.0.1\TEST;OPTION=35"
wscript.echo now()

Each time I executed this it took 9 seconds just to connect.


After much playing running the script locally on the sql box it resulted in returning immediatley, I installed wireshark on the DB box and had a look at what was happing, we were seing a few packets for LLMNR, I disabled this in group policy (Computer Configuration\Administrative Templates\Network\DNS Client\Turn off Multicast Name Resolution) this made no difference -apart from stopping this traffic, I then disabled LMHOSTS lookup and disabled NetBIOS over TCP/IP this didnt make a difference either.

I then added the hostname of the sql server into the clients hosts file and this resolved the issue immediatley.. from looking at an early MDAC bug relating to sql 2000 it seems that this was previously a bug - which has been fixed but it attempts to do a reverse lookup and then times out after 5 seconds - despite me specifying the ip\instance name as there is no DNS in use in this enviroment!

Anyway its fixed it for now, I may try and MDAC update later but for now Ill move on and find the next problem.