Strange SQL traffic over WAN (link to csv file)

Question

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?

https://drive.google.com/file/d/0By8V5doQZvoWR1k1VUN4Q0tJRG8/view?usp=sharing

web-srvr==10.10.10.200, db-srvr==10.0.0.50

Answer 1

We are trying to troubleshoot issues with slow performance on this web application. It was developed in house.

To me it looks like your application is not well designed to work over a WAN. Take a look at TCP stream 9 (the only one with some data to transmit). The client sends a SELECT and gets the answer pretty fast. But then it waits for ~ 2.5 seconds before it sends the next SELECT. These seconds wait time add up to a lot of "dead wait time" with the result, that the whole communication takes longer than it should.

Example:

frame #1462, 50.174: SELECT 
frame #1469, 50.461: last ACK for that request
~2.2 seconds wait time
frame #1480, 52.693: SELECT
frame #1487, 52.977: last ACK for that request
~2.6 seconds wait time
frame #1488, 55.562: SELECT
frame #1495, 55.821: last ACK for that request

As you can see. Between the SELECT and the last ACK for that request, there is a time delta of a few 100 ms, which is O.K. for a WAN link, especially as the SELECT statement is pretty large (~ 6KB).

However between the last ACK and the next SELECT, there is a pause of ~2.5 seconds!!

You can see that in the TCP Stream graph as well.

Please ask your developers why there is a pause of n seconds between the SELECT statements, as that’s causing the whole problem!

++ UPDATE ++
BTW: A database application that uses a lot of SELECT statements might work well on a local network (or on localhost of the developers laptop) as there is almost no latency, but it won’t work well on a WAN link with high latency (RTT > 50ms). I’ve seen this particular problem quite often, because developers usually run everything on their laptop or on the local lan (client, web server, database server) and don’t realize, that latency might be a problem while they write and test the code. So, what a good programmer usually does to solve this kind of problem:

have an application server next to the database server (same lan) and send a request to the application server to retrieve the whole data in one step (JSON, XML, whatever)
write a stored procedure on the SQL server and call that, instead of sending 20-30 SELECT statements over a low latency network

;-))

alt text

Regards
Kurt