with (nolock) replacement/substitute

Question

with (nolock) replacement/substitute

nplinares

Old Hand

Points: 335
More actions
September 25, 2008 at 5:46 am

#194145

hi, currently i am using with (nolock) command on almost of my query to speed up the query process. but out DBA tells us that we are not allowed to use with (nolocks) anymore.
is there a way we can enhance the speed of our query with using the with (nolock) command?

Viewing 8 posts - 1 through 7 (of 7 total)

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

To prevent loss of speed with your queries without the WITH (NOLOCK) option, you must keep all exclusive locks (INSERTs, UPDATEs) to a minimum. That means optimizing the code to only set the exclusive lock when required and release them asap (COMMIT transaction). You also need to look at the use of indexing (adding or removing) to speed up the queries that INSERT or UPDATE.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

one more point is that you shouldn't be using with (nolock) or SET ISOLATION LEVEL for performance.

You should only be using these if you need to alter or force locks

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

You should thank your DBA - that is a very good decision they have made.

Leo-696352 SSCrazy Points: 2708 More actions · Answer 4

Hi Michael,

I have the same situation, My Senior DBA told me that he use WITH (NOLOCK) on every query in Store Procedure and he said that for the better performance.

Why do you think WITH (NOLOCK) Option are not the good idea any more?

Can you explain? Becasue I am very confused, some are saying great. but some aren't.

Thanks.

Leo

noeld SSC Guru Points: 96590 More actions · Answer 5

You should know that with ( nolock) can read dirty values and even return duplicates if it hits a hot spot.

If your queries are reading mostly historic data you should be fine

* Noel

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

noeld (9/25/2008)
You should know that with ( nolock) can read dirty values and even return duplicates if it hits a hot spot.
If your queries are reading mostly historic data you should be fine

That's the thing though. If you're reading historical data, there should be no locks anyway, so the option should be useless.

If the data is NOT historical (meaning - data is getting updated), like Noel mentioned, you get dirty data. That means you don't really know what state you might be retrieving for any given row (committed/uncommitted/partial committed), whether you picked up a given row multiple times or if it got skipped, etc...

And - nolock only helps with certain kinds of locks. It does not get past schema locks for example.

So - if you need a high degree of accuracy, you don't WANT nolock. If your data is historical, you shouldn't NEED nolock. If your data updates a lot, and you don't much care about accuracy of results (ugly way to say it, but that's about what it boils down to), then Nolock might be your friend.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Leo-696352 SSCrazy Points: 2708 More actions · Answer 7

Leo-696352

SSCrazy

Points: 2708

September 25, 2008 at 3:38 pm

#877373

That explained all..............

Thank you.

Leo