Поведение индекса SQL Server при выполнении массовой вставки

Вы можете увидеть, как индексы обновляются, изучая план запроса. Рассмотрим эту таблицу кучи только с некластеризованными индексами.

CREATE TABLE dbo.BulkInsertTest(
      Column1 int NOT NULL
    , Column2 int NOT NULL
    , Column3 int NOT NULL
    , Column4 int NOT NULL
    , Column5 int NOT NULL
    );
CREATE INDEX BulkInsertTest_Column1 ON dbo.BulkInsertTest(Column1);
CREATE INDEX BulkInsertTest_Column2 ON dbo.BulkInsertTest(Column2);
CREATE INDEX BulkInsertTest_Column3 ON dbo.BulkInsertTest(Column3);
CREATE INDEX BulkInsertTest_Column4 ON dbo.BulkInsertTest(Column4);
CREATE INDEX BulkInsertTest_Column5 ON dbo.BulkInsertTest(Column5);
GO

Ниже приведен план выполнения одиночного INSERT .

INSERT INTO dbo.BulkInsertTest(Column1, Column2, Column3, Column4, Column5) VALUES
     (1, 2, 3, 4, 5);

В плане выполнения показан только оператор таблицы Insert, поэтому новые некластеризованные строки индекса были вставлены по существу во время самой операции вставки таблицы. Большая партия однотонных инструкций INSERT даст этот же план для каждого оператора insert.

Я получаю аналогичный план с одним оператором INSERT с большим количеством строк, заданных с помощью конструктора строк, с той лишь разницей, что добавление оператора Constant Scan для испускания строк.

INSERT INTO dbo.BulkInsertTest(Column1, Column2, Column3, Column4, Column5) VALUES
     (1, 2, 3, 4, 5)
    ,(1, 2, 3, 4, 5)
    ,(1, 2, 3, 4, 5)
    ,...
    ,(1, 2, 3, 4, 5);

Вот план выполнения BULK INSERT T-SQL (с использованием пустого файла в качестве источника). С помощью BULK INSERT SQL Server добавила дополнительные операторы плана запросов для оптимизации вставки индексов. Строки были намотаны после вставки в таблицу, а затем строки из буфера отсортированы и вставлены в каждый индекс отдельно в качестве операции массовой вставки. Этот метод уменьшает накладные расходы для больших операций вставки. Вы также можете увидеть похожие планы для запросов INSERT...SELECT .

BULK INSERT dbo.BulkInsertTest
    FROM 'c:\Temp\BulkInsertTest.txt';

Я подтвердил, что SqlBulkCopy генерирует тот же план выполнения, что и SqlBulkCopy BULK INSERT путем сбора фактических планов с помощью трассировки расширенного события. Ниже приведен скрипт DDL и PowerShell, который я использовал.

Trace DDL:

CREATE EVENT SESSION [SqlBulkCopyTest] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'SqlBulkCopyTest') 
        AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'insert bulk%') 
        ))
ADD TARGET package0.event_file(SET filename=N'SqlBulkCopyTest');
GO

Скрипт PowerShell:

$connectionString = "Data Source=.;Initial Catalog=YourUserDatabase;Integrated Security=SSPI;Application Name=SqlBulkCopyTest"

$dt = New-Object System.Data.DataTable;
$null = $dt.Columns.Add("Column1", [System.Type]::GetType("System.Int32"))
$null = $dt.Columns.Add("Column2", [System.Type]::GetType("System.Int32"))
$null = $dt.Columns.Add("Column3", [System.Type]::GetType("System.Int32"))
$null = $dt.Columns.Add("Column4", [System.Type]::GetType("System.Int32"))
$null = $dt.Columns.Add("Column5", [System.Type]::GetType("System.Int32"))

$row = $dt.NewRow()
[void]$dt.Rows.Add($row)
$row["Column1"] = 1
$row["Column2"] = 2
$row["Column3"] = 3
$row["Column4"] = 4
$row["Column5"] = 5

$bcp = New-Object System.Data.SqlClient.SqlBulkCopy($connectionString)
$bcp.DestinationTableName = "dbo.BulkInsertTest"
$bcp.WriteToServer($dt)

РЕДАКТИРОВАТЬ

Благодарим Владимира Баранова за предоставление этой статьи в блоге Microsoft Data Platform MVP Пол Уайт , в котором подробно описывается стратегия обслуживания индексов SQL Server.

EDIT 2

Из вашего пересмотренного вопроса я вижу, что ваша фактическая ситуация - это таблица с кластеризованным индексом, а не кучей. Планы будут аналогичны приведенным выше примерам кучи, за исключением того, что данные будут вставляться с использованием оператора кластеризованной индексной вставки вместо таблицы.

Подсказка ORDER может быть указана во время операций массовой вставки в таблицу с кластерным индексом. Когда указанный порядок совпадает с указанным кластеризованным индексом, SQL Server может исключить оператор сортировки перед вложением кластерного индекса, поскольку он предполагает, что данные уже отсортированы по подсказке. К сожалению, SqlBulkCopy не поддерживает подсказку ORDER .