Linee guida per l'ottimizzazione dell'importazione bulk
In questo argomento sono disponibili linee guida per ottimizzare le prestazioni in diversi scenari di importazione bulk:
Importazione di dati da un unico client o flusso in una tabella vuota.
Importazione di dati da un unico client o flusso in una tabella parzialmente popolata, non vuota.
Nota
L'importazione di dati in una tabella non vuota è definita importazione bulk incrementale. In caso di importazione bulk incrementale è essenziale stabilire se rimuovere gli indici prima dell'operazione.
Importazione di dati in parallelo al blocco a livello di tabella da più client o flussi.
Copia di dati tra istanze di Microsoft SQL Server.
In questo argomento è inoltre incluso un riepilogo dei blocchi e delle registrazioni a livello di tabella durante le operazioni di importazione bulk.
Importazione di dati da un unico client o flusso in una tabella vuota
Quando si importano dati in una tabella vuota da un unico client o flusso, è consigliabile attenersi alla procedura riportata di seguito.
Specificare il qualificatore TABLOCK, disponibile come hint o opzione in tutti e tre i metodi di importazione bulk. Se si specifica TABLOCK, il blocco a livello di tabella verrà applicato per l'intera durata dell'operazione bulk, eliminando l'overhead causato dal blocco di singole righe. Per ulteriori informazioni, vedere Controllo della modalità di blocco per l'importazione bulk.
Ridurre al minimo la registrazione. Per ulteriori informazioni, vedere Prerequisiti per la registrazione minima nell'importazione bulk.
Gestire gli indici come illustrato di seguito.
Quando si utilizza bcp, BULK INSERT o INSERT ... SELECT * FROM OPENROWSET(BULK...), se la tabella è vuota e dispone di un indice cluster e i dati nel file di dati sono ordinati in modo corrispondente alle colonne chiave dell'indice cluster, eseguire le operazioni seguenti:
Eseguire l'importazione bulk dei dati con l'indice cluster già a disposizione.
Specificare l'hint ORDER, oltre all'hint TABLOCK. Per ulteriori informazioni, vedere Controllo dell'ordinamento durante l'importazione bulk di dati.
Nel caso di una tabella vuota, questa soluzione risulta significativamente più rapida rispetto alla creazione dell'indice cluster dopo l'importazione dei dati, poiché consente di eliminare il passaggio relativo all'ordinamento.
Nota
Se nella tabella non vuota sono disponibili indici, viene eseguita la registrazione completa delle importazioni bulk, anche se si utilizza il modello di recupero con registrazione minima delle operazioni bulk. Per decidere se rimuovere gli indici, valutare se i vantaggi derivanti dall'importazione bulk in una tabella priva di indici siano superiori al costo della rimozione e rigenerazione degli indici.
Se si esegue l'importazione bulk di dati in una tabella vuota con indici e si specificano le dimensioni del batch, la tabella risulterà non vuota dopo il primo batch. A partire dal secondo batch, verrà eseguita la registrazione completa dei dati. Nel caso delle tabelle vuote con indici, prendere in considerazione l'esecuzione dell'importazione bulk in un unico batch.
Nota
Quando non si specifica la dimensione del batch, per impostazione predefinita in Query Optimizer di SQL Server viene utilizzata una dimensione predefinita come dimensione del file di dati. Per ottimizzare le prestazioni, è possibile utilizzare il qualificatore ROWS_PER_BATCH o KILOBYTES_PER_BATCH come hint per Query Optimizer in merito al numero approssimativo di righe disponibili nel file di dati. Per ulteriori informazioni, vedere Gestione delle dimensioni dei batch di copia bulk.
In genere l'importazione bulk di dati in una tabella priva di indici risulta più veloce che una tabella con indici. Se quindi in una tabella vuota sono presenti indici, è consigliabile rimuoverli prima di importare dati nella tabella e rigenerarli successivamente. Se i dati non vengono ordinati in base a una colonna chiave cluster e se la tabella è vuota, rimuovere tutti gli indici, importare i dati e quindi creare nuovi indici.
Importazione di dati da un unico client o flusso in una tabella non vuota
L'importazione di dati in una tabella che contiene già dati, ovvero una tabella non vuota, viene definita importazione bulk incrementale. In caso di importazione bulk incrementale è essenziale stabilire se rimuovere gli indici prima dell'operazione. Sono disponibili due opzioni. È possibile mantenere gli indici o eliminarli e ricrearli in seguito.
Quando si importano dati da un unico client o flusso in una tabella non vuota, la decisione in merito al mantenimento degli indici dipende dalla quantità di nuovi dati importati rispetto alla quantità di dati esistenti nella tabella:
Se la quantità di dati importati è ridotta rispetto alla quantità dei dati esistenti, la rimozione e rigenerazione degli indici potrebbe risultare controproducente, poiché è probabile che il tempo necessario per la rigenerazione degli indici sia superiore rispetto al tempo risparmiato durante l'operazione bulk.
Se invece si esegue l'importazione di una quantità relativamente elevata di nuovi dati, la rimozione degli indici dalla tabella prima dell'esecuzione dell'operazione bulk può consentire di ottimizzare le prestazioni, senza comportare incrementi significativi nel tempo necessario per l'indicizzazione.
Nella tabella seguente viene indicata la quantità minima di nuovi dati che deve essere presente in una tabella per giustificare la rimozione degli indici. Tale quantità minima è proporzionata alla quantità totale dei dati nella tabella e dipende dai tipi e dalle combinazioni degli indici. Se i nuovi dati superano la percentuale suggerita per un determinato tipo di indice o di raggruppamento di indici, prendere in considerazione la rimozione degli indici prima di un'operazione bulk e la rigenerazione degli indici in un secondo momento. Questi numeri sono sensibili allo schema dei dati esistenti e dei dati da caricare. Di conseguenza, i numeri vengono forniti esclusivamente come linea guida generale.
Indici |
Quantità relativa di nuovi dati |
|---|---|
Solo indice cluster |
30% |
Indice cluster e un indice non cluster |
25% |
Indice cluster e due indici non cluster |
25% |
Un solo indice non cluster |
100% |
Due indici non cluster |
60% |
Importazione di dati in parallelo con il blocco a livello di tabella da più client o flussi
Se SQL Server è in esecuzione in un computer con più processori ed è possibile suddividere in file di dati distinti i dati di cui eseguire l'importazione bulk in una tabella, è possibile ottimizzare le prestazioni tramite l'importazione parallela di dati nella tabella da più client. Quando si esegue l'importazione bulk da più client in una tabella, è necessario che ogni client disponga del proprio file di dati di input.
Per l'importazione di dati in una tabella da più client tenere presente quanto segue:
È possibile che più flussi di importazione bulk si blocchino a vicenda.
Per evitare tale problema, in SQL Server è disponibile un blocco interno speciale, denominato blocco aggiornamenti bulk. Per ottenere un blocco aggiornamenti bulk, è necessario specificare l'opzione TABLOCK con ogni flusso di importazione bulk, senza bloccare altri flussi di importazione bulk. Questa soluzione consente di evitare conflitti di accesso alla tabella tra i client. Il blocco aggiornamenti bulk è tuttavia disponibile solo in una tabella priva di indice, vuota o non vuota. Se si specifica TABLOCK su una tabella con indici, l'importazione bulk parallela non è possibile. Per ulteriori informazioni, vedere Controllo della modalità di blocco per l'importazione bulk.
Se nella tabella sono presenti indici, è possibile avvalersi del blocco aggiornamenti bulk, rimuovendo tutti gli indici prima di eseguire l'importazione bulk dei dati. È quindi possibile eseguire l'importazione bulk dei dati in parallelo tramite TABLOCK e infine rigenerare l'indice o gli indici. Si noti inoltre che se nella tabella non vuota sono disponibili indici, viene eseguita la registrazione completa delle importazioni bulk, anche se si utilizza il modello di recupero con registrazione minima delle operazioni bulk. Per decidere se rimuovere gli indici, valutare se i vantaggi derivanti dall'importazione bulk in una tabella priva di indici siano superiori al costo della rimozione e rigenerazione degli indici.
Nota
Se si rimuovono indici secondari, valutare se rigenerarli in parallelo, creando ogni indice secondario da un client distinto.
Per evitare di rimuovere e rigenerare gli indici, è possibile eseguire un'importazione parallela, senza specificare l'hint TABLOCK. In tale caso, è tuttavia possibile che i diversi flussi di importazione bulk si blocchino a vicenda. Non sono inoltre disponibili le ottimizzazioni relative alla registrazione bulk. Per ridurre al minimo i blocchi, è possibile specificare una dimensione inferiore del batch e utilizzare l'hint ORDER per eliminare il passaggio relativo all'ordinamento durante l'operazione di importazione bulk.
I dati devono essere suddivisi in più file di input, uno per ogni client. Per un utilizzo efficiente della CPU, è consigliabile che le dimensioni dei file di dati siano analoghe.
Per ulteriori informazioni, vedere Importazione di dati in parallelo tramite il blocco a livello di tabella.
Blocchi e registrazioni a livello di tabella durante l'importazione bulk
Nella tabella seguente è disponibile un riepilogo delle modalità con cui vengono determinati i tipi di blocchi dallo schema della tabella durante un'operazione di importazione bulk. Nella tabella seguente viene inoltre indicato se la tabella è vuota, se l'opzione TABLOCK è impostata per l'operazione e quale tipo di registrazione si verifica se il database utilizza il modello di recupero con registrazione minima delle operazioni bulk.
Nota
Dopo il primo batch riuscito, la tabella non è più vuota.
Tabella di destinazione dell'importazione bulk |
La tabella è vuota? |
L'opzione TABLOCK è impostata? |
Blocchi |
Registrazione con i modelli di recupero con registrazione minima delle operazioni bulk e con registrazione minima |
|---|---|---|---|---|
Heap |
Sì |
Sì |
BU-Tab |
Registrazione bulk |
Heap |
Sì |
No |
IX-Tab |
Registrazione completa |
Heap |
No |
Sì |
BU-tab |
Registrazione bulk |
Heap |
No |
No |
IX-Tab |
Registrazione completa |
Heap con un indice non cluster |
Sì |
Sì |
SCH-M |
Registrazione bulk |
Heap con un indice non cluster |
Sì |
No |
IX-Tab |
Registrazione completa |
Heap con un indice non cluster |
No |
Sì |
SCH-M |
|
Heap con un indice non cluster |
No |
No |
IX-Tab |
Registrazione completa |
Indice cluster |
Sì |
Sì |
SCH-M |
Registrazione bulk |
Indice cluster |
Sì |
No |
IX-Tab |
Registrazione completa |
Indice cluster |
No |
Sì |
X-TAB |
Registrazione completa |
Indice cluster |
No |
No |
IX-Tab |
Registrazione completa |
Copia di dati tra istanze di SQL Server
Per eseguire la copia bulk di dati da un'istanza di SQL Server a un'altra, utilizzare bcp per esportare i dati della tabella in un file di dati. Scegliere quindi uno dei metodi di importazione bulk disponibili per importare i dati dal file alla tabella. Eseguire le operazioni di esportazione e importazione bulk utilizzando il formato nativo o Unicode nativo.
Nota
Per ulteriori informazioni su tali formati, vedere Utilizzo del formato nativo per l'importazione o l'esportazione di dati e Utilizzo del formato Unicode nativo per importare o esportare dati.
Se la tabella di origine dispone di un indice cluster o si desidera eseguire un'importazione bulk di dati in una tabella con indice cluster:
Eseguire l'esportazione bulk dei dati dalla tabella di origine utilizzando bcp con l'opzione query per un'istruzione SELECT e specificando una clausola ORDER BY appropriata per creare un file di dati ordinato. Per ulteriori informazioni, vedere Utilità bcp.
Quando si esegue l'importazione bulk dei dati in SQL Server. Utilizzare il qualificatore ORDER, supportato solo da bcp e BULK INSERT. Per ulteriori informazioni, vedere Controllo dell'ordinamento durante l'importazione bulk di dati.
Per ulteriori informazioni, vedere Copia di dati tra server.