Sql-server – Slow remote SELECT statement due to long “client processing time”, but fast locally

performanceremotesql-server-2008

While connected to our production server (SQL Server 2008, very powerful machine), this SELECT statement takes 2 seconds, spitting back all fields (4 MB of data in total). 

SELECT TOP (30000) *
FROM person
WITH(NOLOCK);

From any other box on the same network (connecting using SQL authentication or Windows Authentication), the same query takes 1 minute, 8 seconds.

I am testing with this very simple statement to illustrate that it's not an indexing problem or query-related problem. (We have performance issues with all queries at the moment…)

The rows come in chunks, and not all at once. I get my first rows instantly, and then wait for over 1 minute for the batches of rows to come in.

Here are the Client Statistics of the query, when it is ran from the remote box:

Query Profile Statistics
  Number of INSERT, DELETE and UPDATE statements 0
  Rows affected by INSERT, DELETE, or UPDATE statements 0
  Number of SELECT statements  2
  Rows returned by SELECT statements 30001
  Number of transactions 0

Network Statistics
  Number of server roundtrips 3
  TDS packets sent from client        3
  TDS packets received from server 1216
  Bytes sent from client         266
  Bytes received from server 4019800

Time Statistics
  Client processing time 72441 ms (72 seconds)
  Total execution time   72441 ms
  Wait time on server replies 0

We can see that the "Client Processing Time" is equal to the total execution time.

Does anyone know what steps I can take to diagnose why the transfer of the actual data is taking a long time?

Is there an SQL configuration parameter that restricts or limits data transfer speed between machines?

Best Answer

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.

Related Question