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?
也许读取计数包括读取锁定的页面读取?