Strange SQL traffic over WAN (csv file attached)
I'd like some opinions on a wireshark capture between a web server and SQL database server that takes place over our WAN. This is east coast to west coast. We are trying to troubleshoot issues with slow performance on this web application. It was developed in house. We already know we have some issues with our WAN connection to this location from time to time and also looking into that. I haven't found in issues on the LAN yet. Also, there are other dependencies but this seems to be the main bottleneck since it's the only one cross-country.
I'm not a packet guru, I do know the fundamentals of TCP. I'm seeing a lot of ACK's with no SYN's and multiple ACK's in a row. Is this a bad capture? Also, it's saturated with reassembled PDUs. The average MS for this connection is 90-100, which I know might be out of the range of tolerance for SQL and could be causing the problem. Can anyone take look at this capture and shed some light on what they're seeing?
websrv-dbsrv-cap.csv
I'm not a packet guru, I do know the fundamentals of TCP. I'm seeing a lot of ACK's with no SYN's and multiple ACK's in a row. Is this a bad capture? Also, it's saturated with reassembled PDUs. The average MS for this connection is 90-100, which I know might be out of the range of tolerance for SQL and could be causing the problem. Can anyone take look at this capture and shed some light on what they're seeing?
websrv-dbsrv-cap.csv
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yeah, by the end of my tome, I had somehow dropped that fact, as I was instead looking at the CSV data only. In this case, the "client" is the web server. This nullifies the suggestion about a Term server as a solution, but the rest of the analysis is still accurate -- and moving the client (in this case the web server) closer to the database will help.
ASKER
Thanks BillBach and robocat, this is very helpful information! The client begins with end users and their web browser of choice (HTTP), the Webserver is next in line requesting data from the DB server cross country (SQL). I met with the developers, and PM's this morning and presented some evidence of the 100-120 ms round trips that appear to be killing the response time of the web application.
As BillBach stated...
There are actually 2 servers on the east coast requesting data from the 1 on the west coast. Fore the most part everyone agrees that moving these boxes to the same LAN will at least eliminate the delay.
Does anyone know of a best practice guide to SQL client\server design in regards to network considerations (location,delay, etc) ? I can find a lot of forum posts supporting what we're discussing here but nothing official I could actually use in my meetings with these people.
As BillBach stated...
Remember that each one will take 100ms to complete, or only 10 requests per second
There are actually 2 servers on the east coast requesting data from the 1 on the west coast. Fore the most part everyone agrees that moving these boxes to the same LAN will at least eliminate the delay.
Does anyone know of a best practice guide to SQL client\server design in regards to network considerations (location,delay, etc) ? I can find a lot of forum posts supporting what we're discussing here but nothing official I could actually use in my meetings with these people.
I don't have any "formal" papers, but I suppose we could write one.
As a child, did you ever do one of those races where you need to fill a glass with water from across the room, one spoonful at a time? This is a great analogy to explain this concept to people, since most people have at least seen one of these, if not actually participated.
The network latency is the time it takes you to cross the room. Each spoonful of water is one packet. Spoonfuls are pretty small, so you have to go back & forth many times to get the job done. Imagine if you could instead carry a 1/2-cup scoop every time? It might take more time to fill the 1/2-cup scoop, and the latency is no different, but it requires far fewer trips in total, and you'll be done many times faster. Now, imagine a 1-quart container doing the same!
In short, it's not the volume of data, but the number of times you have to go back & forth that makes the difference here. Designing an application which reduces the number of actual database calls to the bare minimum will reduce this round trip time delay, and thus run faster.
As a child, did you ever do one of those races where you need to fill a glass with water from across the room, one spoonful at a time? This is a great analogy to explain this concept to people, since most people have at least seen one of these, if not actually participated.
The network latency is the time it takes you to cross the room. Each spoonful of water is one packet. Spoonfuls are pretty small, so you have to go back & forth many times to get the job done. Imagine if you could instead carry a 1/2-cup scoop every time? It might take more time to fill the 1/2-cup scoop, and the latency is no different, but it requires far fewer trips in total, and you'll be done many times faster. Now, imagine a 1-quart container doing the same!
In short, it's not the volume of data, but the number of times you have to go back & forth that makes the difference here. Designing an application which reduces the number of actual database calls to the bare minimum will reduce this round trip time delay, and thus run faster.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
HTTP is designed for high latency and will tolerate the latency much better.