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

CPU and memory value may fluctuate significantly
when re-executing the same query with no change
in the base table schema or indexes or even data.

It happens because background applications running on the SQL Server machine
continuously affects the processing time of the under observation query. So these
values are not something we can depend on, at the same time reads remains the same
when a similar query with the same table schema and data is executed multiple times.

Getting ready

Use the SELECT query we have used in previous recipe Monitoring performance of query by
SET STATISTICS XML by adding SET STATISTICS IO .

If there is a difference found in the estimated execution plan and the actual execution plan,
we would definitely like to see what is the current reads status with SET STATISTICS IO
and we can observe the same after we take some action resolving the bottleneck.

How to do it...

SET STATISTICS IO is one of the favorite commands for anybody who is dealing with
performance tuning in SQL Server. Let us see the usage of the same by performing the
following steps:

1. Write down the following query in SSMS from Start |All Programs | SQL Server 2012

| SQL Server Management Studio (SSMS).
Use AdventureWorks2012
GO

SET STATISTICS IO ON
GO

SELECT
POH.PurchaseOrderID
,POH.OrderDate
,EMP.LoginID
,V.Name AS VendorName
,SUM(POD.OrderQty) AS OrderQty
,SUM(POD.OrderQty*POD.UnitPrice) AS Amount
,COUNT_BIG(*) AS Count
FROM
[Purchasing].[PurchaseOrderHeader] AS POH
JOIN