SQL 2005:NOLOCK提示显着增加了读取。 WTF?

时间:2021-11-25 09:40:05

I have a stored procedure that does a lot more reads when the NOLOCK hint is added to the query. I'm baffled - does anyone know why, please?

我有一个存储过程,当NOLOCK提示添加到查询时,它会执行更多的读取操作。我很困惑 - 有谁知道为什么,拜托?

Details: The query is:

详细信息:查询是:

SELECT * FROM dbo.<table-name> WITH (NOLOCK).

It was doing 40,000 reads and there are less than 2,000 rows. I established that most of these reads are caused by 3 TEXT columns. (If I omit those it goes down to 59 reads!) But when I delete the WITH (NOLOCK) it goes from 40,000 reads to 13,000. I repeated this a few times because I thought I must have screwed up but it's really consistent both ways.

它正在进行40,000次读取,并且行数少于2,000行。我确定大多数这些读取都是由3个TEXT列引起的。 (如果我省略那些它会降低到59次读取!)但是当我删除WITH(NOLOCK)时,它从40,000读取到13,000。我重复了几次,因为我认为我必须搞砸了,但两种方式都是一致的。

2 个解决方案

#1


NOLOCK reads data from transactions that have not been committed.

NOLOCK从尚未提交的事务中读取数据。

EDIT

Demo of NOLOCK read of uncommitted data.

演示NOLOCK读取未提交的数据。

create table table1 (id int, val int)
go

select * from table1 with ( NoLock)
begin tran
insert into table1 values (1,1)

--Switch to new query window
select * from table1 with ( NoLock)
--switch back
rollback tran
select * from table1 with ( NoLock)

#2


Maybe the read count includes page reads to read the locks?

也许读取计数包括读取锁定的页面读取?

#1


NOLOCK reads data from transactions that have not been committed.

NOLOCK从尚未提交的事务中读取数据。

EDIT

Demo of NOLOCK read of uncommitted data.

演示NOLOCK读取未提交的数据。

create table table1 (id int, val int)
go

select * from table1 with ( NoLock)
begin tran
insert into table1 values (1,1)

--Switch to new query window
select * from table1 with ( NoLock)
--switch back
rollback tran
select * from table1 with ( NoLock)

#2


Maybe the read count includes page reads to read the locks?

也许读取计数包括读取锁定的页面读取?