高效插入具有聚簇索引的表

INSERT INTO TABL_NAME (TRACKING_NUMBER, COLB, COLC) 
SELECT TRACKING_NUMBER, COL_B, COL_C 
FROM STAGING_TABLE

#1 楼

正如其他答案已表明的那样，SQL Server可能会或可能不会明确确保在insert之前按聚集索引顺序对行进行排序。

这取决于计划中的聚集索引运算符设置了DMLRequestSort属性（这又取决于所插入的行的估计数）。 ORDER BY查询可最大程度地减少页面拆分，并从SELECT操作中产生碎片

示例：

use tempdb;

GO

CREATE TABLE T(N INT PRIMARY KEY,Filler char(2000))

CREATE TABLE T2(N INT PRIMARY KEY,Filler char(2000))

GO

DECLARE @T TABLE (U UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),N int)

INSERT INTO @T(N)
SELECT number 
FROM master..spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 499

/*Estimated row count wrong as inserting from table variable*/
INSERT INTO T(N)
SELECT T1.N*1000 + T2.N
FROM @T T1, @T T2

/*Same operation using explicit sort*/    
INSERT INTO T2(N)
SELECT T1.N*1000 + T2.N
FROM @T T1, @T T2
ORDER BY T1.N*1000 + T2.N


SELECT avg_fragmentation_in_percent,
       fragment_count,
       page_count,
       avg_page_space_used_in_percent,
       record_count
FROM   sys.dm_db_index_physical_stats(2, OBJECT_ID('T'), NULL, NULL, 'DETAILED')
;  


SELECT avg_fragmentation_in_percent,
       fragment_count,
       page_count,
       avg_page_space_used_in_percent,
       record_count
FROM   sys.dm_db_index_physical_stats(2, OBJECT_ID('T2'), NULL, NULL, 'DETAILED')
;  

显示INSERT被大量碎片化

avg_fragmentation_in_percent fragment_count       page_count           avg_page_space_used_in_percent record_count
---------------------------- -------------------- -------------------- ------------------------------ --------------------
99.3116118225536             92535                92535                67.1668272794663               250000
99.5                         200                  200                  74.2868173956017               92535
0                            1                    1                    32.0978502594514               200

但是由于T的碎片很小，因此相反，有时您可能想强制SQL Server低估行计算何时知道数据已经进行了预排序，并希望避免不必要的排序。一个值得注意的例子是，使用T2聚集索引键将大量行插入表中。在Denali之前的SQL Server版本中，SQL Server添加了不必要且可能昂贵的排序操作。可以通过

avg_fragmentation_in_percent fragment_count       page_count           avg_page_space_used_in_percent record_count
---------------------------- -------------------- -------------------- ------------------------------ --------------------
0.376                        262                  62500                99.456387447492                250000
2.1551724137931              232                  232                  43.2438349394613               62500
0                            1                    1                    37.2374598468001               232

避免这种情况，然后SQL Server将估计将插入100行，而不考虑newsequentialid的大小，该大小低于添加排序的阈值计划。但是，正如下面的注释中所指出的那样，这的确意味着插入将无法利用最少的日志记录。

#2 楼

优化器决定在插入之前对数据进行排序会更有效，它将在插入运算符的上游进行。如果您在查询中引入排序，那么优化器应意识到数据已被排序，因此不再进行排序。请注意，根据从暂存表插入的行数，选择的执行计划可能因运行而异。

如果可以捕获带有或不带有显式排序的流程执行计划，请将它们附加到您的问题中以进行评论。

编辑：2011-10-28 17:00

@Gonsalu的答案似乎表明总是进行排序操作，而事实并非如此。演示脚本是必需的！

随着脚本变得越来越大，我已将它们移至Gist。为了便于实验，这些脚本使用SQLCMD模式。测试在2K5SP3（双核，8GB）上运行。

插入测试涵盖三种情况：


按与目标相同的顺序分段数据聚簇索引。 >以相反的顺序分段数据聚簇索引。
由col2聚集的分段数据聚类，其中包含一个随机INT。

第一次运行，插入25行。



所有三个执行计划都是相同的，计划中的任何地方都没有排序，并且聚集索引扫描为“ ordered = false”。

第二次运行，插入26行。 />


这次计划有所不同。


第一个显示聚簇索引扫描为ordered = false。由于对源数据进行了适当的排序，因此未发生排序。
第二次，聚簇索引向后扫描为ordered = true。因此，我们没有排序操作，但是优化程序会识别对数据进行排序的需求，并以相反的顺序进行扫描。
第三个显示了排序运算符。

因此，存在一个临界点，优化器认为这是必需的。如@MartinSmith所示，这似乎是基于要插入的估计行。在我的测试平台上，25不需要排序，26不需要排序（2K5SP3，双核，8GB）。

SQLCMD脚本包含一些变量，这些变量允许更改表中的行的大小（更改页面密度）以及其他插入之前dbo.MyTable中的行数。根据我的测试，两者都不会对引爆点产生任何影响。

如果有任何读者倾向，请运行脚本并将引爆点添加为注释。有兴趣了解它是否在测试平台和/或版本之间有所不同。

编辑：2011-10-28 20:15

在同一平台上重复测试，但使用2K8R2。这次引爆点是251行。同样，更改页面密度和现有行数也无效。

#3 楼

ORDER BY语句中的SELECT子句是多余的。创建测试用例。

CREATE TABLE #Test (
    id INTEGER NOT NULL
);

CREATE UNIQUE CLUSTERED INDEX CL_Test_ID ON #Test (id);

CREATE TABLE #Sequence (
    number INTEGER NOT NULL
);

INSERT INTO #Sequence
SELECT number FROM master..spt_values WHERE name IS NULL;

让我们启用实际查询计划的文本显示，因此我们可以查看查询处理器执行的任务。

SET STATISTICS PROFILE ON;
GO

现在，让我们将不带INSERT子句的ORDER BY 2K行插入表中。

该查询如下。

INSERT INTO #Test
SELECT number
  FROM #Sequence

如您所见，在实际的INSERT发生之前有一个Sort运算符。

现在，让我们清除表，并使用INSERT子句将ORDER BY插入表中2k行。

INSERT INTO #Test  SELECT number    FROM #Sequence
  |--Clustered Index Insert(OBJECT:([tempdb].[dbo].[#Test]), SET:([tempdb].[dbo].[#Test].[id] = [tempdb].[dbo].[#Sequence].[number]))
       |--Top(ROWCOUNT est 0)
            |--Sort(ORDER BY:([tempdb].[dbo].[#Sequence].[number] ASC))
                 |--Table Scan(OBJECT:([tempdb].[dbo].[#Sequence]))

此查询的实际执行计划如下。 >

TRUNCATE TABLE #Test;
GO

INSERT INTO #Test
SELECT number
  FROM #Sequence
 ORDER BY number

请注意，它与INSERT语句所使用的执行计划相同现在，并不总是需要执行ORDER BY操作，如Mark Smith在另一个答案中所示（如果要插入的行数很低），但是Sort子句仍然存在在这种情况下是冗余的，因为即使使用显式的ORDER BY，查询处理器也不会生成任何ORDER BY操作。 Sort，但这超出了此问题的范围。

更新2011-11-02：正如Mark Smith所示，将INSERT放入具有聚簇索引的表中可能并不总是需要进行排序-但是，在这种情况下，INSERT子句也是多余的。