June 5, 2014 at 8:41 am
How do you get people to stop using NOLOCK?
I've sent the devs plenty of articles about it, I've pointed out that side by side the queries don't run any faster with it. But it's in every single piece of code, going back years. The worst part is that they're not bad devs otherwise.
They all tell me that in their previous shops NOLOCK was the norm.
It's also in every single query produced by software we have on multiple servers and have spent a Kajagoogoollion dollars on.
Do I live in a SQL bubble getting all my information from here and like-minded websites? Is the NOLOCK hate some high minded ivory tower stuff that normal people don't have time to care about?
I'm mainly asking because it came as quite a shock to me. I thought everyone knew how potentially disastrous using it can be.
Thanks
June 5, 2014 at 8:56 am
Not sure what to say. 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.
<sarcasm>What's wrong with a little reading and summarizing data that's not been committed... I mean, you're among friends, right? </sarcasm>
No, seriously though, explain to or show them what NOLOCK actually does. If your query results need to be 100% accurate, NOLOCK is a huge mistake.
June 5, 2014 at 9:25 am
pietlinden (6/5/2014)
Not sure what to say. 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.<sarcasm>What's wrong with a little reading and summarizing data that's not been committed... I mean, you're among friends, right? </sarcasm>
No, seriously though, explain to or show them what NOLOCK actually does. If your query results need to be 100% accurate, NOLOCK is a huge mistake.
I've sent articles by, in no particular order:
Gail Shaw, Kendra Little, Paul Randal, Grant Fritchey, Brent Ozar, Jason Strate, and Andrew Kelly.
June 5, 2014 at 9:35 am
Oh, I'm sure. Gail's was just the first I remembered. And if the boss can't take their word for it then all bets are off...
June 5, 2014 at 9:40 am
Most of the time, people believe that it won't happen to them.
In our case we could get some people to stop using them when we demonstrated that we were missing rows of information and presenting that wrong information to the board.
June 5, 2014 at 9:40 am
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 🙁
- 😀
June 5, 2014 at 9:53 am
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.
June 5, 2014 at 11:06 am
The biggest issue by far of that hint is not reading in a row that is being inserted/updated and getting an incorrect value. Most people think that is the issue. The real issue is when you get back duplicate and/or missing rows because the data is moved between pages as the result of a page split. This is where the truly ugly parts of read uncommitted come into play.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 5, 2014 at 11:47 am
Jason Strate has a great example that shows the effects of NOLOCK.
But, one little thing, just on the side, why write code where you put NOLOCK everywhere. If you're really going down that path, why not just set the connections to READ_UNCOMMITTED. It'll make the code a lot more clean. If, just as dangerous.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 5, 2014 at 11:49 am
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
June 5, 2014 at 11:50 am
Grant Fritchey (6/5/2014)
Jason Strate has a great example that shows the effects of NOLOCK.But, one little thing, just on the side, why write code where you put NOLOCK everywhere. If you're really going down that path, why not just set the connections to READ_UNCOMMITTED. It'll make the code a lot more clean. If, just as dangerous.
It's quite funny that the same people that say using NOLOCK on every query will say that it's not a good idea to use READ_UNCOMMITTED.
I wonder why? :hehe:
June 5, 2014 at 12:04 pm
Here is the article from Jason Strate that Grant was mentioning.
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
And here are a couple of others ones that are also quite good.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 5, 2014 at 12:42 pm
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
The solutions are usually along three fronts. The easy one, albeit with a lot of testing, you already hit, Read Committed Snapshot. The second, ensuring you have the right structure for your data. The third, tune the queries. The problem is, nolock can, for a time, mask very serious problems with structure and code, assuming you don't start seeing all the bad data. But even nolock gets overwhelmed eventually. Then you have to do the hard work anyway.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 5, 2014 at 2:14 pm
I've been working pretty hard to first bring all the server settings in line with best practices and then dig in to the queries. I've been using a lot of your code to get into the plan cache and also reading your executions plan book to try to get things right.
Most of the improvement so far has been the obvious stuff: indexing, implicit conversion, scan hunting, bad row estimates. But the NOLOCK thing just bothers me more than everything else. Like, what am I doing all this for? To optimally deliver wrong data? :unsure:
June 5, 2014 at 2:31 pm
That's an excellent point. Sounds like you're doing fine. If you hit issues, you know where to go for help.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply