1

My site's web front-end and database are separated on two servers, which connected by 1000M lan.

I find almost every query's response time are more than 800ms. I can ensure that my query are all ok because when I deploy that on a single server the problem not appear.

website/asp.net 2.0 database/sql server 2000

2
  • 1
    Are you using ip addresses or hostname? Use ip address to avoid DNS problems.
    – lg.
    Dec 23, 2010 at 12:48
  • I use ip address
    – Alex Chen
    Dec 23, 2010 at 13:37

2 Answers 2

2
  1. If your DNS is causing an 800 ms lag to (what should be) a cached IP address, there is something seriously wrong with your networking setup. IMHO. You can see if that is a problem by using ping.

  2. Use traceroute to ensure that the packets are using an efficient route between the two computers. This is easy to do, so it is worth trying before you fiddle with other stuff. If there are a lot of hops in the route, maybe more than two or three, find a network guy and ask him why this is so.

  3. When trying to troubleshoot SQL network problems, it is best to use a very simple query, like "SELECT GETDATE()", and use a simple query tool like SQLCMD.EXE. The simple query means that the server will not have to spend a lot of time parsing the query, there will not be any significant locking or blocking and the query will not bring back millions of rows of data over the network. The simple query tool means that you don't have to worry about what IIS might or might not be doing. If there is no SQLCMD.EXE, OSQL.EXE or similar tool installed on the IIS server, it might be worth writing a tiny psh or vbs script to test the connectivity to the SQL Server. If you don't have that kind of access to the IIS server, you could probably write a special ASP page that just ran that simple query and returned the result and how long it took to run.

  4. If I have to guess at the problem, it is going to be this-> Especially with older equipment and drivers, avoid relying on the "auto-negotiation" settings on the NICs. Set both cards to the same settings manually.

I know it is a drag, but I have personally seen this clear up a dozen or so similar problems where the network guys were stumped. (These are servers, after all, and it isn't like they will be plugged into lots of different switches and would ever need to renegotiate link speed or duplex settings.)

You can also see this problem by observing the data rates on the NICs, as measured in MB/second, with Performance Monitor. Once you have established a baseline, change the settings on the NICs and observe again.

You can usually get away with changing those settings "live", but I wouldn't try it at peak load time the first time I fiddled with those settings. A trial in a test environment would be better, if you have one. If you have a maintenance window, that would be best. If there is only 1 NIC in the server, Be careful not to set the NIC so that it can't talk to the switch or you might have to ask someone to physically log into the console and that is a bummer for everyone.

1

There are many possible reasons. Could be a issue with one of the network adapter drivers for example.

You'll have to check if the SQL connection is the bottleneck or if it's a general networking problem. Try to issue a "select getdate()" command from the web server to see if every query is affected.

Are pings to the database server slow too? Also, try to use the IP address instead of the server name - maybe it's a DNS resolving problem.

4
  • I dont' think network could be a problem. Actually, the two servers both are guest on a vsphere cluster based on IBM bladecenter
    – Alex Chen
    Dec 23, 2010 at 13:34
  • 3
    @Anti, that information changes the entire troubleshooting aspect of this problem. You might want to update your question with more info on how your setup is configured specifically. Dec 23, 2010 at 14:38
  • after a re-deployment from vsphere guest to dedicated server yesterday, the problem still exist.
    – Alex Chen
    Dec 24, 2010 at 1:04
  • Did you check the Windows Eventlog on both servers for possible issues?
    – splattne
    Dec 24, 2010 at 10:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .