在不锁定表的情况下插入大量记录

伊甸有约

我试图将 1,500,000    条记录插入表中。插入过程中出现表锁定问题。所以我想出了以下批处理插入。
DECLARE @BatchSize INT = 50000WHILE 1 = 1  BEGIN      INSERT INTO [dbo].[Destination]                   (proj_details_sid,                  period_sid,                  sales,                  units)      SELECT TOP(@BatchSize) s.proj_details_sid,                            s.period_sid,                            s.sales,                            s.units      FROM   [dbo].[SOURCE] s      WHERE  NOT EXISTS (SELECT                          FROM   dbo.Destination d                         WHERE  d.proj_details_sid = s.proj_details_sid                                AND d.period_sid = s.period_sid)      IF @@ROWCOUNT 我在Destination表面有聚簇索引(proj_details_sid ,period_sid )。NOTEXISTS部分只是为了限制插入记录再次插入表
我做对了，这样可以避免吗？还有更好的方法。
  K" O+ }4 v7 F注意：    插入批处理和非批处理的时间或多或少相同
9 c6 ~/ e- b: c* {: c' [* ?3 C& |                                                               
    解决方案:                                                               
                                                                不可能升级锁SELECT句子的一部分是相关的。
插入大量行是自然的结果
5 o( Y% g- {; P* d4 _( a7 ~当使用ALTER TABLE SET LOCK_ESCALATION当选项未在表上升级禁用锁时，并且存在以下任何情况时，将触发锁升级：
单个Transact-SQL在单个未分区表或索引上至少获得5000个锁。
单个Transact-SQL语句在分区表的单个分区至少获得5000个锁，ALTER TABLE SET
LOCK_ESCALATION选项设置为AUTO。数据库引擎实例中的锁数超过内存或配置阈值。
如果锁因锁冲突无法升级，数据库引擎将定期触发锁升级，每次获得1、250个新锁。
你可以通过Profiler中跟踪锁升级事件，或简单尝试以下不同批处理大小的事件，轻松查看情况。对我来说，TOP(6228)显示了6250个锁，但是TOP（6229）随着锁升级的到来，它突然下降到1。确切的数字可能会有所不同（取决于数据库设置和当前可用的资源）。使用重复测试来显示锁升级的阈值。
CREATE TABLE [dbo].[Destination]  (     proj_details_sid INT,    period_sid       INT,    sales            INT,    units            INT  )BEGIN TRAN --So locks are held for us to count in the next statementINSERT INTO [dbo].[Destination]SELECT TOP  (6229) 1，                                     FROM   master..spt_values v       master..spt_values v2SELECT COUNT(*)FROM   sys.dm_tran_locksWHERE  request_session_id = @@SPID;COMMITDROP TABLE [dbo].[Destination]您要插入50,000行，因此几乎可以肯定，将尝试进行锁升级。
' }1 B6 O; O8 K1 Q如何解决文章的原因SQL Server锁升级引起的堵塞问题已经很老了，但很多建议还是有效的。
[ol]将大批处理操作分为几个较小的操作(即使用较小的批处理大小)
如果其他SPID目前持有不兼容的表锁，锁不会升级-他们给出的例子是正在执行的其他会话[/ol]BEGIN TRANSELECT * FROM mytable (UPDLOCK,HOLDLOCK) WHERE 1=0WAITFOR DELAY '1:00:00'COMMIT TRAN[ol]
使用跟踪标志1211禁止锁定升级-然而，这是一个全球性的设置，可能会导致严重的问题。有1224个新选项，问题很少，但它仍然是全球性的。[/ol]另一种选择是，ALTER TABLE blah SET (LOCK_ESCALATION =DISABLE)但这仍然不是很针对性，因为它会影响针对该表的所有查询，而不仅仅是这里的单个方案。
因此，我会选择选项1或可能的选项2，并折扣其他选项。