Some tips for using table hints in SQL Server 2016
Use the INSERT … SELECT statement with the TABLOCK hint.
In SQL Server 2016, the insert in an INSERT … SELECT statement is multi-threaded
or can have a parallel plan. To get a parallel plan, you can use the compatibility
level 130 and the INSERT … SELECT statement must use the TABLOCK hint.
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.
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)
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 2016 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)
Use the SNAPSHOT table hint.
This table hint was first introduced in SQL Server 2014. The memory-optimized
table is accessed under SNAPSHOT isolation. The SNAPSHOT table hint can only
be used with memory-optimized tables (not with disk-based tables).
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)
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)
Consider using the XLOCK table hint to take and hold exclusive locks until
the transaction completes.
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)
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)
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
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. 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
Consider using the UPDLOCK table hint.
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
Use the FORCESCAN table hint to specify that the query optimizer use only
an index scan operation as the access path to the referenced table or view.
This table hint was first introduced in SQL Server 2008 R2 service pack 1.
The FORCESCAN hint cannot be specified for a remote data source and for a
table that is the target of an INSERT, UPDATE, or DELETE statement. This
table hint cannot be used with more than one index hint and in combination
with the FORCESEEK hint.
Consider using the TABLOCK table hint.
The TABLOCK hint specifies that the acquired lock is applied at the table level.
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
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 2016 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.
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 2016
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)
Use the INDEX hint if you need to specify the name or ID of the indexes to be
used by SQL Server 2016 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.
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 2016 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 2016 will return a message when a lock is
encountered on the authors table:
SELECT * FROM authors WITH (NOWAIT)