用于 ETL 与性能的 MS SQL 触发器

Question

我需要为~30个生产表创建触发器可能对生产数据库产生什么影响的信息，这些表捕获任何更新，删除和插入语句，并将以下信息"PK"，"表名称"，"修改时间"放入单独的表中。
我测试它的能力有限，因为我对生产和测试环境都具有只读权限(我可以有一个工作日让 10 个最终用户对其进行测试)。
我估计从这些触发器插入的记录数量每天约为 ~150-200k。
背景： 我有一个项目为非常定制的数据库部署数据仓库+每天都有操作数据的作业运行。更新日期列未被维护(自定义)+表上发生硬删除。我们决定要求 DEV 团队添加触发器，例如：

CREATE TRIGGER [dbo].[triggerName] ON [dbo].[ProductionTable]
FOR INSERT, UPDATE, DELETE 
AS
INSERT INTO For_ETL_Warehouse (Table_Name, Regular_PK, Insert_Date)
SELECT 'ProductionTable', PK_ID, GETDATE() FROM inserted
INSERT INTO For_ETL_Warehouse (Table_Name, Regular_PK, Insert_Date)
SELECT 'ProductionTable', PK_ID, GETDATE() FROM deleted

在核心~30个生产台上。 根据此表，我们将从过去 24 小时中提取增量，并将其推送到数据仓库临时表。

如果有人遇到类似的问题，并且可以帮助我估计它如何影响生产数据库的性能，我将不胜感激。(如果它有效 - 我得救了，如果没有，我需要提出其他解决方案。目前镜像或复制可能很难获得，因为本地 DEV 不知道如何设置它...... 欢迎其他有关如何处理这种情况或执行测试的想法(我的截止日期是星期五 26-01)。

Answer 1

首先，我建议您将表名编码为较小的变量而不是字符变量(30 个表 => tinyint)。

其次，您需要了解要编写的有效负载有多大以及如何：

1. 如果您选择了正确的聚集索引(日期列)，则服务器只需要按顺序逐行输出数据。即使您一次放置所有 200k 行，这也是一项愚蠢的简单工作。

2. 如果你将表名编码为tinyint，那么基本上它必须写：

   • 1byte(表名)+ PK大小(希望是数字，所以<= 8bytes)+ 8字节日期时间 - 所以数据页面上的aprox 17bytes+索引(如果有的话)+日志文件。这是非常轻量级的，同样不会给sql sever带来"真正的"压力。

3. 触发器本身会增加一个小开销，但对于您正在谈论的行数，它可以忽略不计。

我看到的系统以更大的规模做类似的事情，对工作流程的影响接近于零，所以我想说这是一个安全的赌注。这种方法的唯一问题是它在某些情况下不起作用(例如：从 DML 语句输出到临时表)。但是，如果您没有这些阻止程序，那就去做吧。

我希望它有所帮助。