Professional SQL Server® 2005 Performance Tuning

15.11. Avoiding Changing Code to Fix Issues

One of my other favorite new features of SQL 2005 is the ability to use plan guides. Plan guides give DBAs the ability to add hints to queries without changing the physical query. Plan guides can be used to force query plans, supply join hints, or restrict the use of parallel processing to a specific number of processors. Plan guides are perfect for a production scenario where queries that are performing poorly cannot be changed due to restrictions posed by the application vendor.

15.11.1. The Query

Here is a query that could benefit from the use of Plan Guides. The following query returns 604,000 rows, and you want that query to return the first 40 rows as soon as possible so that the user can start seeing the data before having to wait for all 604,000 rows to be returned.

SELECT A.RevisionNumber,A.OrderDate,A.DueDate,A.ShipDate,A.Status,
B.OrderQty,B.ProductId,
C.AccountNumber,C.CustomerType,
D.FirstName,D.MiddleName,D.LastName,D.EmailAddress,
E.AddressLine1,E.AddressLine2,E.StateProvinceId,E.PostalCode
FROM
SALES.SALESORDERHEADER A

INNER JOIN SALES.SALESORDERDETAIL B ON A.SALESORDERID=B.SALESORDERID
INNER JOIN SALES.CUSTOMER C ON A.CUSTOMERID = C.CUSTOMERID
INNER JOIN SALES.INDIVIDUAL F ON C.CUSTOMERID = F.CUSTOMERID
INNER JOIN PERSON.CONTACT D  ON F.CONTACTID = D.CONTACTID
INNER JOIN SALES.CUSTOMERADDRESS G ON A.CUSTOMERID = G.CUSTOMERID
INNER JOIN PERSON.ADDRESS E ON G.ADDRESSID = E.ADDRESSID

15.11.2. The Query Plan

The following ...