Compressione dei dati

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di Azure

SQL Server, database SQL di Azure e Istanza gestita di SQL di Azure supportano la compressione di righe e pagine per tabelle e indici rowstore e supportano la compressione columnstore e columnstore per tabelle columnstore e indici.

Per le tabelle e gli indici rowstore, utilizzare la funzionalità di compressione dei dati per ridurre le dimensioni del database. Oltre a risparmiare spazio, la compressione dei dati migliora le prestazioni dei carichi di lavoro di I/O a utilizzo elevato di memoria perché i dati vengono archiviati in un numero inferiore di pagine e le query devono leggere un numero inferiore di pagine dal disco. Sono tuttavia necessarie risorse della CPU aggiuntive nel server di database per comprimere e decomprimere i dati, mentre i dati vengono scambiati con l'applicazione. È possibile configurare la compressione di righe e pagine sugli oggetti di database seguenti:

  • Un'intera tabella archiviata come heap.
  • Un'intera tabella archiviata come indice cluster.
  • Un intero indice non cluster.
  • Un'intera vista indicizzata.
  • Per le tabelle e gli indici partizionati, è possibile configurare l'opzione di compressione per ogni partizione e le varie partizioni di un oggetto non devono avere la stessa impostazione di compressione.

Per le tabelle columnstore e gli indici, tutte le tabelle columnstore e gli indici usano sempre la compressione columnstore e questo non è configurabile dall'utente. Utilizzare la compressione dell'archivio columnstore per ridurre ulteriormente le dimensioni dei dati in situazioni in cui è possibile concedere altro tempo e altre risorse della CPU per archiviare e recuperare i dati. È possibile configurare la compressione dell'archivio columnstore sugli oggetti di database seguenti:

  • Un'intera tabella columnstore o un intero indice columnstore cluster. Poiché una tabella columnstore viene archiviata come indice columnstore cluster, entrambi gli approcci producono gli stessi risultati.
  • Un intero indice columnstore non cluster.
  • Per le tabelle columnstore partizionate e gli indici columnstore, è possibile configurare l'opzione di compressione di archiviazione per ogni partizione e le varie partizioni non devono avere la stessa impostazione di compressione di archiviazione.

Nota

È anche possibile comprimere i dati usando il formato di algoritmo GZIP. Si tratta di un passaggio aggiuntivo ed è particolarmente adatto per comprimere porzioni di dati durante l'archiviazione di vecchi dati per l'archiviazione a lungo termine. I dati compressi usando la COMPRESS funzione non possono essere indicizzati. Per altre informazioni, vedere COMPRESS (Transact-SQL).

Considerazioni sulla compressione di righe e pagine

Quando si utilizza la compressione di riga e di pagina, tenere presente le considerazioni seguenti:

  • I dettagli relativi alla compressione dei dati sono soggetti a modifiche senza preavviso nei Service Pack o nelle versioni successive.

  • La compressione è disponibile in database SQL di Azure

  • La compressione non è disponibile in ogni edizione di SQL Server. Per altre informazioni, vedere l’elenco delle versioni e delle funzionalità supportate al termine di questa sezione.

  • La compressione non è disponibile per le tabelle di sistema.

  • La compressione può consentire l'archiviazione di più righe in una pagina, ma non modifica le dimensioni massime delle righe di una tabella o di un indice.

  • Non è possibile abilitare una tabella per la compressione quando le dimensioni massime delle righe più il sovraccarico di compressione superano le dimensioni massime della riga di 8.060 byte. Ad esempio, una tabella con le colonne c1 CHAR(8000) e c2 CHAR(53) non può essere compressa a causa dell'overhead di compressione aggiuntivo. Quando si usa il formato di archiviazione vardecimal , il controllo delle dimensioni delle righe viene eseguito quando il formato è abilitato. Per la compressione di riga e di pagina, il controllo delle dimensioni delle righe viene eseguito quando l'oggetto viene compresso inizialmente e successivamente in occasione dell'inserimento o della modifica di ogni riga. Quando viene utilizzata la compressione, vengono applicate le due regole seguenti:

    • Un aggiornamento a un tipo a lunghezza fissa deve sempre avere esito positivo.
    • La disabilitazione della compressione dei dati deve sempre avere esito positivo. Anche se la riga compressa si adatta alla pagina, il che significa che è minore di 8.060 byte; SQL Server impedisce gli aggiornamenti che non rientrano nella riga quando non sono compressi.
  • I dati fuori riga non vengono compressi quando si abilita la compressione dei dati. Ad esempio, un record XML di dimensioni superiori a 8.060 byte usa pagine esterne alle righe, che non sono compresse.

  • Diversi tipi di dati non sono interessati dalla compressione dei dati. Per altri dettagli, vedere Come la compressione delle righe influisce sull'archiviazione.

  • Quando viene specificato un elenco di partizioni, il tipo di compressione può essere impostato su ROW, PAGEo NONE su singole partizioni. Se l'elenco delle partizioni non viene specificato, tutte le partizioni vengono impostate con la proprietà di compressione dei dati specificata nell'istruzione . Quando viene creato un indice o una tabella, la compressione dei dati viene impostata su NONE se non specificato diversamente. Quando una tabella viene modificata, viene mantenuta la compressione esistente se non specificato diversamente.

  • Se si specifica un elenco di partizioni o una partizione non compresa nell'intervallo, viene generato un errore.

  • Gli indici non cluster non ereditano la proprietà di compressione della tabella. Per comprimere gli indici, è necessario impostarne in modo esplicito la proprietà di compressione. Per impostazione predefinita, al momento della creazione dell'indice la compressione è impostata su NONE.

  • Quando un indice cluster viene creato in un heap, tale indice eredita lo stato di compressione dell'heap, a meno che non venga specificato uno stato di compressione alternativo.

  • Quando un heap è configurato per la compressione a livello di pagina, le pagine vengono compresse solo nelle modalità seguenti:

    • Viene eseguita l'importazione bulk dei dati con l'ottimizzazione delle operazioni bulk abilitata.
    • I dati vengono inseriti usando INSERT INTO ... WITH (TABLOCK) la sintassi e la tabella non ha un indice non cluster.
    • Una tabella viene ricompilata eseguendo l'istruzione ALTER TABLE ... REBUILD con l'opzione PAGE di compressione.
  • Le nuove pagine allocate in un heap come parte delle operazioni DML non usano PAGE la compressione finché l'heap non viene ricompilato. Ricompilare l'heap rimuovendo e riapplicando la compressione oppure creando e rimuovendo un indice cluster.

  • Per modificare l'impostazione di compressione di un heap, è necessario ricompilare tutti gli indici non cluster della tabella in modo che dispongano di puntatori ai nuovi percorsi delle righe nell'heap.

  • È possibile abilitare o disabilitare o disabilitare ROW la PAGE compressione online o offline. L'abilitazione della compressione in un heap è un'operazione a thread singolo se eseguita online.

  • I requisiti di spazio su disco per l'abilitazione o la disabilitazione della compressione di riga o di pagina sono gli stessi necessari per la creazione o la ricompilazione di un indice. Per i dati partizionati, è possibile ridurre lo spazio richiesto abilitando o disabilitando la compressione per una partizione alla volta.

  • Per determinare lo stato di compressione delle partizioni in una tabella partizionata, eseguire una query sulla data_compression colonna della vista del sys.partitions catalogo.

  • Quando si comprimono gli indici, le pagine a livello foglia possono essere compresse sia con la compressione di riga che di pagina. Le pagine non a livello foglia non ricevono la compressione di pagina.

  • A causa della dimensione, i tipi di dati per valori di grandi dimensioni vengono a volte archiviati separatamente rispetto ai dati delle righe normali in pagine specifiche. La compressione dei dati non è disponibile per i dati archiviati separatamente.

  • Le tabelle che hanno implementato il formato di archiviazione vardecimal in SQL Server 2005 (9.x), mantengono tale impostazione al momento dell'aggiornamento. È possibile applicare la compressione di riga a una tabella con il formato di archiviazione vardecimal . Tuttavia, poiché la compressione delle righe è un superset del formato di archiviazione vardecimal , non esiste alcun motivo per mantenere il formato di archiviazione vardecimal . I valori decimali non ottengono alcuna compressione aggiuntiva quando si combina il formato di archiviazione vardecimal con la compressione di riga. È possibile applicare la compressione di pagina a una tabella con il formato di archiviazione vardecimal . Tuttavia, le colonne del formato di archiviazione vardecimal probabilmente non ottengono una compressione aggiuntiva.

    Nota

    Tutte le versioni supportate di SQL Server supportano il formato di archiviazione vardecimal . Tuttavia, poiché la compressione dei dati raggiunge gli stessi obiettivi, il formato di archiviazione vardecimal è deprecato. Questa funzionalità verrà rimossa nelle versioni future di SQL Server. Evitare di usare questa funzionalità in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata.

Per un elenco delle funzionalità supportate dalle edizioni di SQL Server, vedere:

Compressione dell'archivio columnstore e columnstore

Gli indici e le tabelle columnstore vengono sempre archiviati con la compressione columnstore. È possibile ridurre ulteriormente le dimensioni dei dati columnstore configurando una compressione aggiuntiva denominata compressione dell'archivio. Per eseguire la compressione dell'archiviazione, SQL Server esegue l'algoritmo di compressione Microsoft XPRESS sui dati. Aggiungere o rimuovere la compressione dell'archivio utilizzando i tipi di compressione dati seguenti:

  • Utilizzare la compressione dei dati COLUMNSTORE_ARCHIVE per comprimere i dati columnstore con la compressione dell'archivio.
  • Usare COLUMNSTORE la compressione dei dati per decomprimere la compressione dell'archiviazione. I dati risultanti verranno compressi con la compressione columnstore.

Per aggiungere la compressione dell'archivio, usare ALTER TABLE (Transact-SQL) o ALTER INDEX (Transact-SQL) con l'opzione REBUILD e DATA COMPRESSION = COLUMNSTORE_ARCHIVE.

Ad esempio:

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = 1 WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (2, 4)
);

Per rimuovere la compressione dell'archivio e ripristinare i dati nella compressione columnstore, usare ALTER TABLE (Transact-SQL) o ALTER INDEX (Transact-SQL) con l'opzione REBUILD e DATA COMPRESSION = COLUMNSTORE.

Ad esempio:

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = 1 WITH (
     DATA_COMPRESSION = COLUMNSTORE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (2, 4)
);

Nell'esempio seguente la compressione dei dati viene impostata su columnstore in alcune partizioni e su archivio columnstore in altre.

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE
        ON PARTITIONS (4, 5),
    DATA COMPRESSION = COLUMNSTORE_ARCHIVE
        ON PARTITIONS (1, 2, 3)
);

Prestazioni

Quando si comprimono gli indici columnstore con la compressione dell'archiviazione, l'indice viene eseguito più lentamente rispetto agli indici columnstore che non dispongono della compressione di archiviazione. Utilizzare la compressione dell'archivio solo quando è possibile concedere altro tempo e altre risorse della CPU per comprimere e recuperare i dati.

Il vantaggio della compressione dell'archiviazione è una riduzione dello spazio di archiviazione, utile per i dati a cui non si accede di frequente. Se ad esempio si dispone di una partizione per ogni mese di dati e la maggior parte dell'attività è relativa ai mesi più recenti, è possibile archiviare i mesi precedenti per ridurre i requisiti di archiviazione.

Metadati UFX

Nelle viste di sistema seguenti sono contenute informazioni sulla compressione dei dati per gli indici cluster:

La procedura sp_estimate_data_compression_savings (Transact-SQL) può essere applicata anche agli indici columnstore.

Impatto su tabelle e indici partizionati

Quando si utilizza la compressione dei dati con tabelle e indici partizionati, tenere presente le considerazioni seguenti:

  • Quando le partizioni vengono suddivise usando l'istruzione ALTER PARTITION, entrambe le partizioni ereditano l'attributo di compressione dei dati della partizione originale.

  • Quando due partizioni vengono unite, la partizione risultante eredita l'attributo di compressione dei dati della partizione di destinazione.

  • Per cambiare una partizione, è necessario che la relativa proprietà di compressione dei dati corrisponda a quella della tabella.

  • Per modificare la compressione di una tabella o di un indice partizionato, è possibile utilizzare due sintassi diverse:

    • La sintassi seguente consente di ricompilare solo la partizione cui si fa riferimento:

      ALTER TABLE <table_name>
      REBUILD PARTITION = 1 WITH (
          DATA_COMPRESSION = <option>
      );
      
    • La sintassi seguente ricompila l'intera tabella usando l'impostazione di compressione esistente per tutte le partizioni a cui non viene fatto riferimento:

      ALTER TABLE <table_name>
      REBUILD PARTITION = ALL WITH (
          DATA_COMPRESSION = PAGE ON PARTITIONS(<range>),
          ...
      );
      

    Per gli indici partizionati viene seguito lo stesso principio usando l'istruzione ALTER INDEX.

  • Quando un indice cluster viene eliminato, le partizioni di heap corrispondenti mantengono l'impostazione di compressione dei dati, a meno che lo schema di partizione non venga modificato. Se lo schema di partizionamento viene modificato, tutte le partizioni vengono ricompilate in un stato non compresso. Per eliminare un indice cluster e modificare lo schema di partizionamento, è necessario effettuare le operazioni descritte nei passaggi seguenti:

    1. Eliminare l'indice cluster.
    2. Modificare la tabella usando l'opzione ALTER TABLE ... REBUILD che specifica l'opzione di compressione.

    Per eliminare un indice OFFLINE cluster è un'operazione rapida, perché vengono rimossi solo i livelli superiori degli indici cluster. Quando un indice cluster viene eliminato ONLINE, SQL Server deve ricompilare l'heap due volte, una volta per il passaggio 1 e una volta per il passaggio 2.

Impatto della compressione sulla replica

Quando si usa la compressione dei dati con la replica, tenere presenti le considerazioni seguenti:

  • Quando l'agente snapshot genera lo script dello schema iniziale, il nuovo schema usa le stesse impostazioni di compressione sia per la tabella che per i relativi indici. La compressione non può essere abilitata solo nella tabella e non nell'indice.

  • Per la replica transazionale, l'opzione dello schema dell'articolo determina quali oggetti e proprietà dipendenti devono essere creati tramite script. Per altre informazioni, vedere sp_addarticle.

    Il agente di distribuzione non verifica la presenza di Sottoscrittori di livello inferiore quando applica gli script. Se la replica della compressione è selezionata, la creazione della tabella in Sottoscrittori legacy non riesce. Per una topologia mista, non abilitare la replica della compressione.

  • Per la replica di tipo merge, il livello di compatibilità della pubblicazione prevale sulle opzioni dello schema e determina gli oggetti dello schema per i quali viene generato uno script.

    Per una topologia mista, se non è necessario supportare le nuove opzioni di compressione, il livello di compatibilità della pubblicazione deve essere impostato sulla versione del Sottoscrittore di livello inferiore. Se necessario, comprimere le tabelle nel Sottoscrittore dopo la creazione.

Nella tabella seguente vengono illustrate le impostazioni di replica che controllano la compressione durante la replica stessa.

Intenzione dell'utente Replica dello schema di partizione per una tabella o un indice Replica delle impostazioni di compressione Comportamento a livello di script
Replicare lo schema di partizione e abilitare la compressione sulla partizione nel Sottoscrittore. Vero Vero Inserisce nello script lo schema di partizione e le impostazioni di compressione.
Replicare lo schema di partizione senza comprimere i dati nel Sottoscrittore. Vero False Inserimento nello script dello schema di partizione, ma non delle impostazioni di connessione per la partizione.
Non replicare lo schema di partizione e non comprimere i dati nel Sottoscrittore. Falso Falso Non crea script per le impostazioni di partizione o compressione.
Comprimere la tabella nel Sottoscrittore se tutte le partizioni sono compresse nel server di pubblicazione, senza replicare lo schema di partizione. Falso Vero Controlla se tutte le partizioni sono abilitate per la compressione.

Inserisce nello script la compressione a livello di tabella.

Effetto su altri componenti di SQL Server

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di Azure

La compressione si verifica nella motore di database e i dati vengono presentati alla maggior parte degli altri componenti di SQL Server in uno stato non compresso. Ciò limita gli effetti della compressione sugli altri componenti ai fattori seguenti:

  • Operazioni di importazione ed esportazione bulk
    • Quando i dati vengono esportati, anche in formato nativo, il relativo output è in formato di riga non compresso. In questo modo le dimensioni del file di dati esportato potrebbero risultare molto più elevate rispetto ai dati di origine.
    • Quando i dati vengono importati, se la tabella di destinazione è stata abilitata per la compressione, il motore di database converte i dati in formato di riga compresso. In questo modo l'utilizzo della CPU potrebbe aumentare rispetto a una situazione in cui i dati vengono importati in una tabella non compressa.
    • Quando i dati vengono importati in blocco in un heap con compressione di pagina, al momento dell'inserimento dei dati l'operazione di importazione in blocco tenta di comprimerli mediante la compressione di pagina.
  • La compressione non influisce sul backup e sul ripristino.
  • La compressione non influisce sul log shipping.
  • La compressione dei dati è incompatibile con le colonne di tipo sparse. Di conseguenza, le tabelle contenenti colonne di tipo sparse non possono essere compresse né possono essere aggiunte colonne di tipo sparse a una tabella compressa.
  • L'abilitazione della compressione può provocare la modifica dei piani di query, in quanto i dati vengono archiviati tramite un numero diverso di pagine e un numero diverso di righe per pagina.