Comportamento dell'indice di SQL Server quando si esegue l'inserimento in blocco

bulkinsert indexing sqlbulkcopy sql-server

Domanda

Ho un'applicazione che inserisce più righe contemporaneamente in SQL Server.

Uso la classe SqlBulkCopy o il codice auto-scritto che genera un insert into table_name(...) values (...) gigantesco insert into table_name(...) values (...) istruzione insert into table_name(...) values (...) .

La mia tabella ha diversi indici e uno in cluster.

La domanda è: come sono aggiornati quegli indici? Per ogni riga inserisco? Per ogni transazione?

Una domanda un po 'strana: esiste un termine generale per questo scenario, come "comportamento di indicizzazione degli inserimenti di massa"? Ho provato a google diverse combinazioni di parole chiave, non ho trovato nulla. Il motivo per cui lo chiedo è perché a volte lavoro con Postgres e vorrei conoscere anche il suo comportamento.

Ho cercato di trovare un articolo su questo argomento, più volte, senza fortuna.

Se puoi indicarmi qualsiasi documento, articolo o libro con un capitolo pertinente, sarebbe fantastico

Risposta accettata

Puoi vedere come vengono aggiornati gli indici esaminando il piano di query. Considera questa tabella heap con solo indici non cluster.

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

Di seguito è riportato il piano di esecuzione per un singleton INSERT .

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

INSERIRE il piano di esecuzione

Il piano di esecuzione mostra solo l'operatore Inserimento tabella in modo che le nuove righe indice non cluster siano state inserite intrinsecamente durante l'operazione di inserimento tabella stessa. Un grande batch di istruzioni INSERT singleton produrrà lo stesso piano per ogni istruzione di inserimento.

Ricevo un piano simile con una singola istruzione INSERT con un numero elevato di righe specificate tramite un costruttore di righe, con l'unica differenza che è l'aggiunta di un operatore di scansione costante per emettere le righe.

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);

inserisci la descrizione dell'immagine qui

Ecco il piano di esecuzione per un'istruzione BULK INSERT T-SQL (utilizzando un file vuoto fittizio come origine). Con BULK INSERT , SQL Server ha aggiunto operatori di piani di query aggiuntivi per ottimizzare gli inserti di indice. Le righe sono state sottoposte a spooling dopo l'inserimento nella tabella e quindi le righe della spool sono state ordinate e inserite in ciascun indice separatamente come un'operazione di inserimento di massa. Questo metodo riduce il sovraccarico per le operazioni di inserimento di grandi dimensioni. Potresti anche vedere piani simili per le query INSERT...SELECT .

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

BULK INSERT programma di esecuzione

Ho verificato che SqlBulkCopy genera lo stesso piano di esecuzione di T-SQL BULK INSERT acquisendo i piani effettivi con una traccia di eventi estesa. Di seguito è riportato lo script DDL e PowerShell di traccia che ho utilizzato.

Traccia 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

Script di 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)

MODIFICARE

Ringraziamo Vladimir Baranov per aver fornito questo articolo del blog da Microsoft White Platform MVP Paul White , che descrive la strategia di manutenzione degli indici basata sui costi di SQL Server.

MODIFICA 2

Dalla tua domanda rivista vedo che la tua situazione attuale è una tabella con un indice cluster anziché un heap. I piani saranno simili a quelli degli esempi di heap sopra, ad eccezione, ovviamente, che i dati verranno inseriti utilizzando un operatore di inserimento indice cluster anziché un inserimento tabella.

Un suggerimento ORDER può essere specificato durante le operazioni di inserimento bulk in una tabella con un indice cluster. Quando l'ordine specificato corrisponde a quello dell'indice cluster, SQL Server può eliminare l'operatore di ordinamento prima dell'Indice di indice cluster poiché presuppone che i dati siano già ordinati per il suggerimento. Sfortunatamente, SqlBulkCopy non supporta il suggerimento ORDER .


Risposta popolare

La domanda è: come sono aggiornati quegli indici? Per ogni riga inserisco? Per ogni transazione?

Da un punto di vista di basso livello gli indici sono sempre aggiornati riga per riga, questa è una conseguenza della struttura dei dati interna degli indici. Gli indici di SQL Server sono alberi B +. Non esiste alcun algoritmo per aggiornare contemporaneamente più righe in un indice B + tree, è necessario aggiornarle una per una perché non si può sapere in anticipo dove andrà una riga prima di aggiornare o inserire le righe precedenti.

Tuttavia, da un punto di vista transazionale, gli indici vengono aggiornati tutti in una volta, poiché SQL Server implementa la semantica transazionale. Sul livello di isolamento predefinito READ COMMITTED, un'altra transazione non può vedere le righe (righe indice o tabella) inserite nell'operazione di inserimento in blocco fino a quando la transazione non viene confermata. Quindi sembra che le righe siano state inserite tutte in una volta.



Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
È legale questo KB? Sì, impara il perché
Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
È legale questo KB? Sì, impara il perché