SQL 2005：NOLOCK 提示显着增加读取次数。搞什么？

发布于 2024-07-11 23:48:26 字数 340 浏览 6 评论 0原文

我有一个存储过程，当将 NOLOCK 提示添加到查询中时，它会执行更多读取操作。我很困惑-请问有人知道为什么吗？

细节：查询是：

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

它执行了 40,000 次读取，但行数少于 2,000 条。我确定大部分读取都是由 3 个 TEXT 列引起的。（如果我忽略这些，它会下降到 59 次读取！）但是当我删除WITH (NOLOCK) 时，它会从 40,000 次读取减少到 13,000 次。我重复了几次，因为我想我一定搞砸了，但两者确实是一致的。

原文

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?

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

少女情怀诗 2024-07-18 23:48:26

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

编辑

未提交数据的 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)

NOLOCK reads data from transactions that have not been committed.

EDIT

Demo of NOLOCK read of uncommitted data.

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)

回复收藏 0 原文