Understanding the Sql Server execution time

Question

I'm using an SQL Server 2012 and SET STATISTICS TIME ON to measure the CPU-time for my sql statements. I use this because i only want to get the time the database needs to execute the statement.

When returning large data from a select, i noticed the CPU-time going up pretty high, like using TOP 2000 will need about 400ms, but without it will need about 10000ms CPU-time.

What i'm not sure about is: Is it possible that the CPU-time i get returned includes something like the time it needs to display the millions of rows returned in my Sql Server Management Studio? That would be pretty much of a bad situation.

Update:

The time i want to recieve is the execution time of the sql server without the time needed for the ssms to display the rows. There are several time statistics display in the Client statistics , but after searching for a long time it's really hard to find good references explaining what they are. Any suggestions?

Idea: elapsed time(sql server execution time) - client processing time (Client statistics)

Maybe this is an option?

Answer 1

In a multi-threaded world, CPU time is increasingly less helpful for simple tuning. Execution time is worth looking at.

To see if execution time (elapsed time) spent on displaying results is included you could SELECT TOP 2000 * INTO #temp to compare execution times.

Update:

My quick tests suggest the overhead of creating/inserting into a #temp table outweighs that of displaying results (at 5000). When I go to 50,000 results the SELECT INTO runs more quickly. The counts at which the two become equivalent depends on how many and what type of fields are returned. I tested with:

SET STATISTICS TIME ON
SELECT TOP 50000 NEWID()
FROM  master..spt_values v1, master..spt_values v2
WHERE v1.number > 100
SET STATISTICS TIME OFF
-- CPU time = 32 ms,  elapsed time = 121 ms.


SET STATISTICS TIME ON
SELECT TOP 50000 NEWID() col1
INTO #test
FROM  master..spt_values v1, master..spt_values v2
WHERE v1.number > 100
SET STATISTICS TIME OFF
-- CPU time = 15 ms,  elapsed time = 87 ms.