patternMinor
Slow remote SELECT statement due to long "client processing time", but fast locally
Viewed 0 times
fastduestatementbutlocallylongslowtimeclientremote
Problem
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).
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:
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?
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 0We 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?
Solution
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.
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.
Context
StackExchange Database Administrators Q#17265, answer score: 8
Revisions (0)
No revisions yet.