SQL Server Query Hints : the use of WITH (NOLOCK)

Since we all want to see our queries run faster (at least for most scrum development houses), I am suggesting the use of WITH (NOLOCK), but with loads of cautionary nuances.

There are certainly times when it can be used in production (closely monitored!) as long as the data in which you are querying is not at risk of change, because the database engine will spurt out dirty reads and will even give you duplicate rows or even skip rows. In a Datawarehouse, for example, you'll see a performance enhancement (thanks to Itzak Ben-Gan for the tip) since the data should be set to read only after a daily early-morning load, assuming one would load the data during non business hours. The distaster I wish you all to avoid is placing a view in production, deciding to use the query hint, and then find data movement errors (page splits are occuring due to inserts/updates while you are reading the table). The most important avoidance for NOLOCK is it's potential to flip cluster nodes (error 7105 excessive cpu usage) when used on tables that have NTEXT, TEXT or IMAGE datatypes - at least from what I saw first hand on a SQL 2000 cluster.

Another caveat with respect to nolock use: The Database Engine might have to acquire locks when reading metadata, even when processing a select with a locking hint that prevents requests for shared locks when reading data. For example, a SELECT using the NOLOCK hint does not acquire share locks when reading data, but might sometime request locks when reading a system catalog view. This means it is possible for a SELECT statement using NOLOCK to be blocked! Thus, if you are feeling iffy about a process or specific stored procedure, run the following:

-- Get information about the locks held by
-- the transaction.
SELECT *
FROM sys.lock_information
WHERE request_spid = @@SPID

It's okay to use NOLOCK when you know the table is being pounded and you're just wanting to check some data without affecting production. From Microsoft literature they say basically that because the SQL Server query optimizer typically selects the best execution plan for a query, they recommend that NOLOCK be used only as a last resort by experienced developers and database administrators if you do not care about inconsistent data or if the data is guaranteed to be static.

Beware the trends in some companies where developers have decided that it's good to use it all the time, which is, for the reasons mentioned above, a very bad idea in production. Whereas in a datawarehouse, it can be set for the entire area, since the data is created to stay static until the next day or month's load anyway.

http://www.sqlservercentral.com/articles/Performance+Tuning/2764/
http://www.sqlservercentral.com/Forums/Topic330889-192-1.aspx