Monitoring performance of a query by SET STATISTICS TIME

SET STATISTICS TIME is also one of the light weight weapons which can be used to get some of the important information regarding CPU resources consumed by the batch we have executed.

Getting ready

In order to know the real CPU usage statistics of the query you execute, we will use the same SELECT query that we have used in the previous recipe Monitoring performance of query by SET STATISTICS IO by adding SET STATISTICS TIME.

After comparing the different execution plan, check the statistics IO of the query, it is now time for further digging by looking at CPU resources consumed by query or batch which displays the number of milliseconds required by the CPU to parse, compile, and execute query or batch. This time doesn't include the time which SQL Server 2012 spends to deliver the result set to client.

Every database professional wants to tune the query and make it faster than before, but how could you measure whether query is working...