tsql - 添加外键约束会占用内存并导致分页

Question

我在向新创建的空表添加简单的外键约束时遇到了很多问题。引用表很小，其中只有不到 40 条记录，但它被引用了很多。

这是发生的事情:新表成功创建，但是当添加 FK 约束时，它“思考”了很长时间并增加了 CPU 负载。内存使用量增加，服务器开始疯狂分页并变得无响应(连接超时)。取消查询没有帮助。唯一有效的是重新启动服务器，这是非常昂贵的。

这是我试图运行的脚本。我希望 SQL 服务器专家可以提供帮助。谢谢!

USE [my_db]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MyNewTable](
    [Column1ID] [int] NOT NULL,
    [Column2ID] [int] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[MyNewTable]  WITH CHECK ADD  CONSTRAINT [FK_MyNewTable_Column1ID] FOREIGN KEY([Column1ID])
REFERENCES [dbo].[ReferenceTable] ([Column1ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[MyNewTable] CHECK CONSTRAINT [FK_MyNewTable_Column1ID]
GO

编辑:ReferenceTable 是一个看起来像这样的小表:

[Column1ID] [int] IDENTITY(1,1) NOT NULL,
[TxtCol1] [varchar](50) NOT NULL,
[TxtCol2] [varchar](50) NOT NULL,
[TxtCol3] [varchar](200) NOT NULL,
[TxtCol4] [nvarchar](2000) NOT NULL,
[TxtCol5] [varchar](200) NOT NULL,
[BitCol1] [bit] NOT NULL,
[TxtCol6] [varchar](200) NOT NULL,
[NumCol1] [smallint] NOT NULL,
[ExternalColumnId] [int] NOT NULL,
[NumCol2] [int] NOT NULL

Column1ID 被其他表(FK's)引用了很多。 ExternalColumnId 是另一个表的 FK。问题发生在 ALTER TABLE 调用之一期间。不幸的是，这两个是一起运行的，所以我无法说是哪一个导致了它。

编辑:一旦数据库进入“思考”模式，就可以通过将其切换到单模式然后再切换回多用户模式来将其恢复。这比重新启动服务器要好得多，但仍然 Not Acceptable 。

Answer 1

随机思考:你有任何交易吗？

ALTER TABLE 将需要独占访问(大多数 DDL 也是如此)，并且可能是它被模式锁阻塞，这反过来又会阻塞 ReferenceTable，这又会阻塞其他查询......