In most cases, the query optimizer chooses the fastest execution plan. However, there are some special situations in which the optimizer, for some particular reasons, cannot find the optimal solution. In such cases, you should use optimizer hints to force it to use a particular execution plan that could perform better.
Optimizer hints are optional parts in a SELECT statement that instruct the query optimizer to execute one specific behavior. In other words, by using optimizer hints, you do not allow the query optimizer to search and find the way to execute a query because you tell it exactly what to do.
1. Why Use Optimizer Hints
You should use optimizer hints only temporarily and for testing. In other words, avoid using them as a permanent part of a query. There are two reasons for this statement. First, if you force the optimizer to use a particular index and later define an index that results in better performance of the query, the query and the application to which it belongs cannot benefit from the new index. Second, Microsoft continuously strives to make the query optimizer better. If you bind a query to a specific execution plan, the query cannot benefit from new and improved features in the subsequent versions of the database system.
There are two reasons why the optimizer sometimes does not choose the fastest execution plan:
- The query optimizer is not perfect.
- The system does not provide the optimizer with the appropriate information.
NOTE Optimizer hints can help you only if the execution plan chosen by the optimizer is not optimal. If the system does not provide the optimizer with the appropriate information, use the AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS database options to create or modify existing statistics.
2. Types of Optimizer Hints
The Database Engine supports the following types of optimizer hints:
- Table hints
- Join hints
- Query hints
- Plan guides
The following sections describe these hints.
NOTE The examples that follow demonstrate the use of optimizer hints, but they don’t give you any recommendations about using them in any particular query. (In most cases shown in these examples, the use of hints would be counterproductive.)
2.1. Table Hints
You can apply table hints to a single table. The following table hints are supported:
- INDEX
- NOEXPAND
- FORCESEEK
The INDEX hint is used to specify one or more indices that are then used in a query. This hint is specified in the FROM clause of the query. You can use this hint to force index access if the optimizer for some reason chooses to perform a table scan for a given query. (Also, the INDEX hint can be used to prevent the optimizer from using a particular index.)
Examples 19.13 and 19.14 show the use of the INDEX hint.
Example 19.13
USE sample;
SELECT * FROM new_addresses a WITH ( INDEX(i_stateprov))
WHERE a.StateProvinceID = 9;
The graphical output of Example 19.13 is shown in Figure 19-9.
Example 19.13 is identical to Example 19.4, but contains the additional INDEX hint. This hint forces the query optimizer to use the i_stateprov index. Without this hint, the optimizer chooses the table scan, as you can see from the output of Example 19.4.
The other form of the INDEX query hint, INDEX(0), forces the optimizer to not use any of the existing nonclustered indices. Example 19.14 shows the use of this hint.
Example 19.14
SET SHOWPLAN_TEXT ON;
GO
USE AdventureWorks;
SELECT * FROM Person.Address a
WITH(INDEX(0))
WHERE a.StateProvinceID = 32;
GO
SET SHOWPLAN_TEXT OFF;
The textual output of Example 19.14 is
|–Clustered Index Scan
(OBJECT:([AdventureWorks].[Person].[Address].[PK_Address_AddressID]
AS [a]),
WHERE:([AdventureWorks].[Person].[Address].[StateProvinceID] as
[a].[StateProvinceID]=(32)))
NOTE If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error.
The execution plan of the query in Example 19.14 shows that the optimizer uses the clustered index scan, because of the INDEX(0) hint. Without this hint, the query optimizer itself would choose the nonclustered index scan.
The NOEXPAND hint specifies that any indexed view isn’t expanded to access underlying tables when the query optimizer processes the query. The query optimizer treats the view like a table with the clustered index. (For a discussion of indexed views, see Chapter 26.)
The FORCESEEK table hint forces the optimizer to use only an index seek operation as the access path to the data in the table (or view) referenced in the query. You can use this table hint to override the default plan chosen by the query optimizer, to avoid performance issues caused by an inefficient query plan. For example, if a plan contains table or index scan operators, and the corresponding tables cause a high number of reads during the execution of the query, forcing an index seek operation may yield better query performance. This is especially true when inaccurate cardinality or cost estimations cause the optimizer to favor scan operations at plan compilation time.
NOTE The FORCESEEK hint can be applied to both clustered and nonclustered indices.
2.2. Join Hints
Join hints instruct the query optimizer how join operations in a query should be performed. They force the optimizer either to join tables in the order in which they are specified in the FROM clause of the SELECT statement or to use the join processing techniques explicitly specified in the statement. The Database Engine supports the following join hints:
- FORCE ORDER
- LOOP
- HASH
- MERGE
The FORCE ORDER hint forces the optimizer to join tables in the order in which they are specified in a query. Example 19.15 shows the use of this join hint.
Example 19.15
USE AdventureWorks;
SELECT e.BusinessEntitylD, e.LoginID, d.DepartmentID
FROM HumanResources.Employee e, HumanResources.Department d,
HumanResources.EmployeeDepartmentHistory h
WHERE d.DepartmentID = h.DepartmentID
AND h.BusinessEntityID = e.BusinessEntityID
AND h.EndDate IS NOT NULL
OPTION(FORCE ORDER);
The graphical output of Example 19.15 is shown in Figure 19-10.
As you can see from the graphical output of Example 19.15, the optimizer performs the join operation in the order in which the tables appear in the query. This means that the EmployeeDepartmentHistory table will be processed first, then the Department table, and finally the Employee table. (If you execute the query without the FORCE ORDER hint, the query optimizer will process the tables in the opposite order: first Employee, then Department, and then EmployeeDepartmentHistory.)
NOTE Keep in mind that this does not necessarily mean that the new execution plan performs better than that chosen by the optimizer.
The query hints LOOP, MERGE, and HASH force the optimizer to use the nested loop technique, merge join technique, and hash join technique, respectively. These three join hints can be used only when the join operation conforms to the SQL standard—that is, when the join is explicitly indicated with the JOIN keyword in the FROM clause of a SELECT statement.
Example 19.16 shows a query that uses the merge join technique because the hint with the same name is explicitly defined in the SELECT statement. (You can apply the other two hints, HASH and LOOP, in the same way.)
Example 19.16
USE AdventureWorks;
SELECT * FROM Person.Address a JOIN Person.StateProvince s
ON a.StateProvinceID = s.StateProvinceID
OPTION (MERGE JOIN);
The graphical output of Example 19.16 is given in Figure 19-11.
As you can see from the output of Example 19.16, the query optimizer is forced to use the merge join processing technique. (If the hint is removed, the query optimizer chooses the hash join technique.)
The specific join hint can be written either in the FROM clause of a query or using the OPTION clause at the end of it. The use of the OPTION clause is recommended if you want to write several different hints together. Example 19.17 is identical to Example 19.16, but specifies the join hint in the FROM clause of the query. (Note that in this case the INNER keyword is required.)
Example 19.17
USE AdventureWorks;
SELECT * FROM Person.Address a INNER MERGE JOIN Person.StateProvince s
ON a.StateProvinceID = s.StateProvinceID;
2.3. Query Hints
There are several query hints, which are used for different purposes. This section discusses the following query hints:
- FAST n
- OPTIMIZE FOR
- OPTIMIZE FOR UNKNOWN
- USE PLAN
The FAST n hint specifies that the query is optimized for fast retrieval of the first n rows.
After the first n rows are returned, the query continues execution and produces its full result set.
NOTE This hint can be very helpful if you have a complex query with many result rows, requiring a lot of time for processing. Generally, a query is processed completely and then the system displays the result. This query hint forces the system to display the first n rows immediately after their processing.
The OPTIMIZE FOR hint forces the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution. This query hint can be used when you create plan guides, which are discussed in the next section.
Example 19.18 shows the use of the OPTIMIZE FOR query hint.
Example 19.18
USE AdventureWorks;
DECLARE @city_name nvarchar(30)
SET @city_name = ‘Newark’
SELECT * FROM Person.Address
WHERE City = @city_name
OPTION ( OPTIMIZE FOR (@city_name = ‘Seattle’) );
Although the value of the @city_name variable is set to Newark, the OPTIMIZE FOR hint forces the optimizer to use the value Seattle for the variable when optimizing the query.
The OPTIMIZE FOR UNKNOWN hint instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization. (Forced parameterization means that any literal value that appears in a SELECT, INSERT, UPDATE, or DELETE statement, submitted in any form, is converted to a parameter during query compilation. That way, all queries with a different parameter value will be able to reuse the cached query plan instead of having to recompile the statement each time when the parameter value is different.)
The USE PLAN hint takes a plan stored as an XML document as the parameter and advises the Database Engine to use the specified execution plan for the query.
(For the storage of an execution plan as an XML document, see the description of the SHOWPLAN_XML option of the SET statement earlier in this chapter, in the section “XML Execution Plan”)
2.4. Plan Guides
As you know from the previous section, hints are explicitly specified in the SELECT statement to influence the work of the query optimizer. Sometimes you cannot or do not want to change the text of the SELECT statement directly. In that case, it is still possible to influence the execution of queries by using plan guides. In other words, plan guides allow you to use a particular optimizer hint without changing the syntax of the SELECT statement.
NOTE The main purpose of plan guides is to avoid hard-coding of hints in cases where it is not recommended or not possible (for third-party application code, for instance).
Plan guides are created using the sp_create_plan_guide system procedure. This procedure creates a plan guide for associating query hints or actual query plans with queries in a database. Another system procedure, sp_control_plan_guide, enables, disables, or drops an existing plan guide. (You can also use SQL Server Management Studio to drop a plan guide. Examples for creation and deletion of plan guides are given in Microsoft Docs.)
NOTE There are no Transact-SQL DDL statements for creation and deletion of plan guides.
A subsequent SQL Server version will hopefully support such statements.
The Database Engine supports three types of plan guides:
- SQL Matches queries that execute in the context of stand-alone Transact-SQL statements and batches that are not part of a database object
- OBJECT Matches queries that execute in the context of routines and DML triggers
- TEMPLATE Matches stand-alone queries that are parameterized to a specified form
Example 19.19 shows how you can create the optimizer hint from Example 19.16, without the modification of the corresponding SELECT statement.
Example 19.19
sp_create_plan_guide @name = N’Example_19_16′,
@stmt = N’SELECT * FROM Person.Address a JOIN Person.StateProvince s
ON a.StateProvinceID = s.StateProvinceID’,
@type = N’SQL’,
@module_or_batch = NULL,
@params = NULL,
@hints = N’OPTION (HASH JOIN)’
As you can see from Example 19.19, the sp_create_plan_guide system procedure has several parameters. The @name parameter specifies the name of the new plan guide. The @stmt parameter comprises the T-SQL statement, while the @type parameter specifies the type of the plan guide (SQL, OBJECT, or TEMPLATE). The optimizer hint is specified in the @hints parameter. (You can also use Management Studio to create plan guides.)
To edit information related to plan guides, use the sys.plan_guides catalog view. This view contains a row for each plan guide in the current database. The most important columns are plan_guide_id, name, and query_text. The plan_guide_id column specifies the unique identifier of the plan guide, while the name column defines its name. The query_text column specifies the text of the query on which the plan guide is created.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.