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.