Microsoft® SQL Server™ 2005 Administrator's Companion

Plan Guides

As we’ve seen so far, hints provide a powerful option for influencing the behavior of the SQL Sever database engine and, in a way, give you almost full control over how a query is executed. While a powerful option, the hints are of little use for queries originating in applications which you do not have the code and therefore cannot modify the query to add the hints. For example, you could have the following query that originates in a third-party CRM application and exhibits poor performance due to an incorrect selection of the join type by the optimizer:

SELECT pc.ContactID, FirstName, LastName, EmailAddress, CreditCardID
FROM Person.Contact pc, Sales.ContactCreditCard ccc
WHERE pc.ContactID = ccc.ContactID
AND pc.ContactID < 15;