SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Consensus on NOLOCK
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 3

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 06/22/2006 :  10:32:41  Show Profile  Reply with Quote
Too bad I wasn't aware of this post earlier but I feel like contributing. The whole point behind my post was basically to raise awareness to the guy in need about the NOLOCK option but maybe I should have stressed a little more to be really aware where to use it. I used to be the head developer of a "large" oltp system where we needed to do nasty calculations in real-time to present on the web. The data source changed so fast that using NOLOCK was the smallest of several cons when doing selects. In the post Kristen referred to I stated that 98% of all selects are on historic data that might as well have been cast in stone, and why on earth would I need to bother my server with locking these data when there is no chance whatsoever that they will be changed?

And the question was raised if I had done extensive testing on this or not, and belive me; I have (!) and the results made me a sworn beliver! The most important thing to consider is this: "Will anything ever get corrupted if I use NOLOCK in this select?" If the answer is no, I can't think of any reason why you shouldn't use it.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

BillSheldon
Starting Member

USA
2 Posts

Posted - 06/22/2006 :  10:51:31  Show Profile  Visit BillSheldon's Homepage  Reply with Quote
Sunil,
Thank you for your response, I have follow ups:
1. regarding the balance between the two it appears that while the new feature is more consistent for a company using nolock because their performance is on the edge - the scaling requirements to ensure that changing to snapshot queries won't worsen their performance situation make it a difficult sell to the customer - expecially that 14 bytes of data on each row requirement...

2. Regarding guidance, I agree with your suggestions - in fact I'll go furhter and say the "Update <table ReportData> With Select <Table1Data> join <Table2Data>" style of query routinely causes problems and would be better implemented as a large query with a separate process to then insert the rows of the Report Data to maintain a scalable environment. But what I'm looking for is formal guidance that says that in a production environment of thousands or millions of rows this type of statement is bad because it creates a long running transaction.

Similarlly a statement that while a bulk upload to initialize a database makes sense a daily upload of data for an older file based interface is more scalable when the uploading process executes a larger number of small transacations instead of trying to do a bulk upload in the middle of traditional OLTP activities.

I think there are alot of people that recognize these patterns in database implementation, but unfortunately there are far more people who don't and who have to be convinced every time.

Bill Sheldon
MVP for VB.NET
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.27 seconds. Powered By: Snitz Forums 2000