ARTICLES

Sometimes your query executes very slowly. One of the ways to increase the queries performance
is using the SQL Server 2005 table hints. In this article, I describe the table optimizer hints
you can use and show some simple scripts to make this optimization. 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.

Use the INDEX hint if you need to specify the name or ID of the indexes to be
used by SQL Server 2005 when processing the statement.
This example shows how you can use the INDEX hint:
SELECT au_fname FROM authors WIRH (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 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 NOLOCK or READUNCOMMITTED table hint if you need the highest concurrency.
These hints specify that dirty reads can be allowed. This result in higher concurrency, but
at the cost of lower consistency. 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 READCOMMITTED table hint to specify that statements cannot
read data that has been modified but not committed by other transactions.
The behavior of this hint is different in SQL Server 2005 than in previous SQL Server versions.
SQL Server 2005 supports row versions. So, 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 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 2005 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)

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

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 2005 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.