fix formatting

Source Link

edit approved Nov 28, 2017 at 13:20

1.6k
1
10
21

Select Slow remote SELECT statement takes long remotely due to long "client processing time", but shortfast locally

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

selectSELECT topTOP (30000) *
fromFROM person with
WITH(nolockNOLOCK);

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 fromof the query (ran, when it is ran from the remote box):

Client Execution Time   15:30:48
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?

Select statement takes long remotely due to long "client processing time", but short locally

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 from the query (ran from the remote box):

Client Execution Time   15:30:48
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   (72 seconds)
  Total execution time  72441
  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?

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

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?

format code and statistics

Source Link

edited Nov 28, 2017 at 10:55

Michael Green

25.1k
13
53
98

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 from the query (ran from the remote box):

Client Execution Time   15:30:48
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   (72 seconds)
  Total execution time  72441
  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?

While connected to our production server (SQL 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 from the query (ran from the remote box):

Client Execution Time   15:30:48
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   (72 seconds)
  Total execution time  72441
  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?

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 from the query (ran from the remote box):

Client Execution Time   15:30:48
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   (72 seconds)
  Total execution time  72441
  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?

format code and statistics

Source Link

edit approved Nov 28, 2017 at 10:55

Oreo

1.6k
1
10
21

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

select top 30000 * from person with(nolock)

select top 30000 *
from person with(nolock);

From any otherother box on the same network (connecting using SQL authentication or windows authenticationWindows Authentication), the same query takes 1 minute, 8 seconds1 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 from the query (ran from the remote box):

Client Execution Time 15:30:48
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 (72 seconds)
Total execution time 72441
Wait time on server replies 0

Client Execution Time   15:30:48
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   (72 seconds)
  Total execution time  72441
  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?

Thanks in advance!

While connected to our production server (SQL2008. 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)