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

Monitoring performance of a query by SET
STATISTICS XML

SET STATISTICS XML is an XML version of the actual execution plan. It provides all
the information in XML format which we used to get in graphical format in the actual
execution plan.

Getting ready

We are going to use the SELECT query given in the previous recipe's How to do it... section by
replacing SHOWPLAN_XML with SET STATISTICS XML . The main intention to use the same
query from the previous recipe Monitoring performance of query by SET SHOWPLAN_XML
is to see the difference between two execution plan. Generally, if an index and statistics'
histogram is updated, then the execution plan on sampling (estimated execution plan)
and execution plan on real table (actual execution plan) remain the same. If you find any
difference between these two plans, then it is a time to investigate that.

How to do it...

Perform the following steps to use SET STATISTICS XML :

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 XML 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
[Purchasing].[PurchaseOrderDetail] AS POD
ON