归档过程运行不够快

Question

我正在将数据从一个数据库归档到另一个数据库，在不同的SQL server.We上归档我们的database.Recently中的多个表，我们对源数据库的插入增加了，归档速度不够快。我正在考虑将表的归档分割成单独的作业，但是我能做些什么来提高查询的性能呢？

这是估计的执行计划。

这是实际的执行计划。

实际计划中的QueryTimeStats如下

+-----------+---------+-------------+---------+
| Statement | CpuTime | ElapsedTime | Percent |
+-----------+---------+-------------+---------+
|         1 |       3 |           3 | 0.00%   |
|         2 |       3 |           4 | 0.00%   |
|         3 |       0 |           0 | 0.00%   |
|         4 |       1 |           1 | 0.00%   |
|         5 |       0 |           1 | 0.00%   |
|         6 |       1 |           1 | 0.00%   |
|         7 |       6 |           6 | 0.01%   |
|         8 |       0 |           1 | 0.00%   |
|         9 |     516 |         538 | 0.49%   |
|        10 |   76063 |       79110 | 71.84%  |
|        11 |     496 |       21621 | 19.63%  |
|        12 |      91 |         237 | 0.22%   |
|        13 |       4 |           4 | 0.00%   |
|        14 |       3 |           4 | 0.00%   |
|        15 |    2176 |        2446 | 2.22%   |
|        16 |    2581 |        5102 | 4.63%   |
|        17 |      92 |         293 | 0.27%   |
|        18 |      20 |          39 | 0.04%   |
|        19 |       2 |           2 | 0.00%   |
|        20 |       3 |         242 | 0.22%   |
|        21 |       0 |           0 | 0.00%   |
|        22 |       0 |           0 | 0.00%   |
|        23 |       2 |           2 | 0.00%   |
|        24 |       5 |           6 | 0.01%   |
|        25 |     139 |         139 | 0.13%   |
|        26 |       0 |           1 | 0.00%   |
|        27 |       4 |           3 | 0.00%   |
|        28 |       4 |           6 | 0.01%   |
|        29 |      77 |          77 | 0.07%   |
|        30 |       0 |           1 | 0.00%   |
|        31 |       9 |           8 | 0.01%   |
|        32 |       3 |           4 | 0.00%   |
|        33 |       0 |           0 | 0.00%   |
|        34 |       1 |           1 | 0.00%   |
|        35 |       4 |           4 | 0.00%   |
|        36 |       5 |           8 | 0.01%   |
|        37 |      81 |          82 | 0.07%   |
|        38 |       0 |           1 | 0.00%   |
|        39 |       3 |           3 | 0.00%   |
|        40 |       4 |           6 | 0.01%   |
|        41 |     105 |         105 | 0.10%   |
|        42 |       1 |           7 | 0.01%   |
+-----------+---------+-------------+---------+

Answer 1

从源端通过链接服务器执行插入会对性能造成问题。插入目标的每一行都是通过游标操作中的离散INSERT语句插入的。也就是说，如果要向目标中插入12,000行，Server实际上将对每一行执行12,000次插入。

如果您重写存档过程，使其从目标服务器运行，它将更快。

作为执行计划中的一个示例，请重写以下内容：

INSERT INTO @MessageEvent (ID, DateTime) 
SELECT TOP (1500) ID, TimeStamp 
FROM ConnectAPI.dbo.MessageEvent (NOLOCK) 
ORDER BY ID

并在目标服务器上运行它，如下所示：

INSERT INTO ConnectAPI.dbo.MessageEvent (ID, DateTime)
SELECT TOP(1500) ID, TimeStamp
FROM [SourceServer].ConnectAPI.dbo.MessageEvent
ORDER BY ID

注意，我忽略了NOLOCK查询提示。您可能需要确保理解读取未提交隔离下的部分读取和/或重复读取的含义，这是与NOLOCK提示一起使用的隔离级别。

我在SQLServerScience.com上写了一篇博客文章，用最小完整、可验证的示例代码展示了从源服务器到目标服务器的80万行简单插入在从源服务器执行时要花费7分钟以上，从目标服务器执行时只需要11秒。

Answer 2

我查看了您的脚本和执行计划，可以看到您正在跟踪删除以实现归档。我想与你分享我的经验档案，我们几乎每年或一年半，我们的数据库档案。早些时候，我们采取了和你们类似的方法，这对我们来说太久了，而且还要花更长的时间才能得到批准，过去经常会有臃肿的日志，每次繁重的删除操作之后都要备份这些日志--总体来说，这是一项艰巨的任务，过去很难获得批准。

随后，我们改变了我们的方法，并开始如下所示：

我们开发了几个基于表类型的过程，并说表名是Employee，我们将执行到2017年12月31日为止的存档--我们将在2017年12月31日后将雇员表中的所有可用记录插入到Employee_New，并且一旦该插入操作成功完成。将Employee表重命名为Employee_Arch_20171231，将Employee_New表重命名为Employee(只包含存档期间之后的记录)。在新雇员表上创建与旧员工(Employee_Arch_20171231)相似的所有索引，并从旧Employee表中删除索引。Insert操作比delete操作快得多，所需的资源和日志空间也比delete操作少得多。在此操作期间，您将需要停机时间，这将比先前的停机时间短得多.

一旦完成存档并成功地用业务验证了所有数据，Employee_Arch_20171231表的数据将与主存档表合并(用于遵从性目的)。这样，您的归档表就没有对任何对象的任何引用，并且您可以根据您的业务需求执行您喜欢的操作。

在上面的归档过程中需要注意的一件事是非常清楚和小心下面的场景：

1. 表间的PK-FK约束。
2. 表上的触发器(如果有一些基于insert/delete/update等的操作)。
3. 过滤指数

我希望上面能帮上忙。