Validating a Forced Plan
If the database in which the query is being run contains any plan guides at
all, SQL Server must check whether any of the plan guides matches the query
being processed. SQL Server hashes the query text and compares it with the hashed
version of the queries for all existing plan guides, to verify a match. If it
finds a match, SQL Server must verify that the plan guide matches the given
query in the specified environment (the batch or module), and doing so takes
extra time. Then the hints themselves need to be evaluated and compared with
the plans that SQL Server would generate on its own. To guarantee that the forced
plan with the hints is actually valid, SQL Server chooses to use only a plan
that it could come up with on its own. The forced plan needs to be one that
was considered, then rejected by the optimizer.
Let's look at an example of a plan that's considered invalid. The plan guide
that Listing 2 creates is based
on the query in the AdventureWorks database that Listing
3 shows. There are indexes on the SalesOrderID column in both the Sales.SalesOrderHeader
and Sales.SalesOrderDetail tables, and the index for Sales.SalesOrderHeader
is unique. SQL Server's optimizer will realize this and determine that each
table can be accessed by using a seek operation. In this situation, SQL Server
2005 won't consider a HASH JOIN to be any use. Nevertheless, SQL Server lets
you create the plan guide in Listing
2 that includes the hint OPTION (HASH JOIN). However, if you then run the
query in Listing 3 expecting that
SQL Server will use the plan guide, you're in for a shock, and you'll get the
unfriendly error message in Figure 2.
The moral here is be careful when using plan guides. Users running the query
who don't know anything about your plan guides will also get that error message,
and they might have no idea what's causing the problem.
Use Plan Guides with Care
In upcoming articles, we'll look at the other two types of plan guides and the
new optimizer hints that you can use with them. Keep in mind, however, that
hinting isn't something you should expect to have to do on every query. Plan
guides are intended for situations where hinting is the only way to get the
desired performance from your queries. Plan guides have overhead of their own,
especially during query optimization, so overuse of them can potentially make
slow performance even slower. But in those cases where plan guides are truly
useful, they can be the best thing that ever happened to your SQL Server applications.