February 13, 2012 at 12:03 pm
kschwid (2/13/2012)
I also stated before that I only use them for reporting, so there are no inserts or updates involved, using NoLock on some of the tables in my query can optimize performance
If there's no updates/inserts, then there will be no locks that need to be ignored, so nolock gives no gain. It is not a go-faster switch it just allows queries to read through locks that, in your case don't exist.
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 13, 2012 at 12:51 pm
On the original question, Select takes a "shared lock", meaning multiple Selects can run on the same rows at the same time and won't block each other. No need to NoLock or Uncommitted on that, it just increases risk without improving functionality/performance in most cases.
If the data is relatively static, like "last month's sales figures", then shared locks won't even block updates to it (since there are none), and NoLock, et al, are even less relevant.
But, keep in mind, even insert-only data can result in locks being needed on indexes that aren't optimized for "end of index inserts". In other words, any index that doesn't have a sequentially increasing leading edge. Most reporting tables need to support multiple indexes, and not just ones with incremental leading edges, so you actually do need locks on those rows, or you risk a variety of errors, potentially including errors that can interrupt end users in what they are trying to do. (As well as the ones that falsify business information, potentially resulting in poor decisions by managers.) That's where the various snapshot isolation flavors come in handy, as you get performance comparable to NoLock, without most of the drawbacks associated with that.
But if the data isn't being modified, shared locks won't block each other, and locking/nolock become moot points that don't really affect performance.
- 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
February 13, 2012 at 12:55 pm
kschwid (2/13/2012)
I don't think it will hurt for you to have nolock on.Any stored procedures that I write I add that in, regardless of how long something takes to run. A lock and error can occur in a query that only runs for a second, technically. So Nolock won't hurt.
That's like driving without a seatbelt. It makes getting into and out of the car faster, and makes it easier to reach around and get something out of the back seat. Most of the time, you won't be in an accident, and the lack of seatbelt won't matter. Lots of people drive without them because of that.
Go visit an accident scene ONCE where someone without a seatbelt was thrown through the windshield, and you'll either never drive again, or you'll wear a seatbelt every time you do.
NoLock is the database equivalent of driving without a seatbelt. Once you've had to clean up the horrible mess it occassionally makes, even once, you'll curse it's overuse from then on. Using it as a blanket policy, is counting on luck to get you through.
I prefer competence over luck, when it comes to doing my job. But that's me.
- 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
February 13, 2012 at 9:49 pm
GSquared (2/13/2012)
On the original question, Select takes a "shared lock", meaning multiple Selects can run on the same rows at the same time and won't block each other. No need to NoLock or Uncommitted on that, it just increases risk without improving functionality/performance in most cases [...] But if the data isn't being modified, shared locks won't block each other, and locking/nolock become moot points that don't really affect performance.
Just to add:
One of the arguments in favour of NOLOCK used to be that it reduced the overhead of acquiring and releasing locks. Modern builds of SQL Server contain optimizations to significantly reduce the effect of this where a page contains no uncommitted changes (and the most recent change to the page was not rolled back). Overall, read committed on a relatively static data set adds very little overhead, and provides a couple of useful guarantees (though not as many as a lot of people expect). In some cases, however, I have seen a useful performance boost at read committed by explicitly taking a table-level shared lock (TABLOCK) rather than relying on the engine to make the appropriate choice.
The Case of the Missing Shared Locks
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 13, 2012 at 9:55 pm
kschwid (2/13/2012)
I also stated before that I only use them for reporting, so there are no inserts or updates involved, using NoLock on some of the tables in my query can optimize performance and still give little chance for corrupt/phantom data.
There are two main reasons READ UNCOMMITTED can boost performance a bit: avoiding taking locks, and enabling allocation-order scans. Taking a single shared table lock (assuming no concurrent modification activity) provides the same benefits. I don't disagree that there are cases where READ UNCOMMITTED is the correct isolation level to choose, but it is overused in my experience.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply