根问题

根本的问题是,MS试图用RI设计SSIS,但是它们是懒惰的,允许级联删除发生,而不是显式处理它们.

Out of the Box,the new SSIS 2012 catalog database (SSISDB) has some basic indexing applied,with referential integrity set to do cascade deletes between most tables.

Enter the sql Agent job,“SSIS Server Maintenance Job.” This job by default is set to run at midnight daily,and uses two catalog parameters to function: “Clean Logs Periodically” and “Retention Period (days).” When these are set,the maintenance job purges any data outside of the noted retention period.

This maintenance job deletes,10 records at a time in a loop,from internal.operations and then cascades into many tables downstream. In our case,we have around 3000 operations records to delete daily (10 at a time!) that translates into 1.6 million rows from internal.operation_messages. That’s just one downstream table! This entire process completely,utterly locks up the SSISDB database from any SELECT/INSERT data

解析度

直到MS更改事情的工作原理,支持的选项是

move the maintenance job schedule to a more appropriate time for your environment

我知道在目前的客户端,我们只是在几小时内加载数据,所以SSISDB在工作时间安静.

如果在安静期间运行维护工作不是一个选择,那么您正在制定自己的删除语句来尝试将级联删除更少.

在我目前的客户端,我们已经在过去10个月里每晚运行约200个包裹,也是365天的历史.我们最大的桌子,一个数量级.

Schema    Table                   RowCount
internal  event_message_context   1,869,028
internal  operation_messages      1,500,811
internal  event_messages          1,803

所有这些数据的驱动程序,内部操作只有3300行,这符合菲尔关于数据增长呈指数形式的评论.

所以,识别要清除的operation_id和从叶表中删除的内核,内部操作表.

USE SSISDB;
SET NOCOUNT ON;
IF object_id('tempdb..#DELETE_CANDIDATES') IS NOT NULL
BEGIN
    DROP TABLE #DELETE_CANDIDATES;
END;

CREATE TABLE #DELETE_CANDIDATES
(
    operation_id bigint NOT NULL PRIMARY KEY
);

DECLARE @DaysRetention int = 100;
INSERT INTO
    #DELETE_CANDIDATES
(
    operation_id
)
SELECT
    IO.operation_id
FROM
    internal.operations AS IO
WHERE
    IO.start_time < DATEADD(day,-@DaysRetention,CURRENT_TIMESTAMP);

DELETE T
FROM
    internal.event_message_context AS T
    INNER JOIN
        #DELETE_CANDIDATES AS DC
        ON DC.operation_id = T.operation_id;

DELETE T
FROM
    internal.event_messages AS T
    INNER JOIN
        #DELETE_CANDIDATES AS DC
        ON DC.operation_id = T.operation_id;

DELETE T
FROM
    internal.operation_messages AS T
    INNER JOIN
        #DELETE_CANDIDATES AS DC
        ON DC.operation_id = T.operation_id;

-- etc
-- Finally,remove the entry from operations

DELETE T
FROM
    internal.operations AS T
    INNER JOIN
        #DELETE_CANDIDATES AS DC
        ON DC.operation_id = T.operation_id;

通常需要注意
– 不要信任互联网上的随机代码
– 使用ssistalk和/或系统表中的图来标识所有依赖关系
– 您可能只需要将删除操作分割成较小的操作
– 您可以通过删除操作的RI来获益,但要确保使用检查选项重新启用它们,以便它们被信任.
– 如果操作持续时间超过4小时,请咨询您的dba

参考

> Catalog Indexing Recommendations
> Beware the SSIS Server Maintenance Job
> Slow performance when you run the SSIS Server Maintenance Job to remove old data in SQL Server 2012