Slow remote SELECT statement due to long "client processing time", but fast locally

This issue is now resolved.

It was a network problem, and the SQL box was using a 100 MB/s NIC card, instead of a 10 GB/s NIC card...

A network configuration change to use the correct network card has fixed the problem. Now we are getting similar performance for all queries from the Production SQL box and from other boxes on the network.

Thanks everyone for your help.

Your problem is definitely network related, based on your info. As such, it has to be dealt with with network professionals (I am not the one).

Things that might help:

• Faster NIC cards (on SQL server).
• Adding of allocated/specific NIC card/subnet between the servers (web-server and SQL Server).

Is the web-server in the same sub-net as the SQL server?

Are there routers/bridges etc. between them?

Not many possible changes on SQL server:

• Output data is being sent by SQL Server with proprietary MS "TDS protocol".
• Default size of the TDS buffer is 4 KB. See in MSDB: "network packet size Option"
• Compressing the data (with SQL Server or an external application) - depends upon nature of data.

You are using a default size: see your stats: "TDS packets received from server 1216" (4MB/1K=4KB). Yes, size of the TDS buffer can be changed: see in google: "TDS protocol batch size"

Good discussion on the topic: "does sql's network packet size really determine round trip traffic?"

However, changing the TDS package size will (inevitably) have unpredictable effects and should only be used in production in exceptional cases.

Changing of architecture or introduction of caching of data on mid-tier would also help.

At initial reading it sounds like you are experiencing some network latency issues. Have you looked at some of the Network Perfmon counters? Those may give you some indication of what is going on with the network.

Quote from What Perfmon counters should I monitor and what each of them mean?

NETWORK IO

To measure network I/O, you can use the following counters:

Network InterfaceBytes Total/sec

Threshold: Sustained values of more than 80 percent of network bandwidth.

Significance: This counter indicates the rate at which bytes are sent and received over each network adapter. This counter helps you know whether the traffic at your network adapter is saturated and if you need to add another network adapter. How quickly you can identify a problem depends on the type of network you have as well as whether you share bandwidth with other applications.

Network InterfaceBytes Received/sec

This counter indicates the rate at which bytes are received over each network adapter. You can calculate the rate of incoming data as a part of total bandwidth. This will help you know that you need to optimize on the incoming data from the client or that you need to add another network adapter to handle the incoming traffic.

Network InterfaceBytes Sent/sec

This counter indicates the rate at which bytes are sent over each network adapter. You can calculate the rate of incoming data as a part of total bandwidth. This will help you know that you need to optimize on the data being sent to the client or you need to add another network adapter to handle the outbound traffic.

ServerBytes Total/sec

This value should not be more than 50 percent of network capacity.

This counter indicates the number of bytes sent and received over the network. Higher values indicate network bandwidth as the bottleneck. If the sum of Bytes Total/sec for all servers is roughly equal to the maximum transfer rates of your network, you may need to segment the network.

Processor% Interrupt Time

This counter indicates the percentage of time the processor spends receiving and servicing hardware interrupts. This value is an indirect indicator of the activity of devices that generate interrupts, such as network adapters.

Network Interface(*)Output Queue Length

This counter checks to see how many threads are waiting on the network adapter. If there are a lot of threads waiting on the network adapter, then the system is most likely saturating the network I/O most likely due to network latency or network bandwidth.

Output Queue Length is the length of the output packet queue (in packets). If this is longer than two, there are delays and the bottleneck should be found and eliminated, if possible. Since the requests are queued by the Network Driver Interface Specification (NDIS) in this implementation, this will always be 0.