快速 XML，慢速 XML

我们的数据库更新 Windows 应用程序需要在两个数据库之间传输一些数据，作为某个一次性更新过程的一部分。我选择 XML 作为媒介来移动数据。

该过程通过从源中选择一大块作为 XML 的行来工作，这些行通过应用程序传递到目标服务器，在那里它被分解成一个全局临时表。（源数据库和目标数据库可以位于 2 个不同的实例上。）重复该过程，直到所需的所有数据都在目标实例的临时表中。最后，将临时表记录合并到实际的目标数据库表中。

我们遇到的问题是，在某些情况下，第二个块非常慢，CPU 使用率非常高，而且它无处可去。我们能够在我们的托管环境中重现该问题，但不能在开发或 QA 中重现。我们的一些客户也遇到了这个问题——其中一个客户让它运行了一夜，最后在第二天早上运行了 18（！）小时后将其杀死。在那种情况下，我不确定它走了多远。等待约 2 小时后，我无法通过托管的第二块。

这是第一个块的语句批处理：

SET NOCOUNT ON;

DECLARE @src xml;
SET @src = CAST(@P1 AS xml);

SELECT
    n.x.value(N'field1[1]', 'uniqueidentifier') AS field1,
    n.x.value(N'field2[1]', 'smallint') AS field2,
    ... (8 more fields of various types) ...
    INTO [##target_2994] /*******/
    FROM @src.nodes('Rows[1]/Row') n(x);

这是第二个和后续块的批次，这就是问题所在：

SET NOCOUNT ON;

DECLARE @src xml;
SET @src = CAST(@P1 AS xml);

INSERT INTO [dbo].[##target_2994] /*******/
    SELECT
        n.x.value(N'field1[1]', 'uniqueidentifier') AS field1,
        n.x.value(N'field2[1]', 'smallint') AS field2,
        ... (8 more fields of various types) ...
        FROM @src.nodes('Rows[1]/Row') n(x);

这是我到目前为止所看到的：

• 这不是一个阻塞问题：等待统计信息是声明中的 99 SOS_SCHEDULER_YIELD% INSERT。
• sys.dm_io_virtual_file_stats在目标上tempdb显示它基本上是空闲的，所以这不是 I/O 问题。
• 所有数据都只有固定宽度的列，因此没有大量长文本字段。
• 数据块大小目前为 25,000 行，我们可能会降低这个值，但这并不能解释差异，因为我们已经用一些相同的数据集进行了测试。需要传输的最大表约为 725,000 行，测试结果很好。
• 查询计划在问题与没有问题之间是相同的*（我对 XML 进行了文件比较）。
• 问题与无问题之间的会话SET选项相同。
• 版本似乎不是一个因素：托管是 2008 R2 SP1 Enterprise x64；我们已经在 2005 SP4+ Standard x64 一直到 2008 R2 SP1+ Developer x86 进行了测试，没有出现任何问题。有问题的客户是 2008 RTM/SP1 Standard/Enterprise x64（到目前为止）。
• 虚拟化似乎不是一个因素：托管和 QA 是虚拟化的；dev 是部分虚拟化的；有问题的客户是身体上的。MAXDOP没有为我们的任何服务器设置（最大 = 4 个逻辑处理器）；我不确定客户的设置。
• 两个数据库在同一台服务器上与不同的服务器上没有区别。
• 在 TS 盒上运行更新应用程序与在本地运行没有区别。
• Tempdb 数据库设置是相同的。
• 实例和数据库排序规则是相同的。
• 将目标服务器上 tempdb 的兼容级别更改为 90 并没有帮助。（根据马克的回答）
• 没有显着的实例配置差异。（根据金博的回答）

任何人都可以建议其他的东西看吗？

_{* 计算表达式的名称不同，其中一个估计的行大小差异 < 1%，但其他一切都相同，包括总成本估计。}