WITH (NOLOCK)

Question

WITH (NOLOCK)

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

It's my understanding that if you mark a database as read only, no locking occurs. So if you have a warehouse type of app where you won't be modifying the data during the day, better to mark the database than put hints on all the queries.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Mike C SSC-Insane Points: 23224 More actions · Answer 2

That's the best option, but not always allowed due to organizational policies. I made this recommendation to a company for their data warehousing application a few months ago, but their IT policy wouldn't allow it.

Nicolas Neri SSC Journeyman Points: 98 More actions · Answer 3

I only have a question, how can it helps (NOLOCK) in a DWH Database read-only at day and data isn't updated, inserted or deleted?

i don't understand how helps With (NOLOCK) if transaction is not allowed when the database is being queried. i mean, it helps to the statistics or something?

Thanks a lot for your time.

Thx, see you

Nicolás Eduardo Neri Hernández

Mike C SSC-Insane Points: 23224 More actions · Answer 4

Mike C

SSC-Insane

Points: 23224

May 27, 2008 at 12:20 pm

#820745

Eliminates the need for S locks

divison SSC Veteran Points: 277 More actions · Answer 5

Mike C (5/25/2008)
Michael Earl (5/21/2008)
It is a good sign that your new shop does not use it and you should learn to properly design and query databases to make it completely unnecessary.
NOLOCK can absolutely be useful at times, like when SQL Server's row locking behavior is completely unecessary. I refer now to data warehousing applications in which data is only written in batches at night, and only retrieved during daytime business hours. Because there is a clear delineation between when data is written and when it is read in this example, locking is completely unnecessary during the day. In this situation dirty reads are a non-issue and performance is a major issue.

Agreed re' NOLOCK's usefullness... and the replies here have given food for thought.

What surprised me, is that when we were having problems with queries locking out other users, and we experimented with

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

we found it did not prevent locking and contention completely,

whereas (NOLOCK) did prevent locking and contention, completely.

So... we've been using (NOLOCK) on all our read-only queries.

Why are they affecting locking differently?

Is one more reliable (in terms of missing or dups) than the other, for read-only?

(Note that the above finding were under 2000, and now we're on 2005.)

Thanks...