CPU Time
Another important performance measurement is the amount of CPU your query needs to return a result set. In the prior section, CPU time was displayed for each statement in a batch when I use the “SET STATISTICS TIME ON” statement. So this is the first method you can use to measure CPU consumption of a TSQL statement. But once again, how do you measure CPU for multiple statements, or different chunks of code.
SQL Server provides the @@CPU_BUSY system variable that can be used to measure CPU consumption. Here is the definition that Books Online has for this variable:
Returns the time that SQL Server has spent working since it was last started. Result is in CPU time increments, or “ticks,” and is cumulative for all CPUs, so it may exceed the actual elapsed time. Multiply by @@TIMETICKS to convert to microseconds.
Below is a method where you can use @@CPU_BUSY to measure the amount of CPU used by SQL Server during for each execution of my loops from my prior example. Now keep in mind since @@CPU_BUSY contains the amount of CPU used since SQL Server started, it contains all the CPU used by all user and system queries run against server. Therefore this method is only accurate in measuring CPU if you are the only one running SQL Server commands on the system, and there are no back ground system queries running :
As you can see I measured CPU milliseconds used for both the first and second loop. Just to let you know I ran this query a number of different times and got a number of different timings for each loop execution. So keep in mind that using this method is impacted by other things running in SQL Server. I would say that this method is not all that reliable for measuring actual CPU usage, but yet it is still a way to gauge CPU consumption if you have a standalone system with very little activity.
A more accurate way to measure CPU usage is by using SQL Server profiler. This is done by including the CPU column on the different completed events. Here is an example of how I used SQL Server profiler to capture the amount of CPU used for the above looping batch:
Here I used the SQL:BatchCompleted event to capture the CPU. By default SQL Profiler displays the duration in milliseconds. By using the “Tools – Options” dialog box you can change this display into microseconds. Note this method only measured the complete batch and not the duration of each loop within my batch.