sql - 在SQL Server 2012中，随着时间的推移，INSERT INTO的运行速度要慢得多

我们有一个很大的数据库WriteDB，它存储原始交易数据，并且我们使用此表进行快速写入。然后，使用sql脚本，我将WriteDB中的数据导入到相对相同的表中的ReadDB中，但是通过添加一些额外的值+关系来进行扩展。导入脚本是这样的:

TRUNCATE TABLE [ReadDB].[dbo].[Price]
GO
INSERT INTO [ReadDB].[dbo].[Price]
SELECT a.*, 0 as ValueUSD, 0 as ValueEUR
from [WriteDB].[dbo].[Price] a
JOIN [ReadDB].[dbo].[Companies] b ON a.QuoteId = b.QuoteID

因此，最初大约有1.3亿。该表中的行(〜50GB)。每天它们中的一些会增加，其中一些会发生变化，因此现在我们决定不要过度复杂化逻辑，而只是重新导入所有数据。由于某种原因，该脚本在几乎相同的数据量上工作时间越来越长的问题。第一次运行需要大约1小时，现在已经需要3小时

另外导入后的SQL Server效果不佳。导入后(或导入过程中)，如果我尝试运行其他查询，即使是最简单的查询，也经常会因超时错误而失败。

出现此类不良行为的原因是什么，以及如何解决此问题？

最佳答案

一种理论是，您的第一个50GB数据集已填满了用于缓存的可用内存。截断表后，您的缓存现在实际上为空。这种交替的行为使有效利用高速缓存变得困难，并导致大量高速缓存未命中/IO时间增加。

请考虑以下事件序列:

您将初始数据集加载到WriteDb中。在加载操作期间，将缓存WriteDb中的页面。内存争用很少，因为数据集只有一个副本且有足够的内存。

您最初是填充ReadDb的。填充ReadDb(WriteDb中的数据)所需的页面已被大量缓存。从磁盘读取的次数更少，您的IO时间可以专门用于为ReadDb写入插入的数据。 (这是您的首次快速运行。)

您将第二个数据集加载到WriteDb中。在加载操作期间，没有足够的内存来缓存ReadDb中的现有数据和写入WriteDb的新数据。这种内存争用导致更少的WriteDb缓存页面。

您截断了ReadDb。这会使您的大部分缓存(即已缓存的50GB ReadDb数据)无效。

然后，您尝试第二次读取ReadDb。在这里您几乎没有缓存WriteWb，因此您的IO时间在读取WriteDb(您的查询)页面和写入ReadDb(您的插入页面)之间分配。 (这是您的第二次慢速运行。)

您可以通过在第一次和第二次加载操作期间比较SQL Server缓存未命中率来测试此理论。

一些提高性能的方法可能是:

为ReadDb/WriteDb使用单独的磁盘阵列以提高并行IO性能。

增加可用缓存(服务器内存量)以适应ReadDb + WriteDb的总大小，并最大程度地减少缓存未命中。

通过使用MERGE语句(而不是一次转储/加载50GB的数据)，将每个加载操作对现有缓存页面的影响降至最低。