Spazio inutilizzato eccessivo dopo l'esecuzione di operazioni di caricamento bulk

Sintomi

Quando si eseguono operazioni di caricamento bulk, è possibile osservare un aumento costante dello spazio inutilizzato per le tabelle nei database. Se si esegue il sp_spaceused comando, è possibile osservare lo spazio inutilizzato nella tabella occupa una grande percentuale dello spazio riservato (spazio complessivo allocato per la tabella).

EXEC sp_spaceused 'Sales.Customer'

Ecco un esempio.

Tabella	Riservato (KB)	Dati (KB)	Indice (KB)	Inutilizzato (KB)
Sales.Customer	800,000	50,000	10,000	740,000

Causa

Se si usa un'operazione di caricamento bulk con dimensioni batch ridotte, le tabelle potrebbero allocare gli extent di pagina usati appena.

L'esecuzione di operazioni di caricamento bulk che usano la registrazione minima può contribuire a migliorare le prestazioni delle operazioni di caricamento dei dati negli indici se i dati vengono preordinati o caricati in sequenza. Tuttavia, le dimensioni del batch (BATCHSIZE in BULK INSERT e -b l'opzione nell'utilità bcp) usate in queste operazioni svolgono un ruolo fondamentale per ottenere prestazioni più veloci e un utilizzo efficiente dello spazio su un altro. In modalità di registrazione minima, ogni batch di caricamento bulk ignora una ricerca per lo spazio disponibile quando alloca uno o più nuovi extent. SQL Server ignora questa ricerca nella cache per ottimizzare le prestazioni di inserimento. Crea direttamente nuovi extent invece di cercare spazio libero in quelli esistenti. Pertanto, se si usano dimensioni batch ridotte (ad esempio, 10 righe per batch), SQL Server riserva un nuovo extent di 64 KB per ogni batch di 10 record. Ciò è sprecato per la maggior parte delle dimensioni delle righe (alcune righe possono essere così ampie da adattarsi a una singola pagina in cui potrebbero essere appropriati 10 record). Le pagine rimanenti nell'extent sono inutilizzate ma riservate per l'oggetto . Pertanto, l'ottimizzazione rapida del carico combinata con una dimensione batch più piccola causa un utilizzo inefficiente dello spazio.

La tabella seguente del sito di blog msSQL Tiger Team mostra alcune prove empiriche per illustrare questo comportamento.

Dimensioni del batch	Riservato (KB)	Dati (KB)	Dimensioni indice (KB)	Inutilizzato (KB)	Percentuale (%) inutilizzata
10	6,472	808	8	5,656	87
100	1,352	168	8	1.176	86
1.000	264	128	8	128	49

Risoluzione

Per risolvere questo problema, prendere in considerazione le linee guida seguenti.

Numero ridotto di inserimenti significa che non sono presenti operazioni di caricamento bulk

Se si dispone di un numero relativamente ridotto di righe da inserire, questi non sono inserimenti in blocco. Nei casi di piccole dimensioni dei batch, è consigliabile usare normali istruzioni INSERT con registrazione completa, anziché operazioni di caricamento bulk con ottimizzazioni minime della registrazione.

Impostazione del valore delle dimensioni del batch per l'operazione di caricamento bulk

Per le operazioni di caricamento bulk, scegliere una dimensione batch multipla delle dimensioni di un extent (64 KB) e si basa sulle dimensioni medie delle righe. Tale valore di dimensione batch consente alle righe di riempire in modo efficiente lo spazio all'interno dell'extent. Ad esempio, se la dimensione media delle righe è di 25 byte, si dividerebbero 64 KB per 25 byte per riga per determinare il numero di righe che è possibile comprimere in un batch di dimensioni. In questo caso, 64 KB = 65.536 byte / 25 byte per riga = 2.620 righe. Pertanto, è possibile scegliere una dimensione batch intorno a questo numero, consentendo lo spazio per l'intestazione di ogni pagina di dati. È possibile testarlo usando dimensioni batch, ad esempio un intervallo compreso tra 2.500 e 2.700 e osservare l'utilizzo dello spazio. Per trovare le dimensioni medie delle righe nella tabella, usare la query seguente. Per gli heap (tabelle senza indice cluster), usare 0 per il parametro index_ID (terzo parametro). Per le tabelle con indici cluster, usare 1, come illustrato nell'esempio seguente.

SELECT 
  index_type_desc,alloc_unit_type_desc, 
  avg_record_size_in_bytes, 
  max_record_size_in_bytes, 
  avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2016'), OBJECT_ID(N'Production.ProductDocument'), 1, NULL , 'DETAILED')

Note

Prestare attenzione a non impostare le dimensioni del batch su un valore elevato perché questa operazione potrebbe causare picchi di richieste di I/O di grandi dimensioni. Per altre informazioni, vedere Effetto di I/O di dimensioni batch estremamente grandi.

Se la configurazione delle dimensioni batch non è un'opzione per l'operazione di caricamento bulk

Se, per qualche motivo, non è possibile modificare le dimensioni del batch o usare il normale INSERTS, è possibile disabilitare il comportamento di inserimenti rapidi (registrazione minima) usando il flag di traccia 692 (TF 692). A partire da SQL Server 2016 gli inserimenti rapidi sono abilitati per impostazione predefinita nel prodotto. Di conseguenza, ogni batch di caricamento bulk alloca nuovi extent e ignora la ricerca dello spazio disponibile nelle pagine esistenti per impostazione predefinita. Pertanto, le operazioni di caricamento bulk con dimensioni batch di piccole dimensioni possono causare un aumento dello spazio inutilizzato negli oggetti. Il flag di traccia 692 disabilita gli inserimenti rapidi durante il caricamento bulk di dati in un heap o in un indice cluster. In questo modo viene ridotto al minimo il problema di spazio inutilizzato descritto nella sezione Sintomi .

È possibile abilitare il flag di traccia mentre SQL Server è online usando la query seguente:

DBCC TRACEON(692,-1)

In alternativa, è possibile aggiungere -T692 come parametro di avvio del servizio SQL Server per abilitare AUTOMATICAMENTE TF 692 al riavvio del servizio SQL Server.

Effetto di I/O di dimensioni batch estremamente grandi

Nel modello di recupero con registrazione minima con registrazione minima delle operazioni bulk, SQL Server scarica le pagine di dati non appena esegue il commit del batch (noto anche come scritture eager). Ciò è dovuto al fatto che la registrazione minima indica che non vengono scritti singoli record di log nel log delle transazioni, vengono registrate solo le allocazioni di extent. Per assicurarsi che i dati non vengano persi in caso di interruzione del servizio, SQL Server scrive immediatamente le pagine di dati riempite con dati su disco. Pertanto, se si sceglie una dimensione batch di grandi dimensioni, questo può causare picchi di I/O di scrittura. Se il sottosistema di I/O non è in grado di gestire i burst di I/O di scrittura, ciò può influire negativamente sulle prestazioni dell'operazione di caricamento bulk e su tutte le altre transazioni in esecuzione nell'istanza di SQL Server in quel momento. In altre parole, c'è un punto di riduzione dei vantaggi relativi alle dimensioni del batch: se si sceglie una dimensione del batch troppo grande, il vantaggio diminuisce.

È quindi importante scegliere una dimensione batch multipla delle dimensioni di un extent (64 KB) in base alle dimensioni medie delle righe. A seconda delle prestazioni di I/O del disco sottostante, è possibile scegliere una dimensione batch ovunque tra le dimensioni di un extent (64 KB) e 64 extent (4 MB). Questo intervallo consente di bilanciare l'utilizzo efficiente dello spazio e le prestazioni ottimali del caricamento bulk.