Queries run extremely fast locally, extremely slow on remote machines

We have a query that take takes anywhere from 24-38 seconds to run when you run it from SQL Server Management Studio from a remote machine, but <1 second to run when it is run from the machine locally using SQL Server Management Studio.

SQL Server 2005 Windows Server 2003 R2 Standard Edition Virtualized on VMWare 3.5.0 w/ 4GB of Ram and a Single CPU.

I'll also add that the same query runs slowly from apps as well -- not just from the SQL Mgmt Studio.

Any idea on why it might run super fast locally but super slow on remote machines?

Edit: Does SQL Server handle results caching differently? I ask this because the vendor whose software was causing the problem added indexes to the tables which caused the results to show up ask quickly remotely as they did locally. I had used the SQL Profiler to look at the time that queries were taking to complete and the remote ones were definitely taking longer to complete and now they seem to be taking the same time after adding indexes.

Solution 1:

My expectation would be one of the following:

Lots of data is being returned and it's just taking that long for the data to cross the wire.
Performance issue with the remote machine.

Click the toolbar button in the query window "Include client statistics" and have a look at what gets returned after you run the query. You can see how many bytes were received from the server, execution time, etc.

Client Execution Time                                   12:08:38
Query Profile Statistics                                                                        
  Number of INSERT, DELETE and UPDATE statements        0
  Rows affected by INSERT, DELETE, or UPDATE statements 0
  Number of SELECT statements                           3
  Rows returned by SELECT statements                    15
  Number of transactions                                0
Network Statistics                                                                      
  Number of server roundtrips                           3
  TDS packets sent from client                          3
  TDS packets received from server                      7
  Bytes sent from client                                240
  Bytes received from server                            21306
Time Statistics                                                                 
  Client processing time                                31
  Total execution time                                  61093
  Wait time on server replies                           61062

Edit:

Your comment says that it's only returning 39 rows, so it's probably not data transfer between the client and the server.

I'd consider running SQL Profiler against the server filtered to your client and see what might be going on. Here's a couple of nice articles on using SQL Profiler:

http://msdn.microsoft.com/en-us/library/ms187929.aspx
http://www.eggheadcafe.com/articles/sql_server_profiler.asp

Solution 2:

By default SQL Server connects using shared memory when making a local connection and then either TCP/IP or Named Pipes when connecting remotely. To test if it's something related to the protocol being used try connecting locally but force it to use TCP/IP or Named Pipes.