SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NOLOCK Discussion


NOLOCK Discussion

Author
Message
parackson
parackson
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 112
Gila brought some attention to something that is rather interesting to me and was hoping to spark a discussion.... (http://www.sqlservercentral.com/Forums/Topic563258-338-2.aspx#bm563628)

Myself I have not worked in an enviroment that has a massive amount of writes... Just reads...

I have noticed that if I put NOLOCK on a table it has always had a dramatic improvement on the query execution time.... Calling out an index has not always been productive but no matter what NOLOCK would always speed up a query...


Just bear in mind that NOLOCK means dirty reads, so if the data's changing a lot, you may read something you don't want to. There's also the chance of missing rows completely or reading rows twice under certain circumstances.

Nolock essentially means to SQL "I don't care if my result set is slightly inaccurate."

If the data is not changing a lot, then you are probably not running into lock waits and hence nolock won't help much. Nolock is not a silver bullet to be added to every select statement without careful consideration.


So with this all said what is the prevailing wisdom?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118654 Visits: 45547
parackson (9/4/2008)

Calling out an index has not always been productive but no matter what NOLOCK would always speed up a query...


I've already put in my 2 cents on nolock... I'll debate something else.

RE the quoted line, do you mean using a hint to force a particular index? If so...

Index hints (and other hints in general) are massively overused. If SQL's not picking the index that you think is optimal, there is usually a good reason why. It could be something like a cardinality inaccuracy that's leading to inaccurate costs, the query could be written in such a way as to make the index less attractive or it could be that the index you think is optimal actually isn't.

95% of the time, the optimiser does actually know best (especially in the later versions of SQL). In those few, rare cases where hinting an index does lead to a performance improvement and the query is written in the absolute optimal way, then document carefully and test carefully and regularly. The index that's good this week may not be so good in a couple of weeks time when the data volumes and distribution have changed.

A query hint is not a suggestion, it's an order. By using them you are forcing the optimiser to do what you say and preventing it from even evaluating plans that may be far more optimal than the one it's actually going to use.

Personally, I've never found it necessary to use an index hint in a real system and in all but one of the cases where I've seen them used, removing the hint improved performance.

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


parackson
parackson
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 112
Intersting...
From what I have seen sometimes (yet rare) calling out the actual index has had a marked improvement on the execution time.
But except for your "dirty read" comment (which is new knowledge to me..thank you) NOLOCK has been one of the things that I would do to a query, especially one with multiple joins, that would always show a decrease in the overall processing time.

Thanks for the eye opener however.
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31255 Visits: 9730
Nolock reduces query time in two ways. First, it eliminates the establishment of a lock, which means less processing time. Second, it allows the select to read data that updates/deletes aren't done with, instead of waiting for the update/delete to finish.

The first one is the reason so many database developers like Nolock. They notice that the query runs faster pretty much every time, get excited about that, and then use it all over the place, without accounting for the second effect.

Personally, I would prefer accurate data slightly slower over garbage that's fast, in almost all circumstances.

Nolock is pretty much the database equivalent of always eating at McDonald's, instead of doing your own cooking. It's fast, easy, etc., and it will probably end up killing you eventually. At the very least, make sure to budget for blood-pressure medication and a good possibility of bypass surgery.

Same principles apply to Nolock. Make sure, if you use Nolock all over the place, that company execs know that the reports they are using might be just plain wrong, and that they budget for expensive mistakes because of that; and make sure that you yourself budget for a potential unemployment period if that happens.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118654 Visits: 45547
If you know that a set of tables are static and hence locks are an unnecessary overhead, then put those tables into a separate filegroup and mark that filegroup as readonly. SQL will never take a lock on a table in a read-only filegroup.

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


parackson
parackson
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 112
GilaMonster (9/4/2008)
If you know that a set of tables are static and hence locks are an unnecessary overhead, then put those tables into a separate filegroup and mark that filegroup as readonly. SQL will never take a lock on a table in a read-only filegroup.


nice..
Glen Sidelnikov
Glen Sidelnikov
SSC Eights!
SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)

Group: General Forum Members
Points: 957 Visits: 804
Just my 5 cents.
There is no prevailing wisdom. There is a situational wisdom.
You are bringing it from a conversation link where you are suggesting to use a NOLOCK in a stored procedure that is ran on the heavy OLTP environment for a reporting?
First of all, running reporting on OLTP is a faulty design.
I would assume then that running NOLOCK on a sproc for reports will simply return a wrong data.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56651 Visits: 32799
From where I sit, NOLOCK is a crutch, not unlike DISTINCT, that can, in the short term, "fix" issues that are better approached by the hard slog of understanding how to appropriately write TSQL, how to structure the database, how to index the database, etc. The first time someone makes an important business decision based on missing or duplicate data... Or worse still, what if you're writing queries against medical data. I'd sure hate to be responsible for killing people.

Query hints in general are very dangerous things. We had a consultant fix one query with a FAST 1 query hint. The programming team involved proceded to put FAST 1 on EVERY single query inside EVERY procedure. Later, they were experiencing serious performance problems and I looked at their queries. When I saw the FAST 1, I started complaining. Ah, but they had an answer. Look at the graphical execution plan, they said. It's all index seeks and nested loop joins... Unfortunately, the way FAST 1 works is to create two executions, one to return that first row and a second to return everything else. Yes, the first row was coming back faster, the rest was coming back slower. Just removing the FAST 1 and allowing the optimizer to pick good execution plans resulted in a 5x increase.

I don't trust query hints, especially when they're applied as "best practice."

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118654 Visits: 45547
Grant Fritchey (9/4/2008)
Look at the graphical execution plan, they said. It's all index seeks and nested loop joins...


I had a dev that wanted to use the LOOP hint in all his queries, because the nested loop is the 'fastest' join type. I gently pointed out to him that there are conditions where the other two join types are appropriate. They're in the product for a reason.

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


Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56651 Visits: 32799
Yeah, I've run into that one a few times too.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search