September 4, 2008 at 9:11 am
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?
September 4, 2008 at 9:38 am
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
September 4, 2008 at 9:44 am
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.
September 4, 2008 at 9:55 am
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
September 4, 2008 at 10:05 am
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
September 4, 2008 at 10:08 am
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..
September 4, 2008 at 10:56 am
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.
September 4, 2008 at 12:15 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 4, 2008 at 12:44 pm
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
September 4, 2008 at 12:51 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 4, 2008 at 1:50 pm
It is my understanding that nolock only helps when you have a write lock on a row/table. C# has something simular for accessing objects in a multithreaded environment. You can have as many 'read locks' as you want, but as soon as a 'write lock' comes in, your read locks wait until everything is commited. By putting in the nolock on a read, you by-pass the 'wait for clean data' and just read as-is.
If I'm wrong, let me know, because I thought this is how it worked.
edit: does a query have to check every row to see if it's locked or does nolock only speed up a fixed check then any waiting on writes?
eg. hypothetically, would a table that will never get written to see a scaling performance increase or a fixed; is a lock a one time check or does it have to be checked per row, every single time?
September 4, 2008 at 1:54 pm
That's what I thought as well... So except for where exact reporting is required, if you do your job right on the SQL syntax and DB design a NOLOCK statement can only help in CRM environments...
Thats where my experience is, I have not dealt with extremely high transaction enviroments.
September 4, 2008 at 2:12 pm
bcronce (9/4/2008)
It is my understanding that nolock only helps when you have a write lock on a row/table. C# has something simular for accessing objects in a multithreaded environment. You can have as many 'read locks' as you want, but as soon as a 'write lock' comes in, your read locks wait until everything is commited. By putting in the nolock on a read, you by-pass the 'wait for clean data' and just read as-is.If I'm wrong, let me know, because I thought this is how it worked.
edit: does a query have to check every row to see if it's locked or does nolock only speed up a fixed check then any waiting on writes?
eg. hypothetically, would a table that will never get written to see a scaling performance increase or a fixed; is a lock a one time check or does it have to be checked per row, every single time?
I think Grant hit on the point pretty well. If it DOES help that much, then there's some other underlying issue you're glossing over.
To me NOLOCK is the equivalent of a medic treating someone, saying:"Now I'm going to ignore the gunshot wound you have, and just fix everything ELSE, okay?"
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 4, 2008 at 2:17 pm
Honestly, in my opinion, if you do your job right on the SQL syntax, DB design and indexing a NOLOCK statement shouldn't be necessary in any environment.
If you're encountering severe locking problems in SQL 2005 or higher, use SNAPSHOT isolation. That way reads never take locks, but don't read inconsistent data. It's the way Oracle works (more or less). The downside is that TempDB usage increases.
Yes, there will be the occasional case where nolock is necessary. The problem is that it is vastly overused with people adding it to every query without the slightest knowledge of what it actually means or the possible problems they could be causing for themselves.
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
September 4, 2008 at 2:22 pm
NOLOCK does have its uses! The main problem is that many tend to abuse it or use it where is not really appropriate.
* Noel
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply