WITH(NOLUCK)
-
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
-
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.
-
Ummm. RCSI? What's that?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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.
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Must be the Alzheimers... I know I have read a few.
-
I got bored this morning. I'm sure someone else could do better and more accurate.
***SQL born on date Spring 2013:-)
-
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
-
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.
-
What's a performance review? :hehe:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
The pictures just made my otherwise crappy Friday. Thanks!
-
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 ...
-
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?
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply