Like all applications SQL Server uses processor (amongst other things) and knowing how much processor a particular T-SQL statement is using is very useful, particularly during performance turning that code.
SET STATISTICS TIME does just that.
So what exactly is SET STATISTICS TIME
SET STATISTICS TIME is a setting you can turn on in your query window (you can also turn it on in options), the idea is that you can turn it on, run your code then turn it off again. This then produces a result set detailing the CPU time the query took. So lets say I want to know how much CPU time my query to select a list of all the databases on my server is taking it would look like this;
SET STATISTICS TIME ON
–This is where your T-SQL code would go
SELECT name FROM sys.databases;
SET STATISTICS TIME OFF
You guessed it, the ‘ON’ line turns it on, and the ‘OFF’ line turns it off. If you don’t turn it off it will remain on until you close the query window (which closes the SPID or Session Process ID).
Where are my results?
So you’ve run the command and all you see are a list of names of the databases in the results pane, well that’s because the results we care about are actually in the ‘messages’ tab so go ahead and click on that. You should see something that looks like this;
SQL Server parse and compile time:
CPU time = 47 ms, elapsed time = 48 ms.
(57 row(s) affected)
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 10 ms.
So what’s going on here?
The results are broken in to two parts for each statement, the first is the parse and compile time, this is basically where SQL Server is going through the statement to check for any parsing errors and then compiling the query to a plan. So in the example above we can see it took 47ms of CPU time to parse and compile the command.
The second part of the results is the actual execution times themselves, so that’s how long it took to run the select statement itself.
Each section has two times, CPU and elapsed, both are in millisecond, the difference between the two is the CPU time is the amount of time the CPU spent doing work, the elapsed time is the total time from start to finish. Our query is nice and simple but if you imagine a query that has to get a lot of data from disk then you could see the CPU time being a few milliseconds but the elapsed time being much higher as the data had to fetched from disk. Likewise CPU time could be higher than elapsed time if the query was run across multiple cores due to the query running across multiple cores.
The elapsed time is prone to variation due to other factors, disk I/O etc whereas the CPU time should be relatively constant and allow you to judge performance.
So there you have it you can now tell the CPU footprint of any T-SQL which runs on your server. Next time we’ll have a look at the I/O footprint using SET STATISTICS IO.