Monitoring with Execution Plans - Microsoft SQL Server 2012 Performance Tuning - page 111

[Purchasing].[PurchaseOrderHeader] AS POH
JOIN
[Purchasing].[PurchaseOrderDetail] AS POD
ON
POH.PurchaseOrderID = POD.PurchaseOrderID
JOIN
[HumanResources].[Employee] AS EMP
ON
POH.EmployeeID=EMP.BusinessEntityID
JOIN
[Purchasing].[Vendor] AS V
ON
POH.VendorID=V.BusinessEntityID
GROUP BY
POH.PurchaseOrderID
,POH.OrderDate
,EMP.LoginID
,V.Name
GO
SET STATISTICS TIME OFF
GO
2. The SELECT query will be executed and the output is displayed in the Results
panel and the result of SET STATISTICS TIME will appear in the Message tab.
The following is screenshot illustrates this:
How it works...
To check the query execution time, are you going to look at your watch after start execution
and count it till end of execution of the query? This is not the best way. The query execution
time from start to end, according to your watch, may misguide you as query execution is
affected by many different things such as load on the server, usage of the SQL Server
instance, and many more things. To check how much CPU resources (CPU time) is
consumed by the query we can use the SET STATISTICS TIME option.
More CPU resources means slow query. It would be a nice combo to use SET STATISTICS IO
and SET STATISTICS TIME together, so that you can get information regarding how much
heavy load on CPU is coming from SET STATISTICS TIME and what table(s) is creating that
load from SET STATISTICS IO .