Why is the speed of executing statements dependent on the network connection?

Question

It looks like the speed of executing T-SQL is dependent on the latency of the network connection against the server. I assumed that if SQL Server has nothing to report back to the client about, it will just execute away until it is done but testing shows another story.

create procedure UselessLoop
  @I int
as
declare @D datetime = getdate()
while @I > 0 set @I -= 1
print datediff(millisecond, @D, getdate())

exec UselessLoop 100000

Server    Milliseconds
local     53
nearby    63
faraway   660

exec UselessLoop 1000000

Server    Milliseconds
local     546
nearby    640
faraway   6183

The tests are executed against the same server from different computers using SSMS. Local is executed from the Server, nearby is on the same local network and faraway is executed from another office 500 km away connected with 1 gigabit fiber.

There is obviously some communication going on between SQL Server and the client that directly is dependent on the number of statements executed.

I used Wireshark to look at what is transported and I can't say that I understand that much but it was a tcp.stream exchanging a total of 26 MB in 22740 packets.

How about a useless function instead?

create function dbo.UDFUselessLoop(@I int)
returns int
as
begin
  declare @D datetime = getdate()
  while @I > 0 set @I -= 1
  return datediff(millisecond, @D, getdate())
end

print dbo.UDFUselessLoop(1000000)

It executes in 406 milliseconds regardless of where it is executed from. Looks like there is no communication with the client in the loop.

Answer 1

There is obviously some communication going on between SQL Server and the client that directly is dependent on the number of statements executed.

Yes, there is. By default, SQL Server sends a TDS DONE_IN_PROC message after every statement in a stored procedure. The message communicates status and row count information for the completed statement to the client.

You can suppress the sending of these messages using the T-SQL command:

SET NOCOUNT ON;

The following is an extract (my emphasis) from the Books Online entry for this command:

For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

Related Q & A: Why does a simple loop result in ASYNC_NETWORK_IO waits?