Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Effect of NOLOCK on Performance


The Effect of NOLOCK on Performance

Author
Message
RyanRandall
RyanRandall
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1761 Visits: 4652

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.
Aaron Ingold
Aaron Ingold
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 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.
Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 1623

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 MVP
Database Engineer at BlueMountain Capital Management
WesleyB
WesleyB
SSC-Addicted
SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)

Group: General Forum Members
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.


jereme.guenther@gmail.com
jereme.guenther@gmail.com
Right there with Babe
Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)

Group: General Forum Members
Points: 771 Visits: 194
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.
ksh
ksh
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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).





DCPeterson
DCPeterson
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1049 Visits: 432

"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



*****************/
Brett-109535
Brett-109535
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.



Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 1623

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 MVP
Database Engineer at BlueMountain Capital Management
rick payne
rick payne
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 12
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search