Advanced Query Tuning Hints and Plan Guides - Microsoft SQL Server 2012 Performance Tuning - page 357

How it works...
In this recipe, we first executed a query from Connection-1, which retrieved records from the
Sales.SalesOrderDetail table where LineTotal > 10000 . Note that we have used the
UPDLOCK and HOLDLOCK query hints here. The UPDLOCK query hint acquires the UPDATE
lock on rows, as if the rows are going to be updated and HOLDLOCK holds that lock until the
transaction is completed. In a real-life scenario, you can think of these rows as if they are
going to be updated by the UPDATE statement.
We could have used the UPDATE statement instead of using the UPDLOCK
and the HOLDLOCK hints here. However, this has been done purposefully,
just to show you how incorrectly used query hints can block other queries.
We then executed the same query without any query hint from Connection-2. The SELECT
statement in the second query tried to acquire the SHARED locks (default behavior of the
SELECT statement) to access the same set of records but the request was blocked, because
there are UPDATE locks on the same rows held by Connection-1.
Finally, we executed the same query from Connection-3, to retrieve the same set of rows,
but this time with the query hint NOLOCK . This hint instructed SQL Server to simply retrieve
the data without acquiring any shared lock on the data. Because SQL Server did not need
to acquire any lock, it could run the query to return the same rows, which are locked by
Connection-1.
Considering the database application architecture and requirements, try to
use the NOLOCK query hint when it is safe to use. It reduces the overhead
on SQL Server caused by acquiring and releasing locks. Using the NOLOCK
hint reduces the blocking issues, as queries with a NOLOCK hint do not have
to wait for the result. This improves the overall performance of queries.
Using FORCESEEK and INDEX table hint
A major role of a query optimizer is to choose the best execution plan among the different
available plans for query execution. In most of the cases, query optimizer always chooses
the right execution plan, and we generally don't need to specify query hints to force the query
optimizer to execute a query in a desired way. However, in rare cases, it can happen that query
optimizer may fail to choose the right query plan.
SQL Server allows us to specify query hints while writing queries, which forces the query
optimizer to execute the query in a specific way only.
In this recipe, we will see how we can force a query to perform an index seek operation by
using the FORCESEEK and INDEX table query hints when the query optimizer performs an
index scan operation.