Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
How much data is being returned? –  Spencer Ruport Jul 14 '09 at 19:11
    
Are all machines using TCP/IP or Named Pipes -- or is the local (fast) one named pipes and everyone else TCP? –  Chris_K Jul 14 '09 at 19:15
    
About 39 rows of data. No blobs, it returns enough info to populate a table for further selection. I'd have to say about 5-6k of data max. –  nvahalik Jul 14 '09 at 19:15
    
Chris, how would I check to see how it's connecting? Is that in the DSN or some other area? –  nvahalik Jul 14 '09 at 19:16
    
This may help (if it is indeed a connection type issue): technet.microsoft.com/en-us/library/ms190611.aspx –  Chris_K Jul 14 '09 at 19:36
show 2 more comments

3 Answers

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.

  1. open Management Studio
  2. Click Connect->Database Engine
  3. Click Options
  4. Under Connection Properties change Network Protocol

Does your query still run just as slow?

Also when you connect remotely is the initial connection slow to connect? My thought here is that it could be authentication.

share|improve this answer
add comment

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:

share|improve this answer
add comment

How is the performance when you try to connect to the machine in another way, like remote desktop etc? If everything is slow when you try to reach the the machine you may have some network issues, like a broken network card/cable, router/firewall issues etc. (I have seen a couple of servers with 10Mbit mode on a Gigabit network card :D )

Do you use the same user when you run from the local server as when you do it remote? Is it an AD account or SQL account?

Look for waittypes in Sys.dm_os_waiting_tasks

http://technet.microsoft.com/en-us/library/cc966413.aspx

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.