How to test the speed of a query without downloading the actual data?

I have a multiple result-set store procedure on a remote Sql Server 2008. Currently, the procedure is returning a rather large result set that is to be parsed and used on a website that is hosted on the same server as the datab开发者_开发技巧ase.

When Sql Server is displaying the time it take to get the data, most of that time is spent in actually download the data from the remote server to my local development machine.

How can I better test the actual speed of a query by removing the download time associated to the data returned by that query?

I want to get the execution time of a query as if the database server was local to my computer.

You can inject the data to a temporary table. You're adding a bit of overhead to the process but negligible compared to network latency.

SELECT * INTO #TempTable FROM [Table]

INTO Clause (Transact-SQL)

To remove network issues from consideration and just look at execution stats I often assign the results to variables.

So instead of

SELECT name,type, COUNT(*)
FROM master..spt_values
GROUP BY name,type

For example I would use

DECLARE @name nvarchar(35), @type nchar(3), @count int

SELECT @name= name, @type = type, @count = COUNT(*)
FROM master..spt_values
GROUP BY name,type

There is an option in SSMS to discard results after execution but that also discards the output of SET STATISTICS IO ON etc. which isn't that useful.

SentryOne Plan Explorer - a free download - lets you do this when you generate an actual execution plan from within the tool. It runs the query on the server and discards the results, so the timings indicated are purely the time on the server. For a heap more info on Plan Explorer:

• SentryOne Plan Explorer
• SentryOne Plan Explorer 3.0 Demo Kit