如何跟踪 SQL 更新的进度？

Question

假设我有一个更新，例如：

  UPDATE [db1].[sc1].[tb1] 
  SET c1 = LEFT(c1, LEN(c1)-1) 
  WHERE c1 like '%:'

此更新基本上将遍历数百万行并修剪冒号（如果 c1 列中有冒号）。

我如何跟踪表中的进展情况？

谢谢

这是sql server 2008

Answer 1

您可以使用 sysindexes 表，它跟踪索引的更改量。因为这是在原子更新中完成的，所以它没有机会重新计算统计信息，因此 rowmodctr 将继续增长。有时，这在小表中并不明显，但对于数百万人来说，它会显示出来。

-- create a test table
create table testtbl (id bigint identity primary key clustered, nv nvarchar(max))

-- fill it up with dummy data. 1/3 will have a trailing ':'
insert testtbl
select
    convert(nvarchar(max), right(a.number*b.number+c.number,30)) + 
    case when a.number %3=1 then ':' else '' end
from master..spt_values a
inner join master..spt_values b on b.type='P'
inner join master..spt_values c on c.type='P'
where a.type='P' and a.number between 1 and 5
-- (20971520 row(s) affected)

update testtbl
set nv = left(nv, len(nv)-1)
where nv like '%:'

现在在另一个查询窗口中，连续运行以下命令并观察 rowmodctr 的不断上升。如果您知道 rowmodctr 需要最终到达的位置，那么 rowmodctr 与 rows 可以让您了解自己的进展情况。在我们的例子中，这一数字是刚刚超过 200 万的 67%。

select rows, rowmodctr
from sysindexes with (nolock)
where id = object_id('testtbl')

在更新表本身时，请不要运行（nolock）计数查询。

Answer 2

不是真的...您可以使用 nolock 提示和相同的 where 进行查询，但这会占用资源

当然这不是带有前导通配符的最佳查询...）

Answer 3

数据库查询，特别是数据操作语言（DML），是原子的。这意味着插入/更新/删除要么成功发生，要么不成功。没有办法查看正在处理哪些记录——对于数据库来说，一旦在 UPDATE 后发出 COMMIT，它们都已更改。即使您能够查看正在处理的记录，当您看到该值时，查询也将继续处理其他记录。

了解流程中哪个位置的唯一方法是将查询编写为在循环内发生的脚本，因此您可以使用计数器来了解处理了多少个查询。这样做很常见，因此定期提交大型数据集，以最大限度地降低需要重新运行整个查询的失败风险。