quote:

---

Originally posted by eyechart
1000s of page hits per second is not the norm. [understatement]That is a busy system[/understatement], and is really a type of environment that SQL Server has had difficulty handling in the past. Using NOLOCK in that environment is no doubt a must to achieve acceptable performance.

On a system that busy and if the data was a little more critical, it might be worthwhile looking at other RDBMS solutions like Oracle or even DB2.

---

quote:

---

Originally posted by eyechart
We could always ask mmarovic to disable NOLOCK on his system to see what happens. I suggest barracading your office door before you run this test. That should hold off your management long enough for you to run a few tests and post the results on sqlteam for us

---

quote:

---

Originally posted by nosepicker

Using NOLOCK can be dangerous. I have a nightly job that does a simple INSERT ... SELECT. I used to use NOLOCK on the SELECT, but about once a month it would actually dupe some of the records. It was selecting from a relatively high transaction table. Bottom line: NOLOCK can produce unpredictable results at times, so be careful when you use it.

---

quote:

---

Originally posted by Michael Valentine Jones
The question of whether the RDBMS is locking or not locking is moot; it is doing locking and that can’t be turned off in SQL Server. The question is when will NOLOCK in queries make any noticeable improvement in performance.

What I am saying is that most of what I have heard about this falls in the "everyone knows that" or "old wives tale" category.

If you have a system that is experiencing heavy blocking due to long running transactions that update a lot of rows, that is one situation. If you have an application this is mostly short inserts of a few rows, that is another situation. It could be that there is very little benefit to using NOLOCK when there are few long running transactions.

mmarovic only said that using NOLOCK was the rule there; he didn't make any statements about what happened before and after this was implemented, what the nature of the transactions were, or if he was even there before it was implemented. That’s hardly the kind of information you should make an informed decision on.

I would like to see a lot more in the way of solid information based on testing under controlled conditions before implementing something like always using NOLOCK. I’m willing to believe NOLOCK can make performance improvements in some situations. I’m just unwilling to believe it’s some dramatic cure-all without seeing hard evidence.

---

quote:

---

Originally posted by TG

I tried to set up a little test to "prove" my anecdotal intuition:

Here is a very simple test which you guys can correct but this is the kind of scenario that I believe most people resort to using nolock (or read uncommitted transaction isolation level). The situation is where a lot of small transactions are written to the same tables that are included in some fairly intensive searches. The search performance isn't really affected but the writes are the things that benefit.

This may just prove that a bad design can benefit from using nolock but I think this is a common situation

/*
Set up the test

use pubs
if object_id('tempdb..##junk') > 0 
	drop table ##junk

create table ##junk (rowid int identity(1,1) primary key clustered, id int, name sysname)
insert ##junk
select a.id, a.name
from	sysobjects a
cross join sysobjects b
cross join sysobjects c
*/


--in 1 window do the selects 
--run this and at the same time:
--run the other statements in another window

--then uncomment the "with (nolock)"
--switch the other window's "values" to "noLock"
--and restart both windows

declare @i int
set @i = 1
while @i < 27
begin
	select * 
	from ##junk --with (nolock)
	where name like char(96 + @i) + '%'
	set @i = @i + 1
end


/*
--in another window do the writes
set nocount on
-- if object_id('tempdb..#log') > 0 
-- 	drop table #log
-- create table #log (seltype varchar(15), duration int)


declare @i int
	,@st datetime
select @i = 1
	,@st = getdate()
while @i < 501
begin
	set @st = getdate()

	insert ##junk (id, name)
	values (1, 'tg')

	insert #log (seltype, duration)
	--values ( 'nolock', datediff(millisecond, @st, getdate()) )
	values ( 'lock', datediff(millisecond, @st, getdate()) )
	
	set @i = @i + 1
end

--select seltype, min(duration) [min], max(duration) [max], avg(duration) [Avg], count(*) [count] from #log group by seltype
*/


output:
Write stats while selects are conducted

seltype         min         max         Avg         count       
--------------- ----------- ----------- ----------- ----------- 
nolock          0           16          0           500
lock            0           10750       21          500

Be One with the Optimizer
TG

---

quote:

---

Originally posted by sunilagarwal
(1) NOLOCK is same as ANSI defined isolation level 'read uncommitted'

---

quote:

---

Originally posted by Michael Valentine Jones

I wonder how SQL Server handles databases that are in read only mode?...

I guess the same could be asked about a filegroup that was set to read only.

---

quote:

---

Originally posted by BillSheldon

So given that there are inherent issues with using NOLOCK/READUNCOMMITED in terms of data consitency, and given that not having reads blocked by writes I have a couple questions:
1. In terms of overall database performance, how does using the NOLOCK hint on query after query in your application compare against the new SQL Server 2005 feature for "Read Committed with Snapshots" isolation level which does protect data consistency but does not cause reads to be blocked by writes?

sunilagarwal> There are two primary overheads when snapshot option (SI or RCSI) is enabled on the database. First, SQL server will generate versions for updates and deletes. Second, a select may need to traverse version chain to access the older version. So may require additinal IO. For common applications, the version chain is small. You will need to test with your workload. There is a white paper on snapshot isoation @ http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbengine.mspx


2. Given that the primary need for this hint is that some database process has a long running update against one or more tables, is there any prescriptive guidance on how to create atomic transactions which avoid long running write statements?
sunilagarwal> you can only update one table per statement. So you should break your update into its own transaction if possible. If you are updating a large number of rows of a given table, it may run for a long time.

Thanks

Bill Sheldon
MVP for VB.NET

---