TABLE HINTS - SQL Server

from http://www.sqlserverspecialists.com/2013/04/table-hints-sql-server.html

Sometimes your query executes very slowly. One of the ways to increase the queries performance is using the SQL Server table hints. The table hints specify a locking method to be used by the query optimizer with this table, specify the locking granularity for a table, or one or more indexes to be used by the query optimizer when querying this table. If you want to use the table optimizer hints, you should specify the WITH keyword with the hints list after the table name in the FROM clause. You can specify several table hints and separate them by a comma. 

If you want to set any table hints, do not remember to test the query with hint
and without the hint and compare results.
Because SQL Server  query optimizer usually proposes the best execution plan, you should 
test the query with table hint and use this hint only when it provides the best result. If the 
table hint provides the better execution plan not stable, avoid using this hint and rely on the
SQL Server query optimizer.

NOLOCK or READUNCOMMITTED:

Use the NOLOCK or READUNCOMMITTED table hint if you need the highest concurrency. 
These hints specify that dirty reads can be allowed. When these hints are used, no shared locks 
are issued and no exclusive locks are honored.

This example shows how you can use the NOLOCK table hint:
SELECT * FROM titles WITH (NOLOCK)

 

The NOLOCK hint means it will not take care of any lock (instead of placing a lock). It will return data that could be dirty (not yet committed by other transactions). We can use this table hint to get results quickly when we are dead sure that dirty data is totally bearable.

In DELETE/UPDATE queries it should be totally avoided as it can produce junk results.

 

READCOMMITTED:

Consider using the READCOMMITTED table hint to specify that statements cannot 
read data that has been modified but not committed by other transactions.
The READCOMMITTED table hint specifies that read operations comply with the rules for the 
READ COMMITTED isolation level by using either locking or row versioning. When the database  option READ_COMITTED_SNAPSHOT is OFF (by default), the shared locks are used, when the READ_COMITTED_SNAPSHOT is ON, the row versions are used instead of shared locks.

This example shows how you can use the READCOMMITTED table hint:
SELECT * FROM authors WITH (READCOMMITTED)

REPEATABLEREAD :

 

Use the REPEATABLEREAD table hint if you need more consistency, than default
(READCOMMITTED) and higher concurrency, than with SERIALIZABLE.
By default, SQL Server uses READ COMMITTED isolation level. With this isolation level SQL Server uses shared locks while reading data. It ensures that not committed data will not be read, but it not ensures that the data will not be changed before the end of the transaction. If you need the current data will not be changed before the end of the transaction, you can use the REPEATABLEREAD table hint.

This example shows how you can use the REPEATABLEREAD table hint:
SELECT * FROM authors WITH (REPEATABLEREAD)

NOWAIT :

 

Consider using the NOWAIT table hint if you need to return a message when a lock is 
encountered on the table.
When the NOWAIT table hint is used, SQL Server returns a message as soon as a lock is encountered on the table. Using this hint is equivalent to use SET LOCK_TIMEOUT 0 for 
a specific table. 

In this example, SQL Server  will return a message when a lock is encountered on the 
authors table:
SELECT * FROM authors WITH (NOWAIT)

INDEX:

Use the INDEX hint if you need to specify the name or ID of the indexes to be
used by SQL Server  when processing the statement.

This example shows how you can use the INDEX hint:
SELECT au_fname FROM authors WITH (INDEX(0))

 

If a clustered index exists, INDEX (0) forces clustered index scan, if no clustered index
exists, INDEX (0) forces a table scan.

Note: You can use only one index hint per table, but multiple indexes can be used in the 
single hint list.

XLOCK :

 

Consider using the XLOCK table hint to take and hold exclusive locks until 
the transaction completes. This hint forces exclusive locks on the resources being referenced (not compatible with NOLOCK or UPDLOCK).
When you use the XLOCK table hint with ROWLOCK, PAGLOCK, or TABLOCK, the exclusive locks apply to the appropriate level of granularity.

This example shows how you can use the XLOCK table hint with the PAGLOCK hint:
SELECT * FROM titles WITH (XLOCK, PAGLOCK)

READPAST :

 

Use the READPAST table hint if you need to return only the pasted rows.
When the READPAST table hint is used, the locked rows will be skipped. i,e Skips locked rows, and does not read them

This is the example to return only the pasted rows from the titles and titleauthor tables:
SELECT * FROM titles a JOIN titleauthor b

WITH (READPAST) ON a.title_id = b.title_id

ROWLOCK :

 

Consider using the ROWLOCK table hint.
This hint specifies that a shared row lock will be taken instead of shared page or table lock.
The ROWLOCK provides the higher concurrency, but at the cost of lower performance because
SQL Server  should hold and release locks at the row level.

This example shows how you can use the ROWLOCK table hint:
SELECT * FROM titles WITH (ROWLOCK)

 

HOLDLOCK or SERIALIZABLE:

Use the HOLDLOCK or SERIALIZABLE table hint if you need the highest consistency.
These hints specify shared locks will be holding until the completion of a transaction.
By default, the shared locks are released as soon as the required table or data page is
no longer needed. This results in higher consistency, but at the cost of lower concurrency.

This example shows how you can use the HOLDLOCK table hint:
SELECT * FROM titles WITH (HOLDLOCK)

PAGLOCK :

 

Consider using the PAGLOCK table hint.
The PAGLOCK hint specifies that a shared page lock will be taken instead of table lock.

This example shows how you can use the PAGLOCK table hint:
SELECT * FROM titles a JOIN titleauthor b

WITH (PAGLOCK) ON a.title_id = b.title_id

UPDLOCK :

 

Consider using the UPDLOCK table hint. The hint will force update locks instead of shared locks to be generated (not compatible with NOLOCK or XLOCK).
The UPDLOCK hint specifies that update locks are taken while reading the table and that
they are held until the end of the Transact-SQL statement or end of the transaction.

This example shows how you can use the UPDLOCK table hint:
SELECT * FROM titles a JOIN titleauthor b

WITH (UPDLOCK) ON a.title_id = b.title_id

FASTFIRSTROW :

 

Use the FASTFIRSTROW table hint if you need to quickly return first row.
You can quickly return first row and work with it, when the query continues execution and
produces its full result set. The FASTFIRSTROW table hint has the same effect as specifying
the FAST 1 query hint. Keep in mind, that when you use the FASTFIRSTROW hint the rest of 
the rows are often returned more slowly that they would have been if the hint had not
been specified. 

This is the example to quickly return first row from the titles and titleauthor tables:
SELECT * FROM titles a JOIN titleauthor b

WITH (FASTFIRSTROW) ON a.title_id = b.title_id

 

TABLOCK :

 

Consider using the TABLOCK table hint.
The TABLOCK hint specifies that a shared lock will be taken on the table until the end of
the Transact-SQL statement.

This example shows how you can use the TABLOCK table hint:
SELECT * FROM titles a JOIN titleauthor b

WITH (TABLOCK) ON a.title_id = b.title_id

TABLOCKX :

 

Consider using the TABLOCKX table hint.
The TABLOCKX hint specifies that an exclusive lock will be taken on the table until the end
of the Transact-SQL statement or the end of the transaction. The TABLOCKX provides the worst
concurrency, but, in this case, SQL Server  will make minimum work to hold and release locks.

This example shows how you can use the TABLOCKX table hint:
SELECT * FROM authors WITH (TABLOCKX)

 

KEEPIDENTITY:

 

This option applies to the OPENROWSET function’s new SQL Server BULK insert functionality and impacts how rows are inserted into a table with an IDENTITY column. If you use this hint, SQL Server will use the identity values from the data file, instead of generating its own.

 

Piece of Advice:
If your query is very slow, try the following before using table hints:
- rebuild indexes used in the query (or defragment them using the DBCC INDEXDEFRAG command),
- update statistics on the relevant tables,
- consider creating new indexes for this query,
- rewrite your query to provide better execution plan.
Use the table hints only when the actions above do not provide good performance.