CREATE TRIGGER在SQL Server 2005上花了30多分钟

On our live/production database I'm trying to add a trigger to a table, but have been unsuccessful. I have tried a few times, but it has taken more than 30 minutes for the create trigger statement to complete and I've cancelled it.

在我们的实时/生产数据库中,我正在尝试向表添加触发器,但一直未成功。我已经尝试了几次,但创建触发器语句需要超过30分钟才能完成,我已经取消了它。

The table is one that gets read/written to often by a couple different processes. I have disabled the scheduled jobs that update the table and attempted at times when there is less activity on the table, but I'm not able to stop everything that accesses the table.

该表通常由几个不同的进程读取/写入。我已禁用更新表的计划作业,并且在表上活动较少时尝试,但我无法停止访问表的所有内容。

I do not believe there is a problem with the create trigger statement itself. The create trigger statement was successful and quick in a test environment, and the trigger works correctly when rows are inserted/updated to the table. Although when I created the trigger on the test database there was no load on the table and it had considerably less rows, which is different than on the live/production database (100 vs. 13,000,000+).

我不认为create trigger语句本身存在问题。创建触发器语句在测试环境中成功且快速,并且在将行插入/更新到表时触发器正常工作。虽然当我在测试数据库上创建触发器时,表上没有负载,但它的行数相当少,这与实时/生产数据库上的不同(100对13,000,000+)。

Here is the create trigger statement that I'm trying to run

这是我正在尝试运行的create trigger语句

CREATE TRIGGER [OnItem_Updated] 
    ON  [Item]
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    IF update(State)
    BEGIN
        /* do some stuff including for each row updated call a stored 
          procedure that increments a value in table based on the 
          UserId of the updated row */
    END
END

Can there be issues with creating a trigger on a table while rows are being updated or if it has many rows?

在更新行或是否有多行时,是否存在在表上创建触发器的问题?

In SQLServer triggers are created enabled by default. Is it possible to create the trigger disabled by default?

在SQLServer中,默认情况下会启用触发器。是否可以默认创建禁用的触发器?

Any other ideas?

还有其他想法吗?

5 个解决方案

#1

The problem may not be in the table itself, but in the system tables that have to be updated in order to create the trigger. If you're doing any other kind of DDL as part of your normal processes they could be holding it up.

问题可能不在表本身中,而是在必须更新以创建触发器的系统表中。如果您正在进行任何其他类型的DDL作为正常流程的一部分,他们可能会坚持下去。

Use sp_who to find out where the block is coming from then investigate from there.

使用sp_who找出块的来源,然后从那里进行调查。

#2

I believe the CREATE Trigger will attempt to put a lock on the entire table.

我相信CREATE Trigger会尝试锁定整个表。

If you have a lots of activity on that table it might have to wait a long time and you could be creating a deadlock.

如果您在该表上有很多活动,则可能需要等待很长时间才能创建死锁。

For any schema changes you should really get everyone of the database.

对于任何架构更改,您应该真正获得数据库的每个人。

That said it is tempting to put in "small" changes with active connections. You should take a look at the locks / connections to see where the lock contention is.

也就是说,通过主动连接进行“小”改变是很诱人的。您应该查看锁定/连接以查看锁定争用的位置。

#3

That's odd. An AFTER UPDATE trigger shouldn't need to check existing rows in the table. I suppose it's possible that you aren't able to obtain a lock on the table to add the trigger.

那很奇怪。 AFTER UPDATE触发器不需要检查表中的现有行。我想你可能无法获得表上的锁来添加触发器。

You might try creating a trigger that basically does nothing. If you can't create that, then it's a locking issue. If you can, then you could disable that trigger, add your intended code to the body, and enable it. (I do not believe you can disable a trigger during creation.)

您可以尝试创建一个基本上什么都不做的触发器。如果你不能创建它,那么这是一个锁定问题。如果可以,那么您可以禁用该触发器,将您想要的代码添加到正文中,然后启用它。 (我不相信你可以在创建过程中禁用触发器。)

#4

Part of the problem may also be the trigger itself. Could your trigger accidentally be updating all rows of the table? There is a big differnce between 100 rows in a test database and 13,000,000. It is a very bad idea to develop code against such a small set when you have such a large dataset as you can have no way to predict performance. SQL that works fine for 100 records can completely lock up a system with millions for hours. You really want to know that in dev, not when you promote to prod.

部分问题也可能是触发器本身。您的触发器是否会意外更新表的所有行?测试数据库中的100行与13,000,000之间存在很大差异。如果您拥有如此庞大的数据集而无法预测性能,那么针对如此小的集合开发代码是一个非常糟糕的主意。适用于100条记录的SQL可以完全锁定数百万小时的系统。你真的想知道在开发中,而不是当你提升为prod时。

Calling a stored proc in a trigger is usually a very bad choice. It also means that you have to loop through records which is an even worse choice in a trigger. Triggers must alawys account for multiple record inserts/updates or deletes. If someone inserts 100,000 rows (not unlikely if you have 13,000,000 records), then looping through a record based stored proc could take hours, lock the entire table and cause all users to want to hunt down the developer and kill (or at least maim) him because they cannot get their work done.

在触发器中调用存储过程通常是一个非常糟糕的选择。这也意味着你必须遍历记录,这是触发器中更糟糕的选择。触发器必须alawys帐户进行多个记录插入/更新或删除。如果有人插入100,000行(如果你有13,000,000条记录,则不太可能),然后循环基于记录的存储过程可能需要数小时,锁定整个表并导致所有用户想要追捕开发人员并杀死(或者至少是maim)因为他们无法完成他们的工作。

I would not even consider putting this trigger on prod until you test against a record set simliar in size to prod.

我甚至不会考虑将这个触发器放在prod上,直到你测试一个大小相同的记录集来刺激。

My friend Dennis wrote this article that illustrates why testing a small volumn of information when you have a large volumn of information can create difficulties on prd that you didn't notice on dev: http://blogs.lessthandot.com/index.php/DataMgmt/?blog=3&title=your-testbed-has-to-have-the-same-volume&disp=single&more=1&c=1&tb=1&pb=1#c1210

我的朋友丹尼斯写了这篇文章,说明为什么在你有大量信息的情况下测试一小部分信息会给你在开发方面没有注意到的prd造成困难:http://blogs.lessthandot.com/index.php / DataMgmt /?博客= 3&标题=你-测试平台-具有到具有最相同体积&DISP =单&更= 1& C = 1&TB = 1&PB = 1#C1210

#5

Run DISABLE TRIGGER triggername ON tablename before altering the trigger, then reenable it with ENABLE TRIGGER triggername ON tablename

在更改触发器之前运行DISABLE TRIGGER triggername ON tablename,然后使用ENABLE TRIGGER triggername ON tablename重新启用它

#1