SQL Server Performance

LAN to WAN = Slow Performance. Why?

Discussion in 'Performance Tuning for Hardware Configurations' started by rweinstein, Aug 3, 2006.

  1. rweinstein New Member

    Very simple problem.

    We moved corporate offices, let's say location A to location B. The SQL servers are still in location A.

    We are in location B and accessing SQL server data through the WAN (4.5MB pipe). When we were at location A, we were accessing SQL server over the LAN (100MB connection).

    In location B now, performance is now terrible when connecting to SQL server via applications (Logility, Cognos Data Manager). Sniffer tests show that the data is being transferred in packets, like slow bits at a time, that it is not using the full 4.5MB pipeline, there is a lot of unused space.

    Question:

    What can be the problem? What can I do to test? Why would the WAN be so much worse than the LAN, there have been no application changes. Also, when I go back to location a to test, performance is good like it was before.

    Can it be the ODBC/native drivers? Can it be the network switches, routers, NICS, etc?

    Any help/direction is greatly appreciated.

    One more thing. Does anyone think that if I log a call with Microsoft, they will be able to help?

    Thanks.

    Rodney
  2. satya Moderator

    I would investigate the problem from Network (switches,router& NIC) side first and also ensure the optimization jobs on SQL server are working as expected.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  3. joechang New Member

    see if your calls to sql server, ie, stored procs, start with SET NOCOUNT ON

    then run a network monitor to capture the sequence of the packets to and from a workstation to SQL server, one for the lan, the other for the WAN

    if you not know how to use the network capture, start by monitoring the performance counter Network Interface -> active network card -> packets sent/sec, packets received/sec

    run a specific set of queries,
    do this on the LAN, then exactly the same of the WAN

    also, look into a TCP/IP isssue with W2K, search nagle
  4. rweinstein New Member

    Satya/Joe,

    Feedback and direction is greatly appreciated!

    Do either of you think a Microsoft call would help? What is an example of a software program that does network monitoring that is easy to zero in on my issue (other than perfmon)?

    Optimization jobs (update statistics/shrink) are running as normal.

    How would you inverstigate the problems from the network?

    I'll do a test with the network monitoring stats and will let you know.

    Thanks.
  5. satya Moderator

  6. rweinstein New Member

    Here are the results of the PERFMON test for the network interface packets sent and received.

    When I connect from the WAN through the application to the server, the packets sent and received go up only very slightly over a period of 30 seconds, the time it takes to open.

    When I connect from the LAN through the application to the server, it opens immediately (3 seconds) and the packets sent and received spike way up.

    What does this tell us? What is preventing the packets from being sent all at once?

    Thanks.
  7. joechang New Member

    my mistake, forget perfmon
    go to Network Connections,
    right click on the network adapter
    select status
    note the current Packets Sent & Received

    run the test,
    note Pkt Sent & Rec

    I want the total packets sent and rec'd for each test
    I suspect their will be a difference in the way acknowledgement packets are sent

    ideally, the network monitor tool should be used because it will show the exact sequence,
    but the packet count might be adequate.

    also do a ping test from the WAN client to the server, not the response time

    i always like to do some basic investigation as to want might be the cause and get some hard data that MS might be able to reproduce, rather than just call every time i have a problem
  8. rweinstein New Member

    Joe,

    Packet test results:
    LAN (about 20 seconds):
    Sent = 1817 Received = 2185

    WAN (about 3 seconds):
    Sent = 2249 Received = 1911

    I'll post the results when I can get to another test with a little longer interval.

    Ping test results:
    LAN = consistantly less than 10ms
    WAN = No less than 27, but jumps around between 27 and 90ms with spikes to 150 and periods where it stays above 100 for a minute or so.

    My network team repeatedly indicates that the ping test is not a good test and even times in the 100ms range are so quick that we should not tell a difference.

    Thanks!

    Rodney

  9. joechang New Member

    your network team are f*cking idiots,
    100ms sucks, the 10ms is really anything between 0-10ms

    anything more can really tanks a network app not designed for slow networks
    (round trip times are more important than bandwidth
  10. joechang New Member

    let me rephrase
    2000 serialized network round-trips at 1ms latency works out to 2000ms or 2sec network progation delays
    2000 at 20ms works out to 40sec

    so they expect you to not notice the difference between 2 sec and 40 sec?
  11. rweinstein New Member

    From using PingPlotter, the average roundtrip time is 31ms. Most of the latency is coming from the connections into and out of the "network cloud."

    Joe,

    In your example, you give 2000 serialized round trips. Where did you get the 2000 from? Is this a small amount, high amount? How could I tell the round trips I am getting from my slow application test that takes 30 seconds?

    Thanks.
  12. joechang New Member

    from your own numbers
    what i can not tell is if they were serialized
    a properly designed app will send out several packets before requiring an acknowledgement, typically 8,
    but there was a problem with a mechanism to prevent virus/hack attacks that could shutdown a network
    that why i said to search for tcp & nagle

    yes 2000 is very high
    to throw the ball back your court, you need to figure out why your app generate ~2000 packets sent and received
    that why i said to see if you are using SET NOCOUNT
    if i say something, it is probably important and should not be overlooked
    run profiler to trap the statements for your test
  13. rweinstein New Member

    I set NOCOUNT to ON the very first thing this morning and will run the profiler.

    Thanks!
  14. rweinstein New Member

    UPDATE:

    Joe/Satya,

    Thanks for the help. These tools give me much more in the ability to monitor network traffic. Ping Plotter is a great freeware and shows all the hops and the latency. Excellent tool!

    We finally brought in a Cisco network engineer and the discovery was a slowdown in the AT&T routers.

    Not sure how much this will fix the problem, we are in discussions as far as timing and what will be upgraded, but I am optimistic we will see positive results.

    Rodney

Share This Page