How do I differentiate execution time from the total amount of time it took to run and return rows?

When I run a query which returns millions of rows from within Sql Management tools it looks like the query executes instantly. There is virtually no execution time as far as I can tell. What makes the query take time to complete is returning all the rows. This got me thinking I've done a good job! But not so fast... As I look at the query in the profiler tool, it开发者_开发知识库 states that the query used 7600 CPU. Duration was 15000.

I'm not sure I know how to interpret these stats. On one hand, the query seems to run fast but the profiler report makes me think otherwise. How come the query is executed instantly in Mgmt Tools? There obviously should be some kind of delayed execution as far as I can tell: at least 7600 ms. I have to wait longer than both the cpu and the duration stats when I run the query in mgmt tools for it to complete the query.

---

it looks like the query executes instantly It might be that the query plan allows to start returning the rows quickly.
For example, if you do SELECT * FROM a_large_table you will see some rows immediately, but retrieval of the whole resultset will take some time. What is the actual execution time reported by Mgmt Studio (shown in the status bar after the query is complete)?

If you want to test the query performance without retrieving data to the client, you can do SELECT INTO #temp_table. This would require some additional I/O, but would still give you a rather good estimate of the execution time.

UPD.
You could also run something like SELECT COUNT(*) FROM (<your query here>) or SELECT SUM(<some field>) FROM (<your query here>) - with some luck, it will make the server execute the query and aggregate the result, basically doing the same work plus a little extra. But it is very easy to skew the results this way - query optimizer is smart, and you need to be very careful to be sure you are measuring what you want to measure (because measuring a query with a different execution plan makes no sense at all).

I suggest you to think again on what you want to measure and why. In any real-life scenario you are not interested in "pure" query duration - because you never want to discard the query result (the result is why you need this query in the first place, right?). So you either need to return the result to the client, or store it somewhere, or join it with another table and so on - and usually you want to measure query execution including the time used for processing its result.

One final notice. If you hope you can somehow force the server to execute this query in 1 second because you think that server does nothing for other 13 seconds, you are wrong.. As they say, SELECT ain't broken.
What might help is query optimization - and for a single query a profiler won't help you much with it. Analyze the query plan, tune your table structure, try to rewrite the query, post another question on SO if in trouble.