SQL Server，将NTEXT转换为NVARCHAR（MAX）

Question

10

我有一个数据库，其中有许多字段当前都是NTEXT类型。

升级到SQL 2005后，我们对将其转换为NVARCHAR(MAX)进行了一些性能测试。

如果您阅读本文章：

它解释了简单的 ALTER COLUMN 操作不会重新组织数据为行。

我对此进行了验证。如果仅运行 ALTER COLUMN，某些区域实际上表现更糟糕。然而，如果我对这些字段运行 UPDATE 表 SET 列 = 列，我们就会获得极大的性能提升。

我的问题在于该数据库包含数百个具有数百万条记录的这些列。在低性能虚拟机上进行的简单测试（一个只包含1个NTEXT列、7百万条记录的表）需要5个小时才能完成更新。

是否有人可以提供任何建议，以更有效地更新数据，减少停机时间和锁定？

编辑：我的备份解决方案是随时间分块更新数据，但对于我们的数据，这会导致性能更差，直到所有记录都已更新。因此，我仍在寻找更快的方法来更新数据。

- Robin Day

5个回答

3

如何分批运行更新 - 每次更新1000行。

您需要使用一个while循环，递增计数器，对应于每次迭代中要更新的行的ID。这可能不会加快更新所有700万条记录所需的时间，但它应该使用户由于记录锁定而遇到错误的可能性大大降低。

- Yaakov Ellis

是的，这是我的当前计划，需要大量的组织工作才能让一切顺利，但应该是可以实现的。我也只会逐个更新字段，以便在准备好更新该列之前不会出现性能下降的情况。我只是希望有一些神奇的解决方案存在，而我却不知道它们。 - Robin Day

以这种方式批量运行通常可以提高更新时间，特别是当你处理此类记录时。 - HLGEM

3

如果您可以安排停机时间：

备份数据库
将恢复模式更改为简单模式
从要更新的表中删除所有索引
添加一个带有非聚集索引的列maintenanceflag（INT DEFAULT 0）
运行： UPDATE TOP 1000 tablename SET nvarchar from ntext, maintenanceflag = 1 WHERE maintenanceflag = 0

根据需要多次运行（在延迟循环内）。

完成后，进行另一次备份，然后将恢复模式改回原来的状态并添加旧索引。

请记住，该表上的每个索引或触发器都会导致额外的磁盘I/O，并且简单恢复模式可以最小化日志文件I/O。

- John

1

在低性能虚拟机上运行数据库测试并不能真正反映生产性能，因为涉及到大量IO操作，需要快速的磁盘阵列，而虚拟化会限制其性能。

- ck

0

你也可以考虑测试一下是否有SSIS包可以更有效地完成这个任务。

无论你做什么，都要将其变成一个自动化的过程，可以在非工作时间安排并运行。尽可能减少尝试访问数据的用户数量，一切都会更快。如果可能的话，挑选出三到四个最关键的进行更改，并在通常的非工作时间内关闭数据库进行维护（以单用户模式进行）。一旦你解决了最关键的问题，其他问题就可以每晚安排一两个。

- HLGEM

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- John · Accepted Answer

如果您无法获得计划停机时间...

创建两个新列： nvarchar(max) processedflag INT DEFAULT 0

在processedflag上创建非聚集索引

您可以使用UPDATE TOP (按主键顺序更新)。

只需在更新期间将processedflag设置为1，以便下一次更新仅更新processed flag仍为0的位置

您可以在更新后使用@@rowcount查看是否可以退出循环。

建议在每个更新查询之后使用WAITFOR几秒钟，以便其他查询有机会获取表上的锁，并且不要过载磁盘使用。