Skip to content

Come si analizza la performance di un'istruzione BULK INSERT?

Abbiamo la migliore risposta che abbiamo trovato online. Vogliamo che tu lo trovi utile e se vuoi condividere qualsiasi dettaglio che possa aiutarci a crescere, fallo liberamente.

Soluzione:

Per quanto ne so, è possibile ottimizzare un'inserzione massiva in modo molto simile a come si ottimizza un'inserzione normale. In genere, il piano di query per un semplice inserimento non è molto informativo, quindi non preoccupatevi di non avere il piano. Verranno illustrati alcuni modi per ottimizzare un inserto, ma la maggior parte di essi probabilmente non si applica all'inserto specificato nella domanda. Tuttavia, potrebbero essere utili se in futuro si dovessero caricare quantità maggiori di dati.

1. Inserire i dati in ordine di chiave di clustering

SQL Server spesso ordina i dati prima di inserirli in una tabella con un indice clusterizzato. Per alcune tabelle e applicazioni è possibile migliorare le prestazioni ordinando i dati nel file flat e facendo sapere a SQL Server che i dati sono ordinati attraverso l'indice di clustering. ORDER di BULK INSERT:

ORDINE ( {colonna [ ASC | DESC ] } [ ,... n ] )

Specifica l'ordinamento dei dati nel file di dati. Le prestazioni dell'importazione massiva migliorano se i dati importati sono ordinati in base all'indice clustered della tabella, se presente.

Poiché si utilizza un indice IDENTITY come chiave clusterizzata, non è necessario preoccuparsi di questo.

2. Utilizzare TABLOCK se possibile

Se si garantisce che una sola sessione inserisca i dati nella tabella, è possibile specificare l'opzione TABLOCK per BULK INSERT. Questo può ridurre la contesa sui lock e, in alcuni scenari, può portare a una registrazione minima. Tuttavia, si sta inserendo in una tabella con un indice clusterizzato che contiene già dei dati, quindi non si otterrà una registrazione minima senza il flag di traccia 610, menzionato più avanti in questa risposta.

Se TABLOCK non è possibile, perché non si può modificare il codice, non tutte le speranze sono perse. Considerare l'uso di sp_table_option:

EXEC [sys].[sp_tableoption]
    @TableNamePattern = N'dbo.BulkLoadTable' ,
    @OptionName = 'table lock on bulk load' , 
    @OptionValue = 'ON'

Un'altra opzione è quella di abilitare il flag di tracciamento 715.

3. Utilizzare una dimensione di batch appropriata

A volte è possibile sintonizzare gli inserti modificando la dimensione del lotto.

ROWS_PER_BATCH = righe_per_lotto

Indica il numero approssimativo di righe di dati nel file di dati.

Per impostazione predefinita, tutti i dati del file di dati vengono inviati al server come una singola transazione e il numero di righe del batch è sconosciuto all'ottimizzatore di query. Se si specifica ROWS_PER_BATCH (con un valore > 0), il server utilizza questo valore per ottimizzare l'operazione di bulk-import. Il valore specificato per ROWS_PER_BATCH deve essere approssimativamente uguale al numero effettivo di righe. Per informazioni sulle prestazioni, vedere "Osservazioni", più avanti in questo argomento.

Ecco la citazione riportata più avanti nell'articolo:

Se il numero di pagine da eliminare in un singolo batch supera una soglia interna, potrebbe verificarsi una scansione completa del pool di buffer per identificare le pagine da eliminare quando il batch viene eseguito. Questa scansione completa può compromettere le prestazioni dell'importazione in blocco. Un caso probabile di superamento della soglia interna si verifica quando un pool di buffer di grandi dimensioni è combinato con un sottosistema di I/O lento. Per evitare l'overflow del buffer su macchine di grandi dimensioni, non utilizzare il suggerimento TABLOCK (che rimuove le ottimizzazioni di bulk) o utilizzare una dimensione di batch più piccola (che conserva le ottimizzazioni di bulk).

Poiché i computer variano, si consiglia di testare varie dimensioni di batch con il proprio carico di dati per scoprire quello che funziona meglio.

Personalmente, inserirei tutte le 695 righe in un singolo batch. La regolazione della dimensione del batch può fare una grande differenza quando si inseriscono molti dati.

4. Assicurarsi di aver bisogno del file IDENTITY colonna

Non so nulla del vostro modello di dati o dei vostri requisiti, ma non cadete nella trappola di aggiungere una colonna IDENTITY a ogni tabella. Aaron Bertrand ha un articolo su questo tema intitolato Cattive abitudini da eliminare: inserire una colonna IDENTITY in ogni tabella. Per essere chiari, non sto dicendo che si debba rimuovere la colonna IDENTITY da questa tabella. Tuttavia, se si determina che la colonna IDENTITY non è necessaria e la si rimuove, si potrebbero migliorare le prestazioni degli inserti.

5. Disabilitare indici o vincoli

Se si sta caricando una grande quantità di dati in una tabella rispetto a quelli già presenti, potrebbe essere più veloce disabilitare gli indici o i vincoli prima del caricamento e abilitarli dopo il caricamento. Per grandi quantità di dati, di solito è più inefficiente per SQL Server costruire un indice tutto in una volta, invece che mentre i dati vengono caricati nella tabella. Sembra che abbiate inserito 695 righe in una tabella con 11500 righe, quindi non vi consiglio questa tecnica.

6. Considerare TF 610

Il flag di tracciamento 610 consente una registrazione minima in alcuni scenari aggiuntivi. Per la tabella con un flag IDENTITY Se si utilizza una chiave cluster, la registrazione per le nuove pagine di dati sarà minima, a patto che il modello di recupero sia semplice o di tipo bulk-log. Credo che questa funzione non sia attiva per impostazione predefinita, perché potrebbe peggiorare le prestazioni su alcuni sistemi. Prima di abilitare questo flag di tracciamento, è necessario fare delle prove accurate. Il riferimento Microsoft consigliato sembra essere ancora The Data Loading Performance Guide (Guida alle prestazioni del caricamento dei dati)

Impatto I/O della registrazione minima con il flag di traccia 610

Quando si esegue il commit di una transazione di caricamento massivo che è stata registrata in modo minimo, tutte le pagine caricate devono essere scaricate su disco prima del completamento del commit. Tutte le pagine scaricate che non sono state catturate da una precedente operazione di checkpoint possono creare una grande quantità di I/O casuale. In contrasto con un'operazione completamente registrata, che crea invece I/O sequenziale sulle scritture di registro e non richiede che le pagine caricate vengano scaricate su disco al momento del commit.

Se lo scenario di carico è costituito da piccole operazioni di inserimento su btrees che non attraversano i confini dei checkpoint e si dispone di un sistema di I/O lento, l'uso di un log minimo può effettivamente rallentare la velocità di inserimento.

Per quanto ne so, questo non ha nulla a che fare con il flag di traccia 610, ma piuttosto con il logging minimo stesso. Credo che la precedente citazione su ROWS_PER_BATCH si riferisse allo stesso concetto.

In conclusione, probabilmente non c'è molto che si possa fare per mettere a punto il sistema BULK INSERT. Non mi preoccuperei del numero di letture osservato con l'inserimento. SQL Server segnala le letture ogni volta che si inseriscono dati. Considerate il seguente esempio molto semplice INSERT:

DROP TABLE IF EXISTS X_TABLE;

CREATE TABLE X_TABLE (
VAL VARCHAR(1000) NOT NULL
);

SET STATISTICS IO, TIME ON;

INSERT INTO X_TABLE WITH (TABLOCK)
SELECT REPLICATE('Z', 1000)
FROM dbo.GetNums(10000); -- generate 10000 rows

Uscita da SET STATISTICS IO, TIME ON:

Tabella 'X_TABLE'. Numero di scansioni 0, letture logiche 11428

Le letture segnalate sono 11428, ma non si tratta di informazioni utili. A volte il numero di letture segnalate può essere ridotto con una registrazione minima, ma ovviamente la differenza non può essere tradotta direttamente in un guadagno di prestazioni.

Inizierò a rispondere a questa domanda, con l'intenzione di aggiornarla continuamente man mano che costruisco una base di conoscenze sui trucchi. Spero che altri si imbattano in questa domanda e mi aiutino a migliorare le mie conoscenze nel processo.

  1. Verifica di base: Il vostro firewall esegue la stateful, deep packet inspection? Non troverete molto su Internet al riguardo, ma se gli inserimenti massivi sono circa 10 volte più lenti di quanto dovrebbero essere, è probabile che abbiate un dispositivo di sicurezza che esegue l'ispezione profonda dei pacchetti di livello 3-7 e controlla la "Generic SQL Injection Prevention".

  2. Misurate la dimensione dei dati che intendete inserire in blocco, in byte, per ogni lotto. Verificate inoltre se state memorizzando dati LOB, poiché si tratta di un'operazione di acquisizione e scrittura di pagine separate.

    Ci sono diversi motivi per cui si dovrebbe procedere in questo modo:

    a. In AWS, gli IOPS di Elastic Block Storage vengono suddivisi in byte, non in righe.

    1. Vedere Amazon EBS Volume Performance on Linux Instances " I/O Characteristics and Monitoring per una spiegazione di cosa sia un'unità EBS IOPS.
    2. In particolare, i volumi General Purpose SSD (gp2) hanno il concetto di "Crediti I/O e prestazioni burst" ed è comune che l'elaborazione ETL pesante esaurisca i crediti del saldo burst. La durata del burst si misura in byte, non in righe di SQL Server 🙂

    b. Sebbene la maggior parte delle librerie o dei whitepaper effettuino test basati sul numero di righe, in realtà l'importante è il numero di pagine su cui è possibile scrivere e, per calcolarlo, è necessario conoscere il numero di byte per riga e la dimensione della pagina (di solito 8KB, ma controllate sempre due volte se avete ereditato il sistema da qualcun altro).

    SELECT *
    FROM 
    sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'YourTable'), NULL, NULL, 'DETAILED')
    

    Prestare attenzione a avg_record_size_in_bytes e page_count.

    c. Come spiega Paul White in https://sqlperformance.com/2019/05/sql-performance/minimal-logging-insert-select-heap, "Per abilitare il logging minimo con INSERT...SELECTSQL Server deve aspettarsi più di 250 righe con una dimensione totale di almeno un extent (8 pagine)".

  3. Se si dispone di indici con vincoli di controllo o vincoli univoci, usare SET STATISTICS IO ON e SET STATISTICS TIME ON (o SQL Server Profiler o SQL Server Extended Events) per acquisire informazioni come l'eventuale presenza di operazioni di lettura nell'inserimento massivo. Le operazioni di lettura sono dovute al motore di database di SQL Server che si assicura che i vincoli di integrità passino.

  4. Provate a creare un database di prova in cui il PRIMARY FILEGROUP è montato su un'unità RAM. Questo dovrebbe essere leggermente più veloce dell'unità SSD, ma eliminerà anche qualsiasi dubbio sul fatto che il controller RAID possa aggiungere costi aggiuntivi. Nel 2018 non dovrebbe, ma creando più linee di base differenziali come questa, si può avere un'idea generale di quanto overhead stia aggiungendo l'hardware.

  5. Mettete anche il file sorgente su un'unità RAM.

    Mettere il file sorgente su un'unità RAM esclude qualsiasi problema di contesa se si legge il file sorgente dalla stessa unità in cui si trova il FILEGROUP del server del database.

  6. Verificate che il disco rigido sia stato formattato con estensioni da 64 KB.

  7. Utilizzate UserBenchmark.com e fate un benchmark del vostro SSD. In questo modo:

    1. Aggiungere maggiori conoscenze agli altri appassionati di prestazioni su quali prestazioni aspettarsi da un dispositivo
    2. Aiuta a capire se le prestazioni dell'unità sono inferiori a quelle di altri dispositivi con le stesse caratteristiche.
    3. Aiuta a capire se le prestazioni della propria unità sono inferiori a quelle di altre unità della stessa categoria (SSD, HDD, ecc.)
  8. Se state chiamando "INSERT BULK" da C# tramite le estensioni di Entity Framework, assicuratevi di "scaldare" prima il JIT e di "buttare via" i primi risultati.

  9. Provate a creare dei contatori di prestazioni per il vostro programma. Con .NET, si può usare benchmark.NET e questo profila automaticamente una serie di metriche di base. Potete poi CONDIVIDERE i vostri tentativi di profilazione con la comunità open source e vedere se le persone che utilizzano hardware diverso riportano le stesse metriche (come nel mio precedente punto sull'uso di UserBenchmark.com per fare un confronto).

  10. Provare a usare le named pipes ed eseguirlo come localhost.

  11. Se state puntando a SQL Server e utilizzate .NET Core, considerate la possibilità di avviare un Linux con SQL Server Std Edition: costa meno di un dollaro all'ora anche per hardware seri. Il vantaggio principale di provare lo stesso codice con lo stesso hardware e con un sistema operativo diverso è quello di vedere se lo stack TCP/IP del kernel del sistema operativo causa problemi.

  12. Utilizzate le query diagnostiche di Glen Barry per SQL Server per misurare la latenza dell'unità che memorizza il FILEGROUP della vostra tabella di database.

    a. Assicuratevi di misurare prima e dopo il test. Il "prima del test" indica solo se le caratteristiche dell'IO sono orribili come linea di base.

    b. Per misurare "durante il test", è necessario utilizzare i contatori di prestazioni PerfMon.

    Perché? Perché la maggior parte dei server di database utilizza una sorta di storage collegato alla rete (NAS). Nel cloud, in AWS, Elastic Block Storage è proprio questo. Potreste essere vincolati dagli IOPS della vostra soluzione EBS volume/NAS.

  13. Utilizzate uno strumento per misurare le statistiche di attesa. Red Gate SQL Monitor, SolarWinds Database Performance Analyzer o anche SQL Server Diagnostic Queries di Glen Barry o la query Wait Statistics di Paul Randal.

    a. I tipi di attesa più comuni saranno probabilmente Memoria/CPU, WRITELOG, PAGEIOLATCH_EX e ASYNC_NETWORK_IO.

    b. È possibile che si verifichino altri tipi di attesa se si eseguono gruppi di disponibilità.

  14. Misurare gli effetti di più attese simultanee INSERT BULK con TABLOCK disabilitati (TABLOCK probabilmente forzerà la serializzazione dei comandi INSERT BULK). Il collo di bottiglia potrebbe essere l'attesa di un comando INSERT BULK per completaree; Si dovrebbe cercare di mettere in coda il maggior numero di questi compiti che il modello fisico dei dati del server di database può gestire.

  15. Considerare la possibilità di partizionare la tabella. Per fare un esempio particolare: se la tabella del database è di sola appendice, Andrew Novick suggerisce di creare una tabella "OGGI". FILEGROUP e di partizionarla in almeno due gruppi di file, TODAY e BEFORE_TODAY. In questo modo, se il file INSERT BULK sono solo i dati di oggi, si può filtrare su un campo CreatedOn per forzare tutti gli inserimenti a colpire un singolo file FILEGROUPriducendo così i blocchi quando si usa TABLOCK. Questa tecnica è descritta in modo più dettagliato in un Whitepaper di Microsoft: Strategie per tabelle e indici partizionati con SQL Server 2008

  16. Se si utilizzano indici columntore, disattivare TABLOCK e caricare i dati in 102.400 righe Dimensione batch. È quindi possibile caricare tutti i dati in parallelo direttamente nei gruppi di righe del columntore. Questo suggerimento (e il razionale documentato) proviene da Columnstore indexes - Data loading guidance di Microsoft:

    Il caricamento in blocco ha queste ottimizzazioni integrate delle prestazioni:

    Carichi paralleli: È possibile avere più caricamenti bulk simultanei (bcp o bulk insert) che caricano ciascuno un file di dati separato. A differenza dei caricamenti massivi di rowstore in SQL Server, non è necessario specificare TABLOCK perché ogni thread di importazione bulk caricherà i dati esclusivamente in un gruppo di righe separato (gruppo di righe compresso o delta) con blocco esclusivo. Utilizzando TABLOCK forzerà un blocco esclusivo sulla tabella e non sarà possibile importare i dati in parallelo.

    Registrazione minima: Un carico massivo utilizza una registrazione minima per i dati che vanno direttamente ai gruppi di righe compressi. Tutti i dati che vanno a un gruppo di righe delta sono completamente registrati. Questo include qualsiasi batch di dimensioni inferiori a 102.400 righe. Tuttavia, con il caricamento in blocco, l'obiettivo è che la maggior parte dei dati non vada ai gruppi di righe delta.

    Ottimizzazione del blocco: Quando si carica in un gruppo di righe compresso, viene acquisito il blocco X sul gruppo di righe. Tuttavia, quando si carica in blocco in un gruppo di righe delta, viene acquisito un blocco X sul gruppo di righe, ma SQL Server continua a bloccare i blocchi PAGE/EXTENT perché il blocco X del gruppo di righe non fa parte della gerarchia di chiusura.

  17. A partire da SQL Server 2016, non è più necessario abilitare il flag di tracciamento 610 per un logging minimo nella tabella indicizzata. Citando l'ingegnere Microsoft Parikshit Savjani (enfasi mia):

    Uno degli obiettivi della progettazione di SQL Server 2016 è stato quello di migliorare le prestazioni e la scalabilità del motore per renderlo più veloce senza bisogno di manopole o flag di tracciamento per i clienti. Come parte di questi miglioramenti, uno dei miglioramenti apportati al codice del motore di SQL Server è stata l'attivazione del contesto di carico massivo (denominato anche inserti veloci o contesto di carico veloce) e della registrazione minima per impostazione predefinita quando si eseguono operazioni di carico massivo su database con modello di recupero semplice o con registrazione massiva. Se non avete familiarità con la registrazione minima, vi consiglio di leggere questo post di Sunil Agrawal che spiega come funziona la registrazione minima in SQL Server. Affinché gli inserti di massa siano registrati in modo minimo, devono comunque soddisfare le condizioni preliminari che sono documentate qui.

    Come parte di questi miglioramenti in SQL Server 2016, non è più necessario abilitare il flag di tracciamento 610 per la registrazione minima in una tabella indicizzata. e si unisce ad alcuni altri flag di traccia (1118, 1117, 1236, 8048) per diventare parte della cronologia. In SQL Server 2016, quando l'operazione di bulk load causa l'allocazione di una nuova pagina, tutte le righe che riempiono sequenzialmente la nuova pagina vengono registrate in modo minimo se sono soddisfatti tutti gli altri prerequisiti per la registrazione minima discussi in precedenza. Le righe inserite in pagine esistenti (senza allocazione di una nuova pagina) per mantenere l'ordine dell'indice vengono comunque registrate completamente, così come le righe spostate in seguito a suddivisioni di pagine durante il caricamento. È inoltre importante che ALLOW_PAGE_LOCKS sia attivato per gli indici (che è attivo per impostazione predefinita) affinché l'operazione di registrazione minima funzioni, poiché i blocchi di pagina vengono acquisiti durante l'allocazione e quindi vengono registrate solo le allocazioni di pagina o di estensione.

  18. Se si utilizza SqlBulkCopy in C# o EntityFramework.Extensions (che utilizza SqlBulkCopy sotto il cofano), controllare la configurazione di compilazione. I test vengono eseguiti in modalità Release? L'architettura di destinazione è impostata su Qualsiasi CPU/x64/x86?

  19. Considerare l'uso di sp_who2 per vedere se la transazione INSERT BULK è SOSPESA. Potrebbe essere SOSPESA perché bloccata da un altro spid. Si consiglia di leggere Come ridurre al minimo il blocco di SQL Server. Si può anche usare sp_WhoIsActive di Adam Machanic, ma sp_who2 fornisce le informazioni di base necessarie.

  20. Potreste avere un cattivo I/O del disco. Se state facendo un inserimento massivo e l'utilizzo del disco non raggiunge il 100%, ma è bloccato intorno al 2%, probabilmente avete un firmware difettoso o un dispositivo di I/O difettoso. (È successo a un mio collega). Usare [SSD UserBenchmark] per fare un confronto con altri per le prestazioni dell'hardware, soprattutto se si può replicare la lentezza sulla propria macchina dev locale. (L'ho messo per ultimo nell'elenco perché la maggior parte delle aziende non consente agli sviluppatori di eseguire database sul proprio computer locale a causa del rischio di IP).

  21. Se la vostra tabella usa la compressione, potete provare a eseguire più sessioni e, in ogni sessione, iniziare usando una transazione esistente ed eseguirla prima del comando SqlBulkCopy:

    MODIFICA LA CONFIGURAZIONE DEL SERVER
    IMPOSTA AFFINITÀ DI PROCESSO CPU=AUTO;

  22. Per il caricamento continuo, un flusso di idee, delineato per la prima volta in un whitepaper di Microsoft, Partitioned Table and Index Strategies Using SQL Server 2008:

    Caricamento continuo

    In uno scenario OLTP, i nuovi dati possono arrivare continuamente. Se gli utenti interrogano anche la partizione più recente, l'inserimento continuo di dati può causare un blocco: Le query degli utenti possono bloccare gli inserimenti e, allo stesso modo, gli inserimenti possono bloccare le query degli utenti.

    La contesa sulla tabella o sulla partizione di caricamento può essere ridotta utilizzando l'isolamento degli snapshot, in particolare la funzione READ COMMITTED SNAPSHOT livello di isolamento. Sotto READ COMMITTED SNAPSHOT gli inserti in una tabella non causano attività nella tabella tempdb quindi l'archivio di versioni tempdb è minimo per gli inserimenti, ma nessun blocco condiviso sarà preso dalle query degli utenti sulla stessa partizione.

    In altri casi, quando i dati vengono inseriti in una tabella partizionata in modo continuo e a una velocità elevata, si può ancora essere in grado di mettere in scena i dati per brevi periodi di tempo nelle tabelle di staging e poi inserire i dati nella partizione più recente ripetutamente fino a quando la finestra per la partizione corrente passa e i dati vengono inseriti nella partizione successiva. Ad esempio, supponiamo di avere due tabelle di staging che ricevono ciascuna 30 secondi di dati, in modo alternato: una tabella per la prima metà di un minuto, la seconda tabella per la seconda metà di un minuto. Una stored procedure di insert determina in quale metà del minuto si trova l'insert corrente e quindi inserisce nella prima tabella di staging. Allo scadere dei 30 secondi, la procedura insert determina che deve inserire nella seconda tabella di staging. Un'altra stored procedure carica quindi i dati dalla prima tabella di staging nella partizione più recente della tabella e tronca la prima tabella di staging. Dopo altri 30 secondi, la stessa procedura memorizzata inserisce i dati della seconda procedura memorizzata e li inserisce nella partizione corrente, quindi tronca la seconda tabella di staging.

  23. Guida alle prestazioni del caricamento dei dati del team CAT di Microsoft

  24. Assicurarsi che le statistiche siano aggiornate. Se possibile, utilizzare FULLSCAN dopo ogni creazione di indici.

  25. SAN Performance Tuning with SQLIO e, se si utilizzano dischi meccanici, assicurarsi che le partizioni del disco siano allineate. Consultare le Best Practice di Microsoft per l'allineamento delle partizioni dei dischi.

  26. COLUMNSTOREINSERT/UPDATE prestazioni

È probabile che le letture siano dovute ai vincoli unique e FK controllati durante l'inserimento; si può ottenere un miglioramento della velocità se si possono disabilitare/togliere i vincoli durante l'inserimento e abilitarli/ricrearli in seguito. È necessario verificare se questo rende l'operazione complessivamente più lenta rispetto a mantenerli attivi. Inoltre, questa potrebbe non essere una buona idea se altri processi stanno scrivendo sulla stessa tabella in modo simultaneo. - Gareth Lyons

Secondo le Q & A le chiavi esterne diventano non attendibili dopo l'inserimento in blocco, i vincoli FK diventano non attendibili dopo una BULK INSERT senza CHECK_CONSTRAINTS (il mio caso, in quanto ho terminato con vincoli non attendibili). Non è chiaro, ma non avrebbe senso controllarli e renderli comunque non attendibili. Tuttavia, PK e UNIQUE saranno comunque controllati (vedere BULK INSERT (Transact-SQL)). - Alessio

Qui puoi vedere le recensioni e le valutazioni dei lettori

Se ti piace questo mondo, puoi lasciare una recensione su ciò che ti è piaciuto di questo scritto.



Utilizzate il nostro motore di ricerca

Ricerca
Generic filters

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.