SQL Server Performance Forum – Threads Archive
LAN to WAN = Slow Performance. Why?
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. RodneyI 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.
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
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.
Search underhttp://www.netmon.org/tools.htm for such tools. 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.
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.
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
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
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
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?
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.
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
I set NOCOUNT to ON the very first thing this morning and will run the profiler. Thanks!
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
]]>