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 ««12345»»»

The Effect of NOLOCK on Performance Expand / Collapse
Author
Message
Posted Wednesday, January 24, 2007 10:21 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652

Just because I have them handy, here are a few links to discussions on this topic...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64803
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67294




Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.
Post #339417
Posted Wednesday, January 24, 2007 10:23 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:05 AM
Points: 779, Visits: 222
I have to chime in as well. If anything I've found overuse of NOLOCK by people who don't really understand why blocking exists.

Blocking is good.

It ensures that there are not conflicting versions of data at any given time. In some instances that's unimportant and that's when NOLOCK can come in handy. But I've found unfortunately that people use locking hints as a way to artificially increase performance on already poorly written queries. NOLOCK can be good, but it should be used with a lot of forethought, IMO.

This article does a great job of showing how NOLOCK works and how you can realize performance benefits from using it. I think the article would have benefited from a good warning about the reasons for not using NOLOCK, however.
Post #339418
Posted Wednesday, January 24, 2007 11:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 20, 2014 1:29 PM
Points: 1,593, Visits: 1,489

I have to take issue with the following statement:

When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it.

A dirty read is not the result of not issuing locks during a Select query. A dirty read is the result of not honoring locks issued by update/insert/delete queries.

However, a Select query (without using nolock) does do both. It honors locks by other queries and issues locks of its own. The locks it issues are shared locks, meaning other queries requesting a shared lock can still read the data.

If you need an exclusive lock (i.e., to perform an update), a shared lock will block the exclusive lock. On a system that is heavily read and only ocassionally updated, not using nolock can cause extensive blocking and often time outs. This is particularly true when the lock count is high enough to cause SQL Server to escalate the exclusive lock request to a table lock.

So when to use nolock is definitely a judgement call.





My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #339447
Posted Wednesday, January 24, 2007 12:57 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, May 02, 2011 5:35 AM
Points: 493, Visits: 45

I love Aaron Ingolds' quote to be honest: "Blocking is good."
Many people tend to forget this :-(

I too see many situations where people use NOLOCK to compensate poor design and that is always a bad thing.

Post #339471
Posted Wednesday, January 24, 2007 1:24 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 8:30 AM
Points: 769, Visits: 191
Lol, sorry if I don't get out much, but that Canadian penny jock is incredibly funny.  This is the first time I had heard it, I will have to use that sometime.
Post #339482
Posted Wednesday, January 24, 2007 2:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 05, 2010 2:52 PM
Points: 10, Visits: 28

Have used NOLOCK for years for batch processing requirements to attempt to avoid the impact of on-going updates when these updates do not impact the underlying batch process(different columns in same table(s) being updated).

Have seen a nasty side effect of NOLOCK in very high transaction environment (multiple updates per second) with queries being executed multiple times per second:  the SELECT statement used for the NOLOCK can fail due to "block read" issues.  Apparently the optimizer can get confused on which blocks to read (UNCOMMITED versus COMMITTED). 

 

 

 

 




Post #339495
Posted Wednesday, January 24, 2007 2:14 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 1,035, Visits: 408

"I would add that a hint is just that "a hint" not 100% of the time you will get what you asked for"

This seems to be a fairly common misconception.  Even though they are called Hints, they are not mere suggestions to the optimizer.  They OVERRIDE the optimizers default behavior--absolutely!  There's no "maybe" about Hints.  Which is why they should be used with care and only by those who understand the ramifications.




/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Post #339499
Posted Wednesday, January 24, 2007 2:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 15, 2008 9:05 AM
Points: 1, Visits: 3
I operate in Web environment where all READ ONLY functional content is served up out of a SQL Server database and then rendered on the site. All "GET" stored procedures have the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED statement in them. This has the same effect of putting a (NOLOCK) hint on every table in the SP. If you are using Dynamic SQL from the application layer, JAVA or .Net, the isolation level can be altered at the connection level.

We have found this to be a huge performance benefit and have also seen some unexpected benefits. It makes mass data modifications and content deployments to the site doable while we are up without ill effects on the performance of the site. This ofcourse has the same dangers as mentioned above. The read uncommitted isolation level can result in a dirty read.




Post #339507
Posted Wednesday, January 24, 2007 3:28 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 20, 2014 1:29 PM
Points: 1,593, Visits: 1,489

Just wondering if anyone has used the new SNAPSHOT isolation level in SQL 2005 yet, and if anyone has any comments on it's potential as a replacement for nolock/READ UNCOMMITTED.

 

From BOL: SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

SNAPSHOT

Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data.

During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read data that is locked by another transaction that is being rolled back. The SNAPSHOT transaction is blocked until that transaction has been rolled back. The lock is released immediately after it has been granted.

The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database.

A transaction cannot be set to SNAPSHOT isolation level that started with another isolation level; doing so will cause the transaction to abort. If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT. A transaction starts at the BEGIN TRANSACTION statement.

A transaction running under SNAPSHOT isolation level can view changes made by that transaction. For example, if the transaction performs an UPDATE on a table and then issues a SELECT statement against the same table, the modified data will be included in the result set.





My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #339520
Posted Wednesday, January 24, 2007 3:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 8:06 AM
Points: 6, Visits: 7
If you work with data in a multi-threaded app, you have to synchronize data access. (NOLOCK) turns off half of the synchronization code. using nolock creates a temporal race condition. Since Murphy's law is absolute, the race condition will occur during the worst possible time.

I guess the next topic will about how pinning tables in memory is a good idea.

Post #339525
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse