Problem

You are joining two tables. The optimizer has made a poor choice on the approach to take in executing the join. You want to override the optimizer and exert control over the mechanism used to perform the join.

Solution

Apply one of the join hints from Table 22-1 in the section “How It Works.” For example, the following is a query with no hints that will trigger a nested-loops join operation:

SELECT p.Name,
        r.ReviewerName,
        r.Rating
FROM Production.Product p
        INNER JOIN Production.ProductReview r
         ON r.ProductID = p.ProductID;

Figure 22-1 shows the relevant part of the execution plan. You can see that the optimizer has chosen a nested loops join.

You can force one of the other join types by placing the relevant hint from Table 22-1 between the words INNER and JOIN. The following example uses INNER HASH JOIN to force a hash join:

SELECT p.Name,
        r.ReviewerName,
        r.Rating
FROM Production.Product p
        INNER HASH JOIN Production.ProductReview r
         ON r.ProductID = p.ProductID;

Figure 22-2 shows the new execution plan, this time with a hash join operation.

How It Works

Table 22-1 shows the join hints at your disposal. The table also provides some general guidance on the situations in which each join method is optimally used. Generally the optimizer will make a reasonable choice. You should think about overriding the optimizer only when you have good reason and no other alternative.

The first solution query generates an execution plan showing a nested loops join. The second solution query shows the HASH hint from Table 22-1 being used to force a hash join.

Be careful and thoughtful in applying hints. Don’t get carried away. Once you apply a hint, you freeze that hint’s aspect of the execution plan until such time as you later change the hint or remove it. Future improvements to the optimizer and future join methods won’t ever get applied, because your hint forces the one approach you’ve chosen.

Problem

Normally SQL Server saves the execution plan from a query so as to reuse that plan the next time the query is executed, perhaps with a differing set of values. Your data is skewed, and plans for one set of values may work poorly for others. You want the optimizer to generate a new plan for each execution.

Solution

Submit your query using the RECOMPILE query hint. Typically you will want to use this RECOMPILE query hint within a stored procedure—so that you can control which statements automatically recompile—instead of having to recompile the entire stored procedure. Here’s an example:

DECLARE @CarrierTrackingNumber nvarchar(25) = '5CE9-4D75-8F';
SELECT SalesOrderID,
        ProductID,
        UnitPrice,
        OrderQty
FROM Sales.SalesOrderDetail
WHERE CarrierTrackingNumber = @CarrierTrackingNumber
ORDER BY SalesOrderID,
        ProductID
OPTION (RECOMPILE);

This returns the following:

How It Works

This example uses the RECOMPILE query hint to recompile the query, forcing SQL Server to discard the plan generated for the query after it executes. With the RECOMPILE query hint, a new plan will be generated the next time the same or a similar query is executed. The hint goes in the OPTION clause at the end of the query.

OPTION (RECOMPILE)

You may decide you want to take this recipe’s approach when faced with a query for which query plans are volatile, in which differing search condition values for the same plan cause extreme fluctuations in the number of rows returned. In such a scenario, using a compiled query plan may hurt, not help, query performance. The benefit of a cached and reusable query execution plan (the avoided cost of compilation) may occasionally be outweighed by the actual performance of the query as it is executed using the saved plan.

Problem

You want to execute a query without being blocked and without blocking others. You are willing to risk seeing uncommitted changes from other transactions.

Solution #1: The NOLOCK Hint

Apply the NOLOCK table hint, as in the following example:

SELECT DocumentNode,
        Title
FROM Production.Document WITH (NOLOCK)
WHERE Status = 1;

Solution #2: The Isolation Level

Another approach here is to execute a SET TRANSACTION statement to specify an isolation level having the same effect as the NOLOCK hint. Here’s an example:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT DocumentNode,
        Title
FROM Production.Document
WHERE Status = 1;

How It Works

The crux of this example is the WITH clause, which specifies the NOLOCK table hint in parentheses.

WITH (NOLOCK)

The example in Solution #1 returns the DocumentID and Title from the Production.Document table where the Status column is equal to 1. The NOLOCK table hint prevents the query from placing shared locks on the Production.Document table. You can then read without being blocked or blocking others (although you are now subject to reading uncommitted and possibly inconsistent data).

The example in Solution #2 accomplishes the same thing by setting the transaction isolation level in a separate statement. Doing that avoids the need for a hint in the query. The command affects all subsequent transactions in the session.

Your transaction isolation level options are as follows:

• READ UNCOMMITTED: You can read uncommitted changes from other transactions.
• READ COMMITTED: You see only committed changes from other transactions.
• REPEATABLE READ: You are not able to read data that has been modified, but not yet committed, by other transactions.
• SNAPSHOT: You see all data as it existed at the precise moment the transaction began.
• SERIALIZABLE: Transactions are guaranteed to be serializable, meaning they can be played back in sequence. You won’t be able to read uncommitted data from other transactions. Other transactions will not be able to modify data that you have read, nor will other transactions be allowed to insert new rows that have key values falling into any of the ranges selected by your transaction.

READ COMMITTED is the default level. If you aren’t reasonably familiar with what the various levels mean, take the time to read the Books Online section on “Transaction Statements.” The URL for the 2012 version of that section is http://msdn.microsoft.com/en-us/library/ms174377.aspx.

Problem

You are executing a query that you know is best executed via an index seek operation, yet the optimizer persists in choosing to scan the index. You’ve done your due diligence by updating statistics. You are still getting the scan operation.

Solution

Specify the FORCESEEK hint, which is available from SQL Server 2008 onward. Here’s an example:

SELECT DISTINCT
        TransactionID,
        TransactionDate
FROM Production.TransactionHistory WITH (FORCESEEK)
WHERE ReferenceOrderID BETWEEN 1000 AND 100000;

You also have the option to designate which index should be used. Here’s an example:

SELECT DISTINCT
        TransactionID,
        TransactionDate
FROM Production.TransactionHistory WITH (FORCESEEK,
        INDEX (IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID))
WHERE ReferenceOrderID BETWEEN 1000 AND 100000;

Your query will now seek directly to the index keys needed to resolve the query.

How It Works

Bad query plans happen for several reasons. For example, if your table data is highly volatile and your statistics are no longer accurate, a bad plan can be produced. Another example would be a query with a poorly constructed WHERE clause that doesn’t provide sufficient or useful information to the query optimization process.

If the intent of your query is to perform a singleton lookup against a specific value and instead you see that the query scans the entire index before retrieving your single row, the I/O costs of the scan can be significant (particularly for very large tables). You may then consider using the new FORCESEEK table hint. FORCESEEK can be used in the FROM clause of a SELECT, UPDATE, or DELETE.

The solution example invokes the hint by placing the WITH keyword into the query, followed by the hint name in parentheses:

FROM Production.TransactionHistory WITH (FORCESEEK)

Using the hint overrides the query’s original clustered index scan access path.

You further narrow down the instructions by designating the INDEX hint as well, forcing the seek to occur against the specific index you name. Here’s an example:

FROM Production.TransactionHistory WITH (FORCESEEK,
     INDEX (IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID))

The INDEX hint is followed by the name of the index within parentheses. You can also specify the index number.

Problem

The optimizer underestimates the number of rows to be returned from a table and chooses to execute a seek operation against an index on the table. You know from your knowledge of the data that an index scan is the better choice.

Solution

Specify the FORCESCAN hint, which is available from SQL Server 2008 R2 SP1 onward. Here’s an example:

SELECT DISTINCT
        TransactionID,
        TransactionDate
FROM Production.TransactionHistory WITH (FORCESCAN)
WHERE ReferenceOrderID BETWEEN 1000 AND 100000;

If you like, you can specify the index to scan.

SELECT DISTINCT
        TransactionID,
        TransactionDate
FROM Production.TransactionHistory WITH (FORCESCAN,
        INDEX (PK_TransactionHistory_TransactionID))
WHERE ReferenceOrderID BETWEEN 1000 AND 100000;

Your query will now scan the specified index to resolve the query.

How It Works

The FORCESCAN hint is the complement of FORCESEEK described in Recipe 22-4. The hint applies to SELECT, INSERT, and UPDATE statements. With it, you can specify that you want an index seek operation to take place when executing a query.

Problem

You want the optimizer to favor execution plans that will return some number of rows very quickly. For example, you are writing a query for an interactive application and would like to display the first screen full of results as soon as possible.

Solution

Place the FAST n hint into the OPTION clause at the end of your query. Specify the number of rows that you would like to be returned quickly. Here’s an example:

SELECT ProductID, TransactionID, ReferenceOrderID
FROM Production.TransactionHistory
ORDER BY ProductID
OPTION (FAST 20);

How It Works

Specify FAST n to alert the optimizer to your need for n rows to come back very quickly. In theory, the optimizer then favors execution plans yielding quick initial results at the expense of plans that might be more efficient overall.

An example of a typical trade-off would be for the optimizer to choose a nested loops join over a hash join or some other operation. Figure 22-3 shows the execution plan for the solution query when that query is executed without the hint. Figure 22-4 shows the plan with the hint included. You can see the nested loops operation in the second figure.

In the case of the solution query, the hint FAST 20 causes the optimizer to drive the query from an index on the ProductID column. By doing so, the query engine is able to begin immediately returning the rows in sorted order, because the query engine can simply read the index in order. The trade-off, which you can see when you compare the two plans as shown in Figures 22-3 and 22-4, is that each access of the one index is accompanied by a key lookup into the table to return the other two column values. Figure 22-4’s plan is probably more costly, but it does begin to return rows immediately. Figure 22-3’s plan might be more efficient, but no rows can be returned until the table has been scanned and the sort operation has been completed.

FAST n is no guarantee that you’ll get n rows any faster than before. Results depend upon available indexes and join types and upon the various possibilities that the programmers writing the optimizer happened to think about ahead of time. Check your query’s execution plan before and after adding the hint to see whether doing so made a difference.

Problem

You are joining two or more tables. You want to force the order in which the tables are accessed while executing the join.

Solution

List the tables in the FROM clause in the order in which you want them to be accessed. Then specify FORCE ORDER in an OPTION clause at the end of the query. Here’s an example:

SELECT PP.FirstName, PP.LastName, PA.City
FROM Person.Person PP
        INNER JOIN Person.BusinessEntityAddress PBA
         ON PP.BusinessEntityID = PBA.BusinessEntityID
        INNER JOIN Person.Address PA
         ON PBA.AddressID = PA.AddressID
OPTION (FORCE ORDER)

The join order will now be Person to BusinessEntityID and then will come the join to Address.

How It Works

Specifying FORCE ORDER causes tables to be joined in the order listed in the FROM cause. Figures 22-5 and 22-6 show the effect of the hint on the solution query. Without the hint (Figure 22-5), the first two tables to be joined are Address and BusinessEntityAddress. With the hint (Figure 22-6), the first two tables are Person and BusinessEntityAddress, matching the order specified in the FROM clause.

Problem

You aren’t happy with the optimizer’s index choice. You want to force the use of a specific index in connection with a given table.

Solution

Specify the INDEX hint at the table level. For example, the following is another rendition of the query first shown in Recipe 22-6. This time, the table reference is followed by a WITH clause containing an INDEX hint.

SELECT ProductID, TransactionID, ReferenceOrderID
FROM Production.TransactionHistory
        WITH (INDEX (IX_TransactionHistory_ProductID))
ORDER BY ProductID

The INDEX hint in this query forces the use of the named index: IX_TransactionHistory_ProductID.

How It Works

Figures 22-7 and 22-8 show an execution plan without and with the INDEX hint. You can see in Figure 22-8 that the hint forces the use of the index on the ProductID column.

Think twice before forcing the use of an index as shown in this recipe. Whenever you lock in an index choice with a hint, that choice remains locked in no matter what optimizer improvements are made. It remains locked in even if the data changes to favor use of some other index. Before hinting an index, consider whether the statistics are up-to-date and whether you can do something to trigger the use of the index without having to hard-code that usage in the form of a table hint.

Problem

You want to avoid trouble from parameter sniffing by instructing the optimizer on which value to consider when parsing a query that has bind variables.

Solution

Specify the OPTIMIZE FOR hint. Here’s an example:

DECLARE @TTYPE NCHAR(1);
SET @TTYPE = 'P';
SELECT *
FROM Production.TransactionHistory TH
WHERE TH.TransactionType = @TTYPE
OPTION (OPTIMIZE FOR (@TTYPE = 'S'));

How It Works

The solution example specifies the hint OPTIMIZE FOR (@TTYPE = 'S'). The optimizer will take the value 'S' into account when building a query plan. Hinting like this can sometimes be helpful in cases in which data is badly skewed, especially when the risk is high that the first execution of a given query will be done using a value resulting in a plan that will work poorly for subsequent values passed in subsequent executions.

If you execute the solution query and choose to view the actual execution plan in XML form, you’ll find the following:

<ColumnReference Column="@TTYPE"
   ParameterCompiledValue="N'S'"
   ParameterRuntimeValue="N'P'" />

Here you can see that the compiled query took into account the value S. But the query as actually executed used the value P. The plan actually executed is the one compiled for the S, just as the hint specified.