主页 / dba / 问题 / 138720
Accepted
Nirav Gajjar
Asked: 2016-05-17 23:24:23 +0800 CST

alter column change datatype 是否使用 tempdb 版本存储？

  • 772

我正在运行从 INT 到 BIGINT 的更改列，我的表大小为 250 GB，1 小时后我收到版本存储错误，因为 tempdb 已满。

据我所知，它只占用您的内存页和日志文件，但我不确定 Tempdb。有人可以解释一下这个内部吗？

我正在使用读提交隔离。

select 
    name, 
    snapshot_isolation_state_desc, 
    is_read_committed_snapshot_on 
from sys.databases

...对于此数据库返回：

名称：数据库名称
Snapshot_isolation_state_desc：关闭
is_read_committed_snapshot_on：0
sql-server sql-server-2014

3 个回答

  1. Best Answer

    假设我们谈论的是普通的ALTER TABLE，而不是通过某些向导完成的操作，该向导执行临时表和重命名技巧或类似的操作。

    直列数据类型更改可能会导致表中每一行的更新。这取决于所涉及的类型。您必须考虑这一点：表中的行图像只是一袋字节，直到引擎以某种方式解释。引擎遵循表元数据描述来理解这些字节是什么。元数据会说“从偏移量 12 到偏移量 16 是一个 int，列号 4。从偏移量 17 到偏移量 19 是一个短整数，列号 5”等等。现在，当您执行更改列类型的 ALTER 时，元数据将更改。现在是 100 万美元的问题：新的元数据会正确描述相同的字节包吗？如果是，则 ALTER 行是瞬时的，并且表中的所有行都保持不变。如果没有，那么所有行必须更新。一个巨大的表将导致一个巨大的更新，全部在一个事务中。此更新可能会耗尽您的资源（例如，日志空间不足）。Shanky 说得对，这个“更新”可能采用临时表-> 插入的形式，但这是一个实现细节。

    我前段时间写了一篇关于SQL Server 表列的文章，它展示了其中一些操作是如何发生的，以及它们如何在实际的物理行集元数据中留下证据。

    现在，你说当这个操作完成时你的版本存储空间已经用完了，即使没有启用快照，对吧？版本存储不仅仅用于快照隔离。举一个众所周知的例子，触发器中的 DELETED 伪表由版本存储提供支持。这意味着即使您没有启用快照隔离，也可以使用版本存储。请参阅在 SQL Server 中管理 TempDB：TempDB 基础知识（版本存储：我们为什么需要它？），Sunil 引用了版本存储的 3 个非快照相关用途：触发器、MARS 和在线索引构建。

    SQL 2014 不支持在线更改列，作为 DDL 操作，触发器和 MARS 都不需要行版本控制。所以恕我直言，它不应该增加版本存储。

    在这种情况下，我的主要嫌疑人将是一个可读的辅助文件（这是在黑暗中拍摄的......）。可读辅助节点将所有读取映射到快照隔离这一事实有据可查。我不记得确切的细节，我可能错了，但我认为为了让辅助数据库支持快照隔离，主数据库必须使用版本存储。想想这样一个事实，即辅助是主的 物理副本，并且行大小必须匹配。

    也许不是答案，但评论太长了。

    • 5

  2. 我相信数据库引擎正在创建一个新的临时表并填充它。查看您的执行计划可能会提供线索。您可以在下面的文章中看到引用，但搜索后我在 MS 文档中没有看到任何官方信息。

    此外，如果您使用 SSMS 设计器运行它，它可能具有与执行 alter table 查询不同的行为。如果使用设计器而不是点击保存，则可以编写查询脚本。

    https://www.mssqltips.com/sqlservertip/1903/best-practices-for-sql-server-database-alter-table-operations/

    如果使用 SSMS 执行大多数 ALTER TABLE 操作，则会导致表的删除和重新创建

    如有必要，我会解决它，以便我自己创建一个新表并将数据泵入新表。如果您必须在此过程中将该表保持打开状态以进行写入，则会变得更加复杂。

    此外，您可以在操作运行时临时给 TempDB 额外的 500GB。一个 250GB 的表应该不需要更多，但我在 TempDB 中看到过奇怪的东西。

    • 4

  3. 我在引用一段

    附录 A：使用 Management Studio 从 SQL Server 2005 更改数据类型文档关于更改排序规则和数据类型的影响

    当您更改列的数据类型时，会发生以下事情

    1. 在内部，SQL Server 会创建一个临时表，该表的列属性与目标列中的列属性相同。

    2. 接下来执行 INSERT INTO 语句将数据复制到临时表。

    当他们使用 Profiler 时，可以看到以下查询。

    IF EXISTS(SELECT * FROM dbo.t1)--T1 was table with col C1 whose data type was changed
   EXEC('INSERT INTO dbo.Tmp_t1 (c1)
      SELECT CONVERT(nchar(10), c1) FROM dbo.t1 WITH (HOLDLOCK TABLOCKX)')
    1. 之后，如果索引存在于原始表中，则添加该索引，并将表名重命名为原始表名。

    现在考虑到你有非常大的表，所以临时表也很大，Tempdb 无法容纳它，因此它变满了。

    • 2

相关问题