Hints

Hints are options or strategies specified for enforcement by the SQL Server query processor on SELECT, INSERT, UPDATE, or DELETE statements. The hints override any execution plan the query optimizer might select for a query. Applies to SELECT, DELETE, UPDATE.

A.     Join Hints:  { LOOP | HASH | MERGE | REMOTE }

Join hints specify that the query optimizer enforce a join strategy between two tables.

LOOP | HASH | MERGE

Specifies that the join in the query should use looping, hashing, or merging between two tables. LOOP cannot be specified together with RIGHT or FULL as a join type.

REMOTE

Specifies that the join operation is performed on the site of the right table. This is useful when the left table is a local table and the right table is a remote table. REMOTE should be used only when the left table has fewer rows than the right table.

If the right table is local, the join is performed locally. If both tables are remote but from different data sources, REMOTE causes the join to be performed on the site of the right table. If both tables are remote tables from the same data source, REMOTE is not required.

REMOTE cannot be used when one of the values being compared in the join predicate is cast to a different collation using the COLLATE clause.

REMOTE can be used only for INNER JOIN operations.

A. Using HASH

USE AdventureWorks2008R2;

GO

SELECT p.Name, pr.ProductReviewID

FROM Production.Product p

LEFT OUTER HASH JOIN Production.ProductReview pr

ON p.ProductID = pr.ProductID

ORDER BY ProductReviewID DESC;

B. Using LOOP

DELETE FROM Sales.SalesPersonQuotaHistory

FROM Sales.SalesPersonQuotaHistory AS spqh

    INNER LOOP JOIN Sales.SalesPerson AS sp

    ON spqh.BusinessEntityID = sp.BusinessEntityID

WHERE sp.SalesYTD > 2500000.00; 

C. Using MERGE

SELECT poh.PurchaseOrderID, poh.OrderDate, pod.ProductID, pod.DueDate, poh.VendorID

FROM Purchasing.PurchaseOrderHeader AS poh

INNER MERGE JOIN Purchasing.PurchaseOrderDetail AS pod

    ON poh.PurchaseOrderID = pod.PurchaseOrderID;

B.      Query Hints: 

                     Query hints override the default behavior of the query optimizer for the duration of the query statement. You can use query hints to specify a locking method on the affected tables, one or more indexes, and a query processing operation such as a table scan or index seek, or other options. Query hints are applied to the entire query. Applies to SELECT, INSERT, UPDATE, DELETE & MERGE. 

{ 

    { 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 ] ] )

}

 

Covering only few hints in detail: 

{ HASH | ORDER } GROUP

Specifies that aggregations described in the GROUP BY, DISTINCT, or COMPUTE clause of the query should use hashing or ordering. 

FAST number_rows:

Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set. 

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);

{ MERGE | HASH | CONCAT } UNION

Specifies that all UNION operations are performed by merging, hashing, or concatenating UNION sets. If more than one UNION hint is specified, the query optimizer selects the least expensive strategy from those hints specified. 

USE AdventureWorks2008R2;

GO

SELECT *  FROM HumanResources.Employee AS e1

UNION

SELECT *  FROM HumanResources.Employee AS e2

OPTION (MERGE UNION);

GO

{ LOOP | MERGE | HASH } JOIN

Specifies that all join operations are performed by LOOP JOIN, MERGE JOIN, or HASH JOIN in the whole query. If more than one join hint is specified, the optimizer selects the least expensive join strategy from the allowed ones.

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 

RECOMPILE

Instructs the SQL Server Database Engine to discard the plan generated for the query after it executes, forcing the query optimizer to recompile a query plan the next time the same query is executed. Without specifying RECOMPILE, the Database Engine caches query plans and reuses them. 

MAXRECURSION number

Specifies the maximum number of recursions allowed for this query. number is a non-negative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100. When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned. 

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 

-- Using Multiple Table Hints: 

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 

-- Using TABLE HINT to override an existing table hint

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

C.      Table Hints:  

                 Table hints override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method, one or more indexes, a query processing operation such as a table scan or index seek, or other options. Table hints are specified in the FROM clause of the DML statement and affect only the table or view referenced in that clause. 

WITH  ( <table_hint> [ [, ]...n ] )

<table_hint> ::= 

[ NOEXPAND ] { 

    INDEX ( index_value [ ,...n ] ) | INDEX =  ( index_value )

  | FASTFIRSTROW 

  | FORCESEEK [( index_value ( index_column_name  [ ,... ] ) ) ]

  | FORCESCAN

  | HOLDLOCK 

  | NOLOCK 

  | NOWAIT

  | PAGLOCK 

  | READCOMMITTED 

  | READCOMMITTEDLOCK 

  | READPAST 

  | READUNCOMMITTED 

  | REPEATABLEREAD 

  | ROWLOCK 

  | SERIALIZABLE 

  | TABLOCK 

  | TABLOCKX 

  | UPDLOCK 

  | XLOCK 

} 

<table_hint_limited> ::=

{

    KEEPIDENTITY 

  | KEEPDEFAULTS 

  | IGNORE_CONSTRAINTS 

  | IGNORE_TRIGGERS   

} 

WITH ( <table_hint> ) [ [ , ]...n ]

With some exceptions, table hints are supported only when the hints are specified with the WITH keyword. Parentheses are required. 

NOEXPAND

Specifies that any indexed views are not expanded to access underlying tables when the query optimizer processes the query. The query optimizer treats the view like a table with clustered index. NOEXPAND applies only to indexed views. 

INDEX (index_value [,... n ] ) | INDEX = (index_value)

The syntax INDEX(index_value) specifies the name or ID of one or more indexes that will be used by the query optimizer when it processes the statement. The alternative syntax INDEX = (index_value) allows for only a single index value.

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.

If multiple indexes are used in a single hint list, any duplicates are ignored and the rest of the listed indexes are used to retrieve the rows of the table. The order of the indexes in the index hint is significant. A multiple index hint also enforces index ANDing, and the query optimizer applies as many conditions as possible on each index that is accessed. If the collection of hinted indexes do not include all columns referenced by the query, a fetch is performed to retrieve the remaining columns after the SQL Server Database Engine retrieves all the indexed columns.

When an index hint referring to multiple indexes is used on the fact table in a star join, the optimizer ignores the index hint and returns a warning message. Also, index ORing is not allowed for a table with an index hint specified.The maximum number of indexes that can be specified in the table hint is 250 non-clustered indexes.

SELECT StartDate, ComponentID FROM Production.BillOfMaterials

    WITH( INDEX (FIBillOfMaterialsWithComponentID) )

    WHERE ComponentID in (533, 324, 753, 855, 924); 

KEEPIDENTITY

    Is applicable only in an INSERT statement when the BULK option is used with OPENROWSET.    Specifies that the identity value or values in the imported data file are to be used for the identity column. 

KEEPDEFAULTS

    Is applicable only in an INSERT statement when the BULK option is used with OPENROWSET.    Specifies insertion of a table column's default value, if any, instead of NULL when the data record lacks a value for the column.

FORCESEEK [ (index_value(index_column_name [ ,... n ] )) ]

                Specifies that the query optimizer use only an index seek operation as the access path to the data in the table or view.

Syntax	Example	Description
Without an index or INDEX hint	FROM dbo.MyTable WITH (FORCESEEK)	The query optimizer considers only index seek operations to access the table or view through any relevant index.
Combined with an INDEX hint	FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex))	The query optimizer considers only index seek operations to access the table or view through the specified index.
Parameterized by specifying an index and index columns	FROM dbo.MyTable WITH (FORCESEEK (MyIndex (col1, col2, col3)))	The query optimizer considers only index seek operations to access the table or view through the specified index using at least the specified index columns.

 -- The following example uses the FORCESEEK hint without specifying an index to force the query optimizer to perform an index seek operation on the Sales.

-- SalesOrderDetail table. 

SELECT *

FROM Sales.SalesOrderHeader AS h

INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK)

    ON h.SalesOrderID = d.SalesOrderID

WHERE h.TotalDue > 100

AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);

GO

-- The following example uses the FORCESEEK hint with an index to force the query optimizer to perform an index seek operation on the specified index and index column. 

SELECT h.SalesOrderID, h.TotalDue, d.OrderQty

FROM Sales.SalesOrderHeader AS h

    INNER JOIN Sales.SalesOrderDetail AS d

    WITH (FORCESEEK (PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID (SalesOrderID)))

    ON h.SalesOrderID = d.SalesOrderID

WHERE h.TotalDue > 100

AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);

GO

FORCESCAN

Introduced in SQL Server 2008 R2 SP1, this hint specifies that the query optimizer use only an index scan operation as the access path to the referenced table or view referenced. The FORCESCAN hint can be useful for queries in which the optimizer underestimates the number of affected rows and chooses a seek operation rather than a scan operation. When this occurs, the amount of memory granted for the operation is too small and query performance is impacted. 

The FORCESCAN hint has the following restrictions.

• The hint cannot be specified for a table that is the target of an INSERT, UPDATE, or DELETE statement.
• The hint cannot be used with more than one index hint.
• The hint prevents the optimizer from considering any spatial or XML indexes on the table.
• The hint cannot be specified for a remote data source.
• The hint cannot be specified in combination with the FORCESEEK hint.

IGNORE_TRIGGERS

Is applicable only in an INSERT statement when the BULK option is used with OPENROWSET.

Specifies that any triggers defined on the table are ignored by the bulk-import operation. By default, INSERT applies triggers. Use IGNORE_TRIGGERS only if your application does not depend on any triggers and maximizing performance is important.  

NOLOCK

Is equivalent to READUNCOMMITTED. For more information, see READUNCOMMITTED later in this topic. 

NOWAIT

Instructs the Database Engine to return a message as soon as a lock is encountered on the table. NOWAIT is equivalent to specifying SET LOCK_TIMEOUT 0 for a specific table. 

READPAST

Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped. That is, the Database Engine skips past the rows instead of blocking the current transaction until the locks are released. 

For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5. READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table. A queue reader that uses READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait until the other transactions release their locks.

READPAST cannot be specified for tables in the INTO clause of an INSERT statement. Read operations that use READPAST do not block. Update or delete operations that use READPAST may block when reading foreign keys or indexed views, or when modifying secondary indexes. 

READPAST can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels. When specified in transactions operating at the SNAPSHOT isolation level, READPAST must be combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK. 

The READPAST table hint cannot be specified when the READ_COMMITTED_SNAPSHOT database option is set to ON and either of the following conditions is true. 

n  The transaction isolation level of the session is READ COMMITTED.

n  The READCOMMITTED table hint is also specified in the query. 

To specify the READPAST hint in these cases, remove the READCOMMITTED table hint if present, and include the READCOMMITTEDLOCK table hint in the query. 

READCOMMITTED

Specifies that read operations comply with the rules for the READ COMMITTED isolation level by using either locking or row versioning. 

READCOMMITTEDLOCK

Specifies that read operations comply with the rules for the READ COMMITTED isolation level by using locking. The Database Engine acquires shared locks as data is read and releases those locks when the read operation is completed, regardless of the setting of the READ_COMMITTED_SNAPSHOT database option. 

TABLOCK

Specifies that the acquired lock is applied at the table level. The type of lock that is acquired depends on the statement being executed. For example, a SELECT statement may acquire a shared lock. By specifying TABLOCK, the shared lock is applied to the entire table instead of at the row or page level. If HOLDLOCK is also specified, the table lock is held until the end of the transaction.

When used with the OPENROWSET bulk rowset provider to import data into a table, TABLOCK enables multiple clients to concurrently load data into the target table with optimized logging and locking.  

-- The following example specifies that a shared lock is taken on the Production.Product table and is held until the end of the UPDATE statement.

USE AdventureWorks2008R2;

GO

UPDATE Production.Product

WITH (TABLOCK)

SET ListPrice = ListPrice * 1.10

WHERE ProductNumber LIKE 'BK-%';

GO

TABLOCKX

Specifies that an exclusive lock is taken on the table.

UPDLOCK

                Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead.

                When UPDLOCK is specified, the READCOMMITTED and READCOMMITTEDLOCK isolation level hints are ignored. For example, if the isolation level of the session is set to SERIALIZABLE and a query specifies (UPDLOCK, READCOMMITTED), the READCOMMITTED hint is ignored and the transaction is run using the SERIALIZABLE isolation level. 

XLOCK

                Specifies that exclusive locks are to be taken and held until the transaction completes. If specified with ROWLOCK, PAGLOCK, or TABLOCK, the exclusive locks apply to the appropriate level of granularity. 

NOTE:

A.      The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. 

B.      The KEEPIDENTITY, IGNORE_CONSTRAINTS, and IGNORE_TRIGGERS hints require ALTER permissions on the table. 

C.      SQL Server does not allow for more than one table hint from each of the following groups for each table in the FROM clause:

o   Granularity hints: PAGLOCK, NOLOCK, READCOMMITTEDLOCK, ROWLOCK, TABLOCK, or TABLOCKX.

o   Isolation level hints: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.