July 30, 2015 at 5:04 am
Why oh why oh why do people insist on using WITH (NOLOCK) instead of doing things like setting transaction isolation levels or fixing indexes to resolve performance issues?
Work thing. I've been trying to stamp out WITH (NOLOCK) use and now because things "aren't working" one group is trying to throw it back in.
ARGH!
Well, this is my battle for the week.
EDIT: Seriously, can anyone give me a real reason why WITH (NOLOCK) might be functionally necessary for production code?
July 30, 2015 at 5:14 am
Brandie Tarvin (7/30/2015)
EDIT: Seriously, can anyone give me a real reason why WITH (NOLOCK) might be functionally necessary for production code?
Incompetent developers.
Prior to SQL 2005 there might have been. Since SQL 2005, severe reader-writer blocking problems can likely be fixed with READ COMMITTED SNAPSHOT/SNAPSHOT rather than READ UNCOMMITTED.
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
July 30, 2015 at 6:09 am
Brandie Tarvin (7/30/2015)
Why oh why oh why do people insist on using WITH (NOLOCK) instead of doing things like setting transaction isolation levels or fixing indexes to resolve performance issues?Work thing. I've been trying to stamp out WITH (NOLOCK) use and now because things "aren't working" one group is trying to throw it back in.
ARGH!
Well, this is my battle for the week.
EDIT: Seriously, can anyone give me a real reason why WITH (NOLOCK) might be functionally necessary for production code?
It's a combination of gross ignorance and laziness. They don't understand the how and why of locking and blocking. Rather than learn this, and then do the necessary rearchitecting of structure and code, they take the shortcut and use the magic turbo button, NOLOCK.
The thing about it that really makes me crazy is that if they were at least efficient in their laziness, they'd just change the connection string to READ_UNCOMMITTED. Instead they're doing a ton of extra work in their attempt to be lazy, exposing more ignorance.
It's a giant pain.
"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
July 30, 2015 at 6:50 am
Brandie Tarvin (7/30/2015)
...EDIT: Seriously, can anyone give me a real reason why WITH (NOLOCK) might be functionally necessary for production code?
To keep the answer brief and firm: No.
The risk encountering nolock downsides is just to big. They will bit you in the back.
Would they use float for all their calculations ? ... strike that last question ... they just might
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 30, 2015 at 6:55 am
ALZDBA (7/30/2015)
Brandie Tarvin (7/30/2015)
...EDIT: Seriously, can anyone give me a real reason why WITH (NOLOCK) might be functionally necessary for production code?To keep the answer brief and firm: No.
The risk encountering nolock downsides is just to big. They will bit you in the back.
Would they use float for all their calculations ? ... strike that last question ... they just might
@=)
Not ALL their calculations. But yes, float is used in this office.
July 30, 2015 at 6:56 am
Grant Fritchey (7/30/2015)
Brandie Tarvin (7/30/2015)
Why oh why oh why do people insist on using WITH (NOLOCK) instead of doing things like setting transaction isolation levels or fixing indexes to resolve performance issues?Work thing. I've been trying to stamp out WITH (NOLOCK) use and now because things "aren't working" one group is trying to throw it back in.
ARGH!
Well, this is my battle for the week.
EDIT: Seriously, can anyone give me a real reason why WITH (NOLOCK) might be functionally necessary for production code?
It's a combination of gross ignorance and laziness. They don't understand the how and why of locking and blocking. Rather than learn this, and then do the necessary rearchitecting of structure and code, they take the shortcut and use the magic turbo button, NOLOCK.
The thing about it that really makes me crazy is that if they were at least efficient in their laziness, they'd just change the connection string to READ_UNCOMMITTED. Instead they're doing a ton of extra work in their attempt to be lazy, exposing more ignorance.
It's a giant pain.
Interesting to read this this morning. I have worked with developers who designed an application with 2 connection methods, 1 for read-write and 1 for read-only that did set the conneciton to READ_UNCOMMITTED. Also, I just got an email from a friend of mine asking for help with a query which had WITH (NOLOCK) after every table. I warned him of the issues and pointed him toward READ_COMMITTED_SNAPSHOT.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2015 at 7:03 am
NOLOCK hints can be useful to get estimates of certain data.
To use it on every single table and every single query is just nonsense and lack of efficiency in laziness as Grant pointed out.
July 30, 2015 at 7:28 am
Luis Cazares (7/30/2015)
NOLOCK hints can be useful to get estimates of certain data.
Yup. In fact I told some people recently to put nolock onto a bunch of queries. They were populating a dashboard showing number of documents captured, number of claims processed, number of errors encountered etc. The queries were causing blocking and it doesn't matter if they're not 100% accurate.
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
July 30, 2015 at 7:52 am
GilaMonster (7/30/2015)
Luis Cazares (7/30/2015)
NOLOCK hints can be useful to get estimates of certain data.Yup. In fact I told some people recently to put nolock onto a bunch of queries. They were populating a dashboard showing number of documents captured, number of claims processed, number of errors encountered etc. The queries were causing blocking and it doesn't matter if they're not 100% accurate.
So would the SNAPSHOT isolation level not have worked in this instance?
July 30, 2015 at 7:54 am
As I dig into this issue (Execution Plans HO!), I come across a thread in which one respondent posts a link to Gail's blog and then Grant shows up to promote his execution plan book (by posting a link to the free PDF).
Hee. You two get around.
July 30, 2015 at 7:57 am
Brandie Tarvin (7/30/2015)
GilaMonster (7/30/2015)
Luis Cazares (7/30/2015)
NOLOCK hints can be useful to get estimates of certain data.Yup. In fact I told some people recently to put nolock onto a bunch of queries. They were populating a dashboard showing number of documents captured, number of claims processed, number of errors encountered etc. The queries were causing blocking and it doesn't matter if they're not 100% accurate.
So would the SNAPSHOT isolation level not have worked in this instance?
For me, it would be an overkill.
July 30, 2015 at 8:04 am
Brandie Tarvin (7/30/2015)
As I dig into this issue (Execution Plans HO!), I come across a thread in which one respondent posts a link to Gail's blog and then Grant shows up to promote his execution plan book (by posting a link to the free PDF).Hee. You two get around.
Just remember, read the second edition. The guy who wrote the first edition was an idiot. Soon, after the third edition comes out, the guy who wrote the second edition will become an idiot.
"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
July 30, 2015 at 8:07 am
Brandie Tarvin (7/30/2015)
GilaMonster (7/30/2015)
Luis Cazares (7/30/2015)
NOLOCK hints can be useful to get estimates of certain data.Yup. In fact I told some people recently to put nolock onto a bunch of queries. They were populating a dashboard showing number of documents captured, number of claims processed, number of errors encountered etc. The queries were causing blocking and it doesn't matter if they're not 100% accurate.
So would the SNAPSHOT isolation level not have worked in this instance?
It would have, but I'd have been adding the row versioning overhead on every single data modification in the entire database (hundreds of inserts/sec) just so that a small number of dashboard queries can run without blocking.
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
July 30, 2015 at 8:08 am
Grant Fritchey (7/30/2015)
Brandie Tarvin (7/30/2015)
As I dig into this issue (Execution Plans HO!), I come across a thread in which one respondent posts a link to Gail's blog and then Grant shows up to promote his execution plan book (by posting a link to the free PDF).Hee. You two get around.
Just remember, read the second edition. The guy who wrote the first edition was an idiot. Soon, after the third edition comes out, the guy who wrote the second edition will become an idiot.
Oh don't get me started. Whoever wrote the older posts on that 'SQLIntheWild' blog was clearly clueless and a terrible writer.
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
July 30, 2015 at 8:23 am
GilaMonster (7/30/2015)
Grant Fritchey (7/30/2015)
Brandie Tarvin (7/30/2015)
As I dig into this issue (Execution Plans HO!), I come across a thread in which one respondent posts a link to Gail's blog and then Grant shows up to promote his execution plan book (by posting a link to the free PDF).Hee. You two get around.
Just remember, read the second edition. The guy who wrote the first edition was an idiot. Soon, after the third edition comes out, the guy who wrote the second edition will become an idiot.
Oh don't get me started. Whoever wrote the older posts on that 'SQLIntheWild' blog was clearly clueless and a terrible writer.
At least things are getting better, not worse.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 49,741 through 49,755 (of 66,549 total)
You must be logged in to reply to this topic. Login to reply