February 10, 2011 at 5:50 am
carlo 47463 (2/10/2011)
TThen surely there is no chance of dirty data,phantom rows etc? The 2 applications do not select based on the same AppKey and there will also only be one session each from App1 and App2.
If those are the only thing running (no other queries, no other connections) sure. But if that's the case NoLock will make no difference to performance as there won't be any blocking anyway.
Nolock (like all other hints) should be used selectively, after due consideration and only when absolutely necessary. If you have widespread blocking, tune the queries, tune the indexes. That will solve most problems.
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
February 11, 2011 at 2:49 am
carlo 47463 (2/10/2011)
Then surely there is no chance of dirty data,phantom rows etc? The 2 applications do not select based on the same AppKey and there will also only be one session each from App1 and App2.
In theory... no. But this will depend on a few things, including your clustered index. I'm surprised noone mentioned it yet, actually.
If you're familiar with page splits, which is when data on a single memory page 'splits' in two to hold a new record, then this will make sense. If you NOLOCK, you can end up with a read that goes from memory page to memory page, ignoring the indexing order, by using the IAM. You can end up skipping or re-reading data because of this.
Also, if you update an item on a portion of the clustered index (or whatever index it's actually using), it can move around the pages on you, either from after to before where you're reading from, skipping the entry, or from before to after, double-reading the entry.
The fact that your individual apps aren't changing *their* data doesn't mean that the other apps aren't working in the same table, possibly moving things around. You aren't protected in this scenario.
I would recommend taking the suggestion of a few others and looking into snapshot isolation. It sounds like a reasonable solution for what you're looking for.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply