does SQL server query performance depend on the speed of network to the client?

My test query runs faster when I do a Remote Desktop connection to the SQL Server box, and open Management Studio from there, than when I connect using Management Studio from remote workstation. The question is: WHY?!

My query does not return any data back. Here's the exact text of my query:

set quoted_identifier off
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls off
set concat_null_yields_null on
set cursor_close_on_com开发者_C百科mit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed 


exec dbo.Test

Where Test is defined as follows:

CREATE PROCEDURE dbo.Test
AS
BEGIN  
 declare @cnt int = 0  
 declare @d as datetime  
 set @d = dateadd(ss,5,GETDATE());  

 WHILE GETDATE() < @d
 BEGIN  
  SET @cnt = @cnt + 1  
 END

 print @cnt  
END

Both queries take 5 seconds to execute, but the local run returns 7 times bigger value of cnt than the remote run.

Looking at profiler, LPC protocol is used locally, while TCP/IP is used for remote connection. I tried named pipes and it is even slower. I thought if query execution does not involve returning data to the client it should not depend on the network connection or the protocol used but apparently it does.

Any ideas why? Can you validate my results?

Every time the SET command is called there is feedback sent to the client.

If you change the code as shown below performance will be a lot better (even local). I get about 9 million loops without and 14 million with the SET NOCOUNT ON. As a general rule you should always switch SET NOCOUNT to on as first statement of a stored procedure.

CREATE PROCEDURE dbo.Test
AS
BEGIN  
 SET NOCOUNT ON
 declare @cnt int = 0  
 declare @d as datetime  
 set @d = dateadd(ss,5,GETDATE());  

 WHILE GETDATE() < @d
 BEGIN  
  SET @cnt = @cnt + 1  
 END

 print @cnt  
END

The SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. More information about DONE_IN_PROC can be found on MSDN

Even when there is no data to be returned, Management Studio has quite a "conversation" with the SQL server instance when running a query. Running SQLMS as an app on the server will virtually always result in faster execution times, because when you remote in to terminal services (RDP), the network is only used for GUI messages. When you're running an instance of SQLMS locally and connecting it to a remote server, that "conversation" between client and server, which involves more data than UI messages, takes place over the LAN, which is guaranteed to be slower than LPC.