sql server - Query taking more time on local SSMS than remote

Question

When I am executing a select statement with 4 'inner' joins and two 'WHERE' conditions it took 13-15 s in local SSMS (I have executed 5 times). But when I connect the same instance from another server's SSMS and execute the same query it took 5 s to execute first time and then it took 0 s! I am using the same user SA.

Is there any possible explanation for that?

Host instance is SQL 2008 and Remote instance has SQL 2008 R2.

Answer 1

If your query is returning data to display in your local SSMS then this data needs to be transferred from the server to your local SSMS. The time to transfer the data from the server to your local SSMS is included in the execution time. So, the execution time is a combination of executing the script and fetching the data in order to display it.

You might want to "Include Client Statistics" and then review the row "Bytes received from server" in the "Client Statistic" tab of the result window.

In order to verify my assumption you can alter your select only to execute without fetching the data.