Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 389

1. Use the SSMS tool to open a New Query window and execute the following TSQL:

SET STATISTICS IO ON
GO
SET SHOWPLAN_ALL ON
GO

USE AdventureWorks2008R2;
GO
SELECT Prod.Name AS ProductName,
NonDiscountSales = (OrderQty * UnitPrice),
Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product AS Prod
INNER JOIN Sales.SalesOrderDetail AS SODetail
ON Prod.ProductID = SODetail.ProductID
ORDER BY ProductName,Discounts ASC;
GO

SET STATISTICS IO OFF
GO
SET SHOWPLAN_ALL OFF
GO

2. The graphic execution plan is displayed as follows that shows an operation involving

parallelism and scanning a nonclustered index, entirely on a range:

3. Only for this time, we need to avoid the parallelism without modifying the query text.

So, how to accomplish this task without touching the actual query text? Let us create
a plan guide as follows:
--Create a Plan guide to use MAXDOP hint
EXEC sp_create_plan_guide
@name = N'SalesOrderDetail_Product_Use_DegOfParallelism',
@stmt = N'SELECT Prod.Name AS ProductName,