Database Reference
In-Depth Information
This prevents the
SELECT
statement from requesting any lock, and it is applicable to
SELECT
statements only.
Although the
NOLOCK
hint can't be used directly on the tables referred to in the action queries (
INSERT
,
UPDATE
, and
DELETE
), it may be used on the data retrieval part of the action queries, as shown here:
DELETE Sales.SalesOrderDetail
FROM Sales.SalesOrderDetail sod WITH(NOLOCK)
JOIN Production.Product p WITH(NOLOCK)
ON sod.ProductID = p.ProductID
AND p.ProductID = 0;
Just know that this leads to dirty reads, which can cause duplicate rows or missing rows and is therefore
considered to be a last resort to control locking. The best approach is to mark the database as read-only or use one of
the snapshot isolation levels.
This is a huge topic, and a lot more can be said about it. I discuss the different types of lock requests and how to
manage lock overhead in the next chapter. If you made any of the proposed changes to the database from this section,
I recommend restoring from a backup.
Summary
As discussed in this chapter, to improve the performance of a database application, it is important to ensure that
SQL queries are designed properly to benefit from performance enhancement techniques such as indexes, stored
procedures, database constraints, and so on. Ensure that queries are resource friendly and don't prevent the use of
indexes. In many cases, the optimizer has the ability to generate cost-effective execution plans irrespective of query
structure, but it is still a good practice to design the queries properly in the first place. Even after you design individual
queries for great performance, the overall performance of a database application may not be satisfactory. It is
important not only to improve the performance of individual queries but also to ensure that they work well with other
queries without causing serious blocking issues. In the next chapter, you will look into the different blocking aspects of
a database application.
