Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

NOLOCK Discussion Expand / Collapse
Author
Message
Posted Thursday, September 4, 2008 9:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 5, 2012 9:52 AM
Points: 86, Visits: 103
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?
Post #563902
Posted Thursday, September 4, 2008 9:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 42,462, Visits: 35,524
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 2008, MVP
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

Post #563926
Posted Thursday, September 4, 2008 9:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 5, 2012 9:52 AM
Points: 86, Visits: 103
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.
Post #563934
Posted Thursday, September 4, 2008 9:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #563944
Posted Thursday, September 4, 2008 10:05 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 42,462, Visits: 35,524
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 2008, MVP
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

Post #563959
Posted Thursday, September 4, 2008 10:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 5, 2012 9:52 AM
Points: 86, Visits: 103
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..
Post #563962
Posted Thursday, September 4, 2008 10:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 5, 2013 12:19 PM
Points: 371, Visits: 797
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.
Post #564004
Posted Thursday, September 4, 2008 12:15 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 15,518, Visits: 27,899
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #564066
Posted Thursday, September 4, 2008 12:44 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 42,462, Visits: 35,524
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 2008, MVP
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

Post #564091
Posted Thursday, September 4, 2008 12:51 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 15,518, Visits: 27,899
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #564098
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse