SQL Clone
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210295 Visits: 41973

I think we've pretty much beat the subject of when and when not to use NOLOCK to death and the author pretty much summed up all that in his new introduction to the article. I give him credit for having the hair to come back with that summary after everyone got through with the previous article on the same subject.

At this point, I'd be more concerned with asking why someone is storing a date as VARCHAR or using a WHILE loop to create simple test data!!!!



--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Wayne-153714
Wayne-153714
SSChasing Mays
SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)

Group: General Forum Members
Points: 636 Visits: 91
Good one, Jeff - you got me there! While loop indeed. Tsk, Tsk :-).CROSS JOIN would have perhaps been much better, or perhaps you can recommend something better?

And the bit about a date field varchar - eh, you got me there - I missed that one! Remember this was just a quick and messy script set up to create test data, but I'll try to be more careful next time. :-)

Cheers,

Wayne


When in doubt - test, test, test!

Wayne

Hugo Shebbeare
Hugo Shebbeare
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 258
Hello, just wanted to add my two cents, now that the Canadian is worth more and has been for longer than most have expected Smile

Please don't use nolock on tables that have text/ntext/image data types, it causes excessive cpu useage according to Idera's tools, plus gives 7105 errors frequently in sql server logs, which cannot be good for any instance in production.

Huggy Bear, Huggy ji, Pugo, Huge...token mcdba since 2001.

Town of Mount Royal, QC
514 812 5087 (txt also)
hugo@intellabase.com (msn im also)

Sorin Petcu
Sorin Petcu
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 367
Hi,

Using the LOCK hint to SELECT query it is a very good issue. I have tested it in my production environment and it is very useful. Before that it was a lot of lock waits and deadlocks. There is an ERP application which is running here and the isolation level of transactions was set in the code of this application. So, I have used this hint.
I want to point the author of this article to make tests of this hint on concurrential systems. There is a huge difference between using and not using LOCK hint.

In Theory, theory and practice are the same...In practice, they are not.
timothyawiseman
timothyawiseman
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2338 Visits: 920
This is an excellent article with the author's notes, though the line warning about only using it where a dirty read is acceptable cannot be overemphasized. I have a couple of particularly heavily queried tables that I have a procedure automatically generate reports on. Due to their heavy use, there are often more locks on. A couple of weeks ago, I added a no lock clause and saw the average running time drop from over an hour to under twenty minutes.

The key though was that the report was returning summarized information and the management really needed an estimate more than an exact number. I could accept the risk of dirty reads and even duplicate row returns. Nolock should be used with greatest caution when the integrity of the results returned is of importance.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Sorin Petcu
Sorin Petcu
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 367
I knew there are dirty reads and assumed the responsability. But from my ERP system point of view, there is no constraint to view dirty data.

In Theory, theory and practice are the same...In practice, they are not.
rubes
rubes
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

Group: General Forum Members
Points: 396 Visits: 398
I am a big fan of nolock and have used it very successfully (and prudently) over the years. I must admit that this whole thread has made me reconsider how I use it.

There is one thing that has not been clarified. In the case of duplicate rows (which causes me the most concern and is something that we have seen a few times in our environment), does this behavior only occur when 1) a clustered index is updated and 2) a non-sequential clustered index is inserted against? I noticed that Tony Rogerson's example of this involved updating a clustered index, causing the row(s) to physically move because of a page split. Could this behavior occur with non-clustered index modifications as well?

Regards,
Rubes
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96463 Visits: 33013
If the data is being pulled from a covering non-clustered index, absolutely. I'm not so sure it would happen during a key lookup.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Brian Munier
Brian Munier
Right there with Babe
Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)

Group: General Forum Members
Points: 743 Visits: 80
Without reading all of the past comments, I am going to post what I believe is a valid reason to use NOLOCK.

We have multiple database servers, support one database per customer, approximately 15 customers per SQL Server (2000, 2005, enterprise edition on clustered servers).

We have data that is customer independant, and environment independant, for example system tables with lists of valid file types. Fortunately none of our columns are text, ntext, varbinary, etc... as pointed out earlier as being an issue. (Excellent point however) Basically we have int, varchar, nvarchar, and datetimes. We use views in the customer databases to look at this data.

The reason I feel safe using it ONLY for this customer independant, evnvironment data views is that the data is very static, not very large, and basically used for data integrity.

The reason we implemented the with NOLOCK hint, was that we had hundreds of thousands of connections and locks in CommonData on our production systems which presented a significant overhead.

We attempted making the institution independant data database read-only, but the delays in changing it to/from read only in order to perform maintenance were very significant and could shut down our production customers.

In a general on-line transaction processing database, I would not recommend the NOLOCK hint, but getting static data from a common database, seems safe to me. Just be sure to keep your common database defragmented to prevent the duplicates.

Brian Munier


Brian
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