Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 390

NonDiscountSales = (OrderQty * UnitPrice),
Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product AS Prod
INNER JOIN Sales.SalesOrderDetail AS SODetail
ON Prod.ProductID = SODetail.ProductID ',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MAXDOP 2)'

4. To assure that the plan guide is created, let us query against a system catalog:

--Obtain the created plan guides on the SQL instance
SELECT * from sys.plan_guides
GO

5. The preceding query displays the newly created plan guide along with information

about create_date, scope_type, and hints that are used.

6. Coming back to our requirement of repeating the execution of the same query

without adding any hints can be done using the following code:
--Clearing the CACHE
DBCC FREEPROCCACHE
go
--Repeating the query execution with statistics
SET STATISTICS IO ON
GO
SET FORCEPLAN 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 FORCEPLAN OFF
GO