Advanced Query Tuning Hints and Plan Guides - Microsoft SQL Server 2012 Performance Tuning - page 368

Click on the Execution Plan tab in the result pane and observe the execution plan
for the preceding query. Note that for ProductID 800 , the query optimizer has
performed an index scan operation. The execution plan should look like the
following screenshot:

5.

6. We want an execution plan to be generated, which performs an index seek operation.

Let's execute the following query, which retrieves the sales order details for
ProductID equal to 806 :
USE AdventureWorks2012
GO

--Retrieving Sales Order Details for
--Product = 806 to in order to generate
--optimum Execution Plan with Index Seek
--operation.
SELECT
SalesOrderID
,SalesOrderDetailID
,ProductID
,OrderQty
,UnitPrice
,UnitPriceDiscount
,LineTotal
FROM Sales.SalesOrderDetail
WHERE ProductID = 806
GO

7. The following script will retrieve an execution plan for the preceding query in XML

format by querying Dynamic Management Views (DMVs) and store it in a variable.
It also creates a SQL plan guide named MySQLFixedPlanGuide , by using the
execution plan. Run the following script:
USE AdventureWorks2012
GO