CPU Cost

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

GO

–This is where your T-SQL code would go

SELECT name FROM sys.databases;

SET STATISTICS TIME OFF

GO

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.

Advertisements
About

I am SQL developer/DBA working in London, I have a passion for all things tech, but my real interests are in databases, mainly SQL Server, Postgres and lately even a little bit of Ubuntu and MongoDB.

Posted in Development, SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Recent Comments
will on SQL Server’s DAC

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3 other followers

%d bloggers like this: