<query_hint > ::=
{ { HASH | ORDER } GROUP
| { CONCAT | HASH | MERGE } UNION
| { LOOP | MERGE | HASH } JOIN
| FAST number_rows
| FORCE ORDER
| MAXDOP number_of_processors
| OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
| OPTIMIZE FOR UNKNOWN
| PARAMETERIZATION { SIMPLE | FORCED }
| RECOMPILE
| ROBUST PLAN
| KEEP PLAN
| KEEPFIXED PLAN
| EXPAND VIEWS
| MAXRECURSION number
| USE PLAN N'xml_plan'
| TABLE HINT ( exposed_object_name [ , <table_hint> [ [, ]...n ] ] )
<table_hint> ::=
[ NOEXPAND ] {
INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value )
| FASTFIRSTROW
| FORCESEEK
| HOLDLOCK
| NOLOCK
| NOWAIT
| PAGLOCK
| READCOMMITTED
| READCOMMITTEDLOCK
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
}
A. Using MERGE JOIN
The following example specifies that the JOIN operation in the query is performed by MERGE JOIN.
Copy Code
USE AdventureWorks2008R2;
GO
SELECT *
FROM Sales.Customer AS c
INNER JOIN Sales.vStoreWithAddresses AS sa
ON c.CustomerID = sa.BusinessEntityID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);
GO
B. Using OPTIMIZE FOR
The following example instructs the query optimizer to use the value 'Seattle' for local variable @city_name and to use statistical data to determine the value for the local variable @postal_code when optimizing the query.
Copy Code
USE AdventureWorks2008R2;
GO
DECLARE @city_name nvarchar(30);
DECLARE @postal_code nvarchar(15);
SET @city_name = 'Ascheim';
SET @postal_code = 86171;
SELECT * FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );
GO
C. Using MAXRECURSION
MAXRECURSION can be used to prevent a poorly formed recursive common table expression from entering into an infinite loop. The following example intentionally creates an infinite loop and uses the MAXRECURSION hint to limit the number of recursion levels to two.
Copy Code
USE AdventureWorks2008R2;
GO
--Creates an infinite loop
WITH cte (CustomerID, PersonID, StoreID) AS
(
SELECT CustomerID, PersonID, StoreID
FROM Sales.Customer
WHERE PersonID IS NOT NULL
UNION ALL
SELECT cte.CustomerID, cte.PersonID, cte.StoreID
FROM cte
JOIN Sales.Customer AS e
ON cte.PersonID = e.CustomerID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT CustomerID, PersonID, StoreID
FROM cte
OPTION (MAXRECURSION 2);
GO
After the coding error is corrected, MAXRECURSION is no longer required.
D. Using MERGE UNION
The following example uses the MERGE UNION query hint.
Copy Code
USE AdventureWorks2008R2;
GO
SELECT *
FROM HumanResources.Employee AS e1
UNION
SELECT *
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);
GO
E. Using HASH GROUP and FAST
The following example uses the HASH GROUP and FAST query hints.
Copy Code
USE AdventureWorks2008R2;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (HASH GROUP, FAST 10);
GO
F. Using MAXDOP
The following example uses the MAXDOP query hint.
Copy Code
USE AdventureWorks2008R2 ;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (MAXDOP 2);
GO
G. Using INDEX
The following examples use the INDEX hint. The first example specifies a single index. The second example specifies multiple indexes for a single table reference. In both examples, because the INDEX hint is applied on a table that uses an alias, the TABLE HINT clause must also specify the same alias as the exposed object name.
Copy Code
USE AdventureWorks2008R2;
GO
EXEC sp_create_plan_guide
@name = N'Guide1',
@stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
FROM HumanResources.Employee AS e
JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
WHERE e.OrganizationLevel = 2;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (TABLE HINT(e, INDEX (IX_Employee_OrganizationLevel_OrganizationNode)))';
GO
EXEC sp_create_plan_guide
@name = N'Guide2',
@stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
FROM HumanResources.Employee AS e
JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
WHERE e.OrganizationLevel = 2;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (TABLE HINT(e, INDEX(PK_Employee_BusinessEntityID, IX_Employee_OrganizationLevel_OrganizationNode)))';
GO
H. Using FORCESEEK
The following example uses the FORCESEEK table hint. Because the INDEX hint is applied on a table that uses a two-part name, the TABLE HINT clause must also specify the same two-part name as the exposed object name.
Copy Code
USE AdventureWorks2008R2;
GO
EXEC sp_create_plan_guide
@name = N'Guide3',
@stmt = N'SELECT c.LastName, c.FirstName, HumanResources.Employee.JobTitle
FROM HumanResources.Employee
JOIN Person.Person AS c ON HumanResources.Employee.BusinessEntityID = c.BusinessEntityID
WHERE HumanResources.Employee.OrganizationLevel = 3
ORDER BY c.LastName, c.FirstName;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (TABLE HINT( HumanResources.Employee, FORCESEEK))';
GO
I. Using multiple table hints
The following example applies the INDEX hint to one table and the FORCESEEK hint to another.
Copy Code
USE AdventureWorks2008R2;
GO
EXEC sp_create_plan_guide
@name = N'Guide4',
@stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
FROM HumanResources.Employee AS e
JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
WHERE OrganizationLevel = 3;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_OrganizationLevel_OrganizationNode ) )
, TABLE HINT ( c, FORCESEEK) )';
GO
J. Using TABLE HINT to override an existing table hint
The following example shows how to use the TABLE HINT hint without specifying a hint to override the behavior of the INDEX table hint specified in the FROM clause of the query.
Copy Code
USE AdventureWorks2008R2;
GO
EXEC sp_create_plan_guide
@name = N'Guide5',
@stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_OrganizationLevel_OrganizationNode))
JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
WHERE OrganizationLevel = 3;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (TABLE HINT(e))';
GO
K. Specifying semantics-affecting table hints
The following example contains two table hints in the query: NOLOCK, which is semantic-affecting, and INDEX, which is non-semantic-affecting. To preserve the semantics of the query, the NOLOCK hint is specified in the OPTIONS clause of the plan guide. In addition to the NOLOCK hint, the INDEX and FORCESEEK hints are specified and replace the non-semantic-affecting INDEX hint in the query when the statement is compiled and optimized.
Copy Code
USE AdventureWorks2008R2;
GO
EXEC sp_create_plan_guide
@name = N'Guide6',
@stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
FROM HumanResources.Employee AS e
JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
WHERE OrganizationLevel = 3;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_OrganizationLevel_OrganizationNode) , NOLOCK, FORCESEEK ))';
GO
The following example shows an alternative method to preserving the semantics of the query and allowing the optimizer to choose an index other than the index specified in the table hint. This is done by specifying the NOLOCK hint in the OPTIONS clause (because it is semantic-affecting) and specifying the TABLE HINT keyword with only a table reference and no INDEX hint.
Copy Code
USE AdventureWorks2008R2;
GO
EXEC sp_create_plan_guide
@name = N'Guide7',
@stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
FROM HumanResources.Employee AS e
JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
WHERE OrganizationLevel = 2;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (TABLE HINT ( e, NOLOCK))';
GO
