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 «««56789

The Effect of NOLOCK on Performance Expand / Collapse
Author
Message
Posted Saturday, March 24, 2007 10:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:37 PM
Points: 35,353, Visits: 31,895

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #353697
Posted Wednesday, March 28, 2007 2:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:01 AM
Points: 143, Visits: 62
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

Post #354634
Posted Monday, November 5, 2007 11:24 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 8, 2014 2:42 PM
Points: 13, Visits: 248
Hello, just wanted to add my two cents, now that the Canadian is worth more and has been for longer than most have expected :)

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)
Post #418689
Posted Monday, December 17, 2007 1:55 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:29 AM
Points: 622, Visits: 287
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.
Post #433801
Posted Monday, December 17, 2007 9:20 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, April 4, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #433931
Posted Monday, December 17, 2007 9:49 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:29 AM
Points: 622, Visits: 287
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.
Post #433946
Posted Wednesday, April 22, 2009 2:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 10:27 AM
Points: 136, 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
Post #702778
Posted Thursday, April 23, 2009 5:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 13,872, Visits: 28,270
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #703047
Posted Wednesday, June 17, 2009 7:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 4, 2012 11:13 AM
Points: 257, 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


Post #736495
« Prev Topic | Next Topic »

Add to briefcase «««56789

Permissions Expand / Collapse