Where to check? 2

[jkeeper]

I am at the central office where the hub of our network is located. A remote location is having problems with clients talking to the SQL server.

The remote tech says there are a lot of collission between our location and his site. My question is can I use Sniffer to see what is going on, and where should I start or place Sniffer.

Yesterday I did Span the switch port to the Server and I saw 280 + mils.

-James

 

[mpennac]

James -

There are several things that are going to cause the SQL server responses to be slow. These things can be put into two categories. Either it never worked right, or it just started being slow.

Category 1 - It never worked right.

Your problem is LATENCY!! This one thing will separate the well written applications from the poorly written applications. I recently analyzed a problem where a roundtrip latency of 7 ms made a fast application totally unusable. Look for lots of small packets on the analyzer. Typically a single packet from the client will be followed by a single packet from the server. If this is the case, either an application rewrite is in your future, or it is time to pick up a copy of Citrix....

Category 2 - It just stopped working well.

Packets are being lost between your hub site and the remote site. If this is the case, you will be seeing TCP transport retransmissions. While the Sniffer can see these, it is much easier to tell if they exist by looking at the computers that are talking to each other. Go to each machine, the SQL server and the client and type NETSTAT -S at the DOS prompt. The last section of the displayed information will be TCP Stats. Look for Retransmitted Segments. This indicates how many TCP packets have been retransmitted as a result of packets being dropped.

If you see this number increasing on either the client or the server, you have physical layer errors and you need to start checking all of the router and switch ports between the server and the remote site.

Or, the server is talking a long time to reply to requests. In this case, span the port the server is connected to and begin monitoring the time between a SQL request and the reply. Remember that TCP will acknowledge the packet after 200 ms if the server is not ready to send back data. In a long reply delay you should see -

Source Dest Description
Client SQL Server SQL Request
SQL Server Client TCP Ack
SQL Server Client SQL Reply Data

If this is the case, it is time to talk to the SQL server administrator and see why the server is taking so long to reply. Could be CPU, Memory, needs to be reindexed.