WITH(NOLUCK)

Question

WITH(NOLUCK)

Viewing 15 posts - 16 through 30 (of 31 total)

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

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 1

I faced the same issue here at this company. Prior to coming on board here I had never used the hint, but here's it's literally in every reporting procedure they have (and I mean every single one). With the massive transaction replication environment we have for reporting data (4+ TB), unless they use the hint, nearly every report they run blocks replication and it falls behind (yes, some queries are very poorly written)...which of course impacts many other applications and business processes that also depends on the replicated data.

While it isn't solving the problem at hand I did finally manage to get them to remove the hints from the actual queries by setting the TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top of each procedure.

So now those are in there I can remove the actual hints themselves. In time, I'll look at different isolation levels and remove it completely without them really noticing:

At least that's the dream...

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

sqldriver (6/5/2014)
So then what are plausible alternatives? What can I present as a change to say "you no longer have to risk incorrect results by using NOLOCK because A is alleviating X and B is preventing Y from being an issue."
It seems like implementing Snapshot Isolation or RCSI has some potential drawbacks and need a lot of testing.
This all started with a conversation where I sort of cornered someone into admitting NOLOCK stinks. I suggested a unique index and he said he wasn't confident that data would be unique. I asked if that was due to NOLOCK being in every query, and things got quiet.
Thanks

RCSI has far fewer potential drawbacks than using NOLOCK.

The only issue I have ever seen with RCSI was an application that left transactions open for weeks(!!!) and caused the tempdb to fill up. That will not be an issue with the majority of applications.

I use RCSI with every database I setup, unless I know of a specific reason not to.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 3

Ummm. RCSI? What's that?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

MyDoggieJessie (6/6/2014)
Ummm. RCSI? What's that?

Read-Committed Snapshot Isolation

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

pietlinden (6/5/2014)
If you dig around here even a little, you'll find Gail Shaw's explanation of what NOLOCK really does. You might need to make them read the article she wrote.

I wrote an article on Nolock?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqldriver SSChampion Points: 10440 More actions · Answer 6

GilaMonster (6/6/2014)
pietlinden (6/5/2014)
If you dig around here even a little, you'll find Gail Shaw's explanation of what NOLOCK really does. You might need to make them read the article she wrote.
I wrote an article on Nolock?

There is a small section on it in your SQL Howlers article.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

sqldriver (6/6/2014)
GilaMonster (6/6/2014)
pietlinden (6/5/2014)
If you dig around here even a little, you'll find Gail Shaw's explanation of what NOLOCK really does. You might need to make them read the article she wrote.
I wrote an article on Nolock?
There is a small section on it in your SQL Howlers article.

Ah, right, that one.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pietlinden SSC Guru Points: 63506 More actions · Answer 8

Must be the Alzheimers... I know I have read a few.

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 9

I got bored this morning. I'm sure someone else could do better and more accurate.

***SQL born on date Spring 2013:-)

ScottPletcher SSC Guru Points: 101118 More actions · Answer 10

The notion that RCSI is "free" and should "always" be used is as wrong as the notion that "NOLOCK" is "free" and should "always" be used.

There are at least two reasons:

1) RCSI has 14 bytes per row overhead. Be sure to plan your first rebuild after enabling RCSI accordingly.

2) LOBs can have particularly severe impact, esp. the first time a large LOB value is modified.

Don't get me wrong. RCSI is fantastic when applicable, and I use if often as well, but it should not be considered a "default" setting, particularly for high-update or "high-LOB" tables.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sql-lover SSCoach Points: 18530 More actions · Answer 11

sqldriver (6/5/2014)
hisakimatama (6/5/2014)
Yep, I've had that problem. Found NOLOCK all over the coding in the vendor-provided software at my current workplace my second day there, and reported it to my supervisor, along with the problems it could cause (which he said they've experienced repeatedly), and citations from Gail and various other experts that point out exactly what it does. I even mocked up a test to show exactly how wrong it can make the data you get back.
The response? "Well, there might be something about it you and all of those people don't know about that makes it a good idea!"
... I struggled to maintain a calm demeanor through the rest of the discussion, mostly succeeded, went back to my desk, and greeted it with a few headbutts 🙁
That was exactly my situation. New hire. Sat down my first day to see what was going on. Figured NOLOCK was some low hanging fruit to pick and bring to the devs to show them I know what I'm doing. Searched stored procedure text for %NOLOCK% and got everything in the catalog back.

I hear you.

In my experience, what happened is that over the time, and because it "fixed" a concurrency issue before (or they thought it was fixed) they continue using it. So it becomes a habit. And habits are hard to eliminate. We, humans being, are people of habits. And if we have not seen anything different, we make of those part of our daily routine.

The major problem is when you know is not a good solution, you cited articles, but the person or persons using it are above you in the chain of command. On those case I think that is better to leave stuff "as is". It can be a boomerang that will hit your face hard on next performance review.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 12

What's a performance review? :hehe:

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Sarah Wagner Hall of Fame Points: 3178 More actions · Answer 13

The pictures just made my otherwise crappy Friday. Thanks!

sql-lover SSCoach Points: 18530 More actions · Answer 14

MyDoggieJessie (6/6/2014)
What's a performance review? :hehe:

The one that your supervisor does on you every year? To check if you are doing "Index Seeks" or just "scans" at your job? lol ...

sqldriver SSChampion Points: 10440 More actions · Answer 15

sql-lover (6/6/2014)
MyDoggieJessie (6/6/2014)
What's a performance review? :hehe:
The one that your supervisor does on you every year? To check if you are doing "Index Seeks" or just "scans" at your job? lol ...

I thought it was where they check your pulse and throw you a 1-3% cost of living increase?