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

Getting ready

I have already mentioned in the introduction section that the execution plan is the obvious
first step when we start looking into any performance issue in the query. Many times, in a
community portal, I have been asked a question regarding query performance tuning but in
that case I neither have access to the database nor have the server access of the person
who has asked the question. So, I usually ask them to post the execution plan in XML format
because it happens many times that the query has a big execution plan and it is not possible
to capture complete graphical execution plan in one screen.

How to do it...

SET SHOWPLAN_XML is small but powerful, let us see the usage 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 SHOWPLAN_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
POH.PurchaseOrderID = POD.PurchaseOrderID
JOIN
[HumanResources].[Employee] AS EMP
ON
POH.EmployeeID=EMP.BusinessEntityID
JOIN
[Purchasing].[Vendor] AS V
ON