November 18, 2009 at 11:25 am
I have a query that is running on a dataset that has 22 million plus records. This data is static for the most part. I'm running a select top 1000 and not using an order by.
When i run this query w/ a (nolock) a different result set is returned that when i run it w/o a (nolock).
For curiosity sake does anyone know exactly what differs between how the data is actually pulled when using a nolock and when not, specifically when not using an order by that would result in a different data set being returned? I realize a nolock should allow u to select records and while the select is running forces those records to no lock the row or datapage.
November 18, 2009 at 11:46 am
In theory doing a TOP N without an Order By could pull different results every time regardless of using nolock.
I just did a quick test on my version of AdventureWorks (extra data), and the STATISTICS IO Results are different even though the Execution Plans are the same. The one without nolock has 3 reads while the one with nolock only has 1. Here's the query:
SELECT TOP 100 * FROM Sales.SalesOrderHeader AS SOH;
Go
SELECT TOP 100 * FROM Sales.SalesOrderHeader AS SOH WITH(NOLOCK);
GO
It appears the nolock only reads the pages needed to get the 10 rows and the one without nolock reads more pages, thus a different result returned. Not sure why it does this, but it does.
Someone else may have more/better information.
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
November 18, 2009 at 11:55 am
That explains why both versions return the same result sets respectively just not the same between the two. 😉
November 18, 2009 at 11:57 am
Thats the question. The one w/o nolock reads more pages. Why? Or better yet why would the one w/ (nolock) read from just one page?
November 18, 2009 at 12:02 pm
I suppose another important question is if the nolock overall will provide a performance boost in certain scenerios due to the less reads than w/o.
November 18, 2009 at 12:03 pm
Wish I knew the answer. Will ask some friends.
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
November 18, 2009 at 12:17 pm
Read this Blog Post on Nolock
Q. Will nolock provide a performance boost?
A. Yes it could, as well as provide unexpected results.
Read the article, it outlines some precautions with Nolock. NoLock was used more as a band-aid in SQL 2000 - mostly due to poorly tuned queries (IMO).
If you need something like NoLock, try using READ_COMMITTED_SNAPSHOT. Some excellent query tuning could also resolve any performance problems related to the cause of using NoLock.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 18, 2009 at 12:23 pm
Itzik Ben-Gan has a post somewhere about the pitfalls of WITH (NO LOCK) and the inconsistent data results it can cause. With SQL 2005+ I just depend of Read Committed, SnapShot (*sp) and steer clear of WITH (NO LOCK).
@SQLvariant
November 18, 2009 at 12:27 pm
I should have asked this before, but if the data is "mostly static" why do you need nolock? With mostly static data you shouldn't have much in the way of concurrency issues.
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
November 18, 2009 at 12:39 pm
i just used the term mostly static so that u guys would answer saying perhaps records are being updated at a high rate causing the issue.
November 18, 2009 at 12:39 pm
CirquedeSQLeil (11/18/2009)
Read this Blog Post on NolockQ. Will nolock provide a performance boost?
A. Yes it could, as well as provide unexpected results.
It could also hurt performance, especially if you have off-row LOB data.
Check out Paul Randal's post:
Performance bug: NOLOCK scans involving off-row LOB data
I posted in the comment about a query I had that did 77MB of IO. With NOLOCK it required 110GB of IO. The fix brought the number down to 388MB using NOLOCK, much better than 110GB, but still 5x more than without.
Rob Boek
November 18, 2009 at 12:40 pm
So thats that. Is there a way for me to close this or do i just leave it?
November 18, 2009 at 12:41 pm
To answer the original question:
Since Top without Order By isn't required to return the same data each time, this isn't a problem. It's just the way it is.
With nolock bypasses lock checking on rows. That means SQL just grabs whatever is most convenient to it. The default has to actually check locks, that means it grabs whatever it's sure doesn't have locks on it. You can control that by specifying the rows to pull, which overrides the default behavior of each.
But really, since you don't care which rows are coming back (based on the lack of Order By with Top), why does it matter? You get 1,000 rows, essentially whichever 1,000 are easiest, which is what you asked for. Why quibble over it if it doesn't matter?
As an aside, if the data is largely static, you can achieve much the same result as "with nolock" by putting it into a read-only file. If you do that, SQL Server doesn't bother with locks. Read-only files (or read-only databases) don't have locking issues. Can speed things up a bit by getting rid of that overhead on high-stress servers. (Of course, this depends on your definition of "largely static".)
- 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
November 18, 2009 at 12:42 pm
Well using a NOLOCK might mean that the engine is not putting a SELECT lock on the table (which it releases as soon as it obtains it).
Other than that, the reson that you would receive different records with a SELECT TOP that doesn't have an ORDER BY; would probably be if someone else reads records from that table that were not previously in the cache... So now they are in cache and available to be selected. Not to mention that the old data pages could have been flushed at some point if the server was under memory pressure.
@SQLvariant
November 18, 2009 at 1:39 pm
I don' treally care weather it comes back different between using nolock or not, i just enjoy finding inconisencies in the system and finding out why these inconsistencies exist for future refference. This isn't a deal breaker by any means just curious thats all. In fact the issue isn't even my own. I was working w a team member when it occured. He and i couldn't explain it and here i am.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply