Creazione di tabelle e di indici compressi
In SQL Server 2008 la compressione di riga e di pagina è supportata sia per le tabelle che per gli indici. È possibile configurare la compressione dei dati per gli 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 ciascuna partizione. Non è inoltre necessario che per le varie partizioni di un oggetto sia configurata la stessa impostazione di compressione.
L'impostazione di compressione di una tabella non viene applicata automaticamente agli indici non cluster. ma ogni indice deve essere impostato individualmente. La compressione non è disponibile per le tabelle di sistema. Tabelle e indici possono essere compressi al momento della creazione creati mediante le istruzioni CREATE TABLE e CREATE INDEX. Per modificare lo stato della compressione di una tabella, un indice o una partizione, utilizzare l'istruzione ALTER TABLE o ALTER INDEX.
[!NOTA]
Se i dati esistenti sono frammentati, potrebbe essere possibile ridurre le dimensioni dell'indice senza utilizzare la compressione ma ricostruendo l'indice stesso. Poiché durante la ricostruzione dell'indice verrà applicato il fattore di riempimento, le dimensioni dell'indice potrebbero aumentare. Per ulteriori informazioni, vedere Fattore di riempimento.
Considerazioni relative all'utilizzo della compressione di riga e di pagina
Quando si utilizza la compressione di riga e di pagina, tenere presente le considerazioni seguenti:
La compressione è disponibile solo in SQL Server 2008 Enterprise Edition e Developer Edition.
La compressione consente di archiviare più righe in una pagina, ma non di modificare le dimensioni massime delle righe di una tabella o di un indice.
Una tabella non può essere abilitata per la compressione quando la somma delle dimensioni massime delle righe e dell'overhead relativo alla compressione supera le dimensioni massime di 8.060 byte delle righe. Una tabella in cui sono presenti le colonne c1char(8000) e c2char(53), ad esempio, non può essere compressa a causa dell'overhead relativo alla compressione aggiuntivo. Se si utilizza il formato di archiviazione vardecimal, il controllo delle dimensioni delle righe viene eseguito al momento dell'abilitazione del formato stesso. Per la compressone 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 rientra nella pagina, ovvero se le dimensioni della riga sono minori di 8.060 byte, in SQL Server vengono impediti gli aggiornamenti che non sarebbe possibile includere nella riga non compressa.
Quando viene specificato un elenco di partizioni, il tipo di compressione può essere impostato su ROW, PAGE o NONE per ogni singola partizione. Se l'elenco di partizioni non è specificato, tutte le partizioni vengono impostate in base alla 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, verrà 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 verrà 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 utilizzando la sintassi INSERT INTO ... WITH (TABLOCK).
Una tabella viene ricostruita eseguendo l'istruzione ALTER TABLE ... REBUILD con l'opzione di compressione PAGE.
Le nuove pagine allocate in un heap nel corso di operazioni DML non utilizzeranno la compressione PAGE finché l'heap non viene ricostruito. Ricostruire l'heap rimuovendo e riapplicando la compressione oppure creando e rimuovendo un indice cluster.
Per modificare l'impostazione di compressione di un heap, è necessario ricostruire tutti gli indici non cluster della tabella in modo che dispongano di puntatori ai nuovi percorsi delle righe nell'heap.
È possibile abilitare o disabilitare l'impostazione di compressione ROW o PAGE in linea o non in linea. L'abilitazione della compressione in un heap è un'operazione a thread singolo se eseguita in linea.
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 ricostruzione 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 della compressione delle partizioni in una tabella partizionata, eseguire una query sulla colonna data_compression della vista del catalogo sys.partitions.
Durante la compressione di indici, alle pagine a livello foglia è possibile applicare sia la compressione di riga che quella di pagina, mentre alle pagine a livello non foglia non è possibile applicare 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 per cui era implementato il formato di archiviazione vardecimal in SQL Server 2005 manterranno questa impostazione anche in seguito all'aggiornamento. È possibile applicare la compressione di riga a una tabella che utilizza il formato di archiviazione vardecimal. Tuttavia, poiché la compressione di riga è un superset del formato di archiviazione vardecimal, non è necessario mantenere quest'ultimo. Quando si utilizza il formato di archiviazione vardecimal con la compressione di riga, per i valori decimali non si ottiene alcun miglioramento in termini di compressione. Sebbene sia possibile inoltre applicare la compressione di pagina a una tabella per cui è implementato il formato di archiviazione vardecimal, per le colonne che utilizzano tale formato probabilmente non è possibile ottenere compressione aggiuntiva.
[!NOTA]
In SQL Server 2008 è supportato il formato di archiviazione vardecimal. Tuttavia, poiché la compressione a livello di riga consente di ottenere gli stessi risultati, tale formato viene considerato obsoleto. Questa caratteristica verrà rimossa a partire da una delle prossime versioni di Microsoft SQL Server. Evitare di utilizzare questa funzionalità in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata.
Implementazione della compressione
Per un riepilogo delle informazioni sull'implementazione della compressione dei dati, vedere Implementazione della compressione di riga e Implementazione della compressione di pagina.
Stima del risparmio in caso di utilizzo della compressione
Per valutare il modo in cui la modifica dello stato di compressione influirà su una tabella o su un indice, utilizzare la stored procedure sp_estimate_data_compression_savings. La stored procedure sp_estimate_data_compression_savings è disponibile solo nelle edizioni di SQL Server che supportano la compressione dati.
Impatto della compressione su tabelle e indici partizionati
Quando si utilizza la compressione dei dati con tabelle e indici partizionati, tenere presente le considerazioni seguenti:
Suddivisione di un intervallo
Quando le partizioni vengono suddivise utilizzando l'istruzione ALTER PARTITION, entrambe le partizioni ereditano l'attributo di compressione dei dati della partizione originale.
Unione di un intervallo
Quando due partizioni vengono unite, la partizione risultante eredita l'attributo di compressione dei dati della partizione di destinazione.
Cambio di partizioni
Per cambiare una partizione, è necessario che la relativa proprietà di compressione dei dati corrisponda a quella della tabella.
Ricostruzione di una o di tutte le partizioni
Per modificare la compressione di una tabella o di un indice partizionato, è possibile utilizzare due sintassi diverse:
La sintassi seguente consente di ricostruire solo la partizione cui si fa riferimento:
ALTER TABLE <table_name> REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = <option>)La sintassi seguente consente di ricostruire l'intera tabella utilizzando l'impostazione di compressione esistente per qualsiasi partizione cui non si fa riferimento:
ALTER TABLE <table_name> REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(<range>), ... )
Per gli indici partizionati viene seguito lo stesso principio utilizzando l'istruzione ALTER INDEX.
Eliminazione di un indice cluster partizionato
Quando un indice cluster viene eliminato, le partizioni di heap corrispondenti mantengono l'impostazione di compressione dei dati a meno che lo schema di partizionamento non venga modificato. Se lo schema di partizionamento viene modificato, tutte le partizioni vengono ricostruite 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 utilizzando l'opzione ALTER TABLE ... REBUILD ... che specifica l'opzione di compressione.
L'eliminazione non in linea di un indice cluster è un'operazione di rapida esecuzione, poiché vengono rimossi solo i livelli superiori degli indici cluster. Quando un indice cluster viene eliminato in linea, in SQL Server è necessario ricostruire l'heap due volte, una volta per l'operazione descritta nel passaggio 1 e una volta per quella descritta nel passaggio 2.
Impatto della compressione sulla replica
Quando si utilizza la compressione dei dati con la replica, tenere presente le considerazioni seguenti:
Quando l'agente snapshot genera lo script dello schema iniziale, il nuovo schema utilizzerà le stesse impostazioni di compressione sia per la tabella che per i relativi indici. Non è possibile abilitare la compressione solo sulla tabella e non sull'indice.
Per la replica transazionale, l'opzione dello schema dell'articolo determina gli oggetti e le proprietà dipendenti da inserire nello script. Per ulteriori informazioni, vedere sp_addarticle.
Quando applica gli script, l'agente di distribuzione non verifica la presenza di Sottoscrittori legacy. Se la replica della compressione è selezionata, non sarà possibile creare la tabella in Sottoscrittori legacy. Nel caso di 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 che verranno inseriti nello script. Per ulteriori informazioni sul livello di compatibilità, vedere Utilizzo di più versioni di SQL Server in una topologia di replica.
Nel caso di una topologia mista, se non è necessario per supportare le nuove opzioni di compressione, il livello di compatibilità della pubblicazione deve essere impostato sulla versione del Sottoscrittore legacy. Se necessario, comprimere le tabelle nel Sottoscrittore dopo che sono state create.
Nella tabella seguente vengono illustrate le impostazioni di replica che controllano la compressione durante la replica stessa.
Operazione che l'utente intende eseguire |
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 né comprimere i dati nel Sottoscrittore. |
False |
False |
Non inserisce nello script né la partizione né le impostazioni di compressione. |
Comprimere la tabella nel Sottoscrittore se tutte le partizioni sono compresse nel server di pubblicazione, senza replicare lo schema di partizione. |
False |
Vero |
Controlla se tutte le partizioni sono abilitate per la compressione. Inserisce nello script la compressione a livello di tabella. |
Impatto della compressione su altri componenti di SQL Server
La compressione viene eseguita nel motore di archiviazione e i dati vengono presentati alla maggior parte degli altri componenti di SQL Server in uno stato non compresso, limitando gli effetti della compressione negli altri componenti in relazione agli aspetti 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 i dati vengono convertiti dal motore di archiviazione 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 mediante un'operazione bulk in un heap con compressione di pagina, al momento dell'inserimento dei dati l'operazione di importazione bulk tenterà di comprimerli mediante la compressione di pagina.
La compressione non influisce sulle operazioni di backup e ripristino.
La compressione non influisce sul log shipping.
La compressione dati è incompatibile con le colonne di tipo sparse. Pertanto, non è possibile comprimere le tabelle che contengono colonne di tipo sparse né aggiungere le 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.
In SQL Server Management Studio la compressione dati è supportata tramite la Compressione guidata dati.
Per avviare la Compressione guidata dati
- In Esplora oggetti fare clic con il pulsante destro del mouse su una tabella, un indice o una vista indicizzata, scegliere Archiviazione, quindi fare clic su Comprimi.
Monitoraggio della compressione
Per monitorare la compressione dell'intera istanza di SQL Server, utilizzare i contatori Page compression attempts/sec e Pages compressed/sec di SQL Server, Access Methods Object.
Per ottenere le statistiche relative alla compressione di pagina per partizioni singole, eseguire una query sulla funzione a gestione dinamica sys.dm_db_index_operational_stats.
Esempi
In alcuni degli esempi seguenti vengono utilizzate tabelle partizionate ed è necessario utilizzare un database con filegroup. Per creare un database con filegroup, eseguire l'istruzione seguente.
CREATE DATABASE TestDatabase
ON PRIMARY
( NAME = TestDatabase,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDB.mdf'),
FILEGROUP test1fg
( NAME = TestDBFile1,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile1.mdf'),
FILEGROUP test2fg
( NAME = TestDBFile2,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile2.ndf'),
FILEGROUP test3fg
( NAME = TestDBFile3,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile3.ndf'),
FILEGROUP test4fg
( NAME = TestDBFile4,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile4.ndf') ;
GO
Per passare al nuovo database:
USE TestDatabase
GO
A. Creazione di una tabella che utilizza la compressione di riga
Nell'esempio seguente viene creata una tabella e la compressione viene impostata su ROW.
CREATE TABLE T1
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = ROW);
GO
B. Creazione di una tabella che utilizza la compressione di pagina
Nell'esempio seguente viene creata una tabella e la compressione viene impostata su PAGE.
CREATE TABLE T2
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = PAGE);
GO
C. Impostazione dell'opzione DATA_COMPRESSION in una tabella partizionata
Nell'esempio seguente viene utilizzata la tabella TestDatabase creata utilizzando il codice fornito in precedenza in questa sezione. Nell'esempio vengono creati una funzione e uno schema di partizione, quindi viene creata una tabella partizionata e vengono specificate le opzioni di compressione per le partizioni della tabella. In questo esempio la partizione 1 viene configurata per l'impostazione di compressione ROW, mentre le partizioni rimanenti vengono configurate per l'impostazione di compressione PAGE.
Per creare una funzione di partizione:
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO
Per creare uno schema di partizione:
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg) ;
GO
Per creare una tabella partizionata con partizioni compresse:
CREATE TABLE PartitionTable1
(col1 int, col2 varchar(max))
ON myRangePS1 (col1)
WITH
(
DATA_COMPRESSION = ROW ON PARTITIONS (1),
DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4)
);
GO
D. Impostazione dell'opzione DATA_COMPRESSION in una tabella partizionata
Nell'esempio seguente viene utilizzato lo stesso database dell'esempio C e viene creata una tabella utilizzando la sintassi per partizioni non contigue.
CREATE TABLE PartitionTable2
(col1 int, col2 varchar(max))
ON myRangePS1 (col1)
WITH
(
DATA_COMPRESSION = ROW ON PARTITIONS (1,3),
DATA_COMPRESSION = NONE ON PARTITIONS (2,4)
);
GO
E. Modifica di una tabella per cambiare la compressione
Nell'esempio seguente viene modificata la compressione della tabella non partizionato creata nell'esempio A.
ALTER TABLE T1
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
F. Modifica della compressione di una partizione in una tabella partizionata
Nell'esempio seguente viene modificata la compressione della tabella partizionata creata nell'esempio C. La sintassi REBUILD PARTITION = 1 consente di ricostruire solo il numero di partizione 1.
ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = NONE) ;
GO
Se per la stessa operazione viene utilizzata la sintassi alternativa seguente, vengono ricostruite tutte le partizioni della tabella.
ALTER TABLE PartitionTable1
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO
G. Modifica della compressione di più partizioni in una tabella partizionata
La sintassi REBUILD PARTITION = ... consente di ricostruire solo una partizione. Per ricostruire più partizioni, è necessario eseguire più istruzioni oppure eseguire l'esempio seguente per ricostruire tutte le partizioni utilizzando le impostazioni di compressione correnti per le partizioni non specificate.
ALTER TABLE PartitionTable1
REBUILD PARTITION = ALL
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1),
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4)
) ;
GO
H. Modifica della compressione su un indice
Nell'esempio seguente viene utilizzata la tabella creata nell'esempio A e viene creato un indice nella colonna C2.
CREATE NONCLUSTERED INDEX IX_INDEX_1
ON T1 (C2)
WITH ( DATA_COMPRESSION = ROW ) ;
GO
Eseguire il codice seguente per impostare l'indice sulla compressione di pagina:
ALTER INDEX IX_INDEX_1
ON T1
REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;
GO
I. Modifica della compressione di una singola partizione in un indice partizionato
Nell'esempio seguente viene creato un indice in una tabella partizionata che utilizza la compressione di riga in tutte le partizioni dell'indice.
CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO
Per creare l'indice in modo che utilizzi impostazioni di compressione diverse per partizioni diverse, utilizzare la sintassi ON PARTITIONS. Nell'esempio seguente viene creato un indice in una tabella partizionata che utilizza la compressione di riga nella partizione 1 dell'indice e quella di pagina nelle partizioni dell'indice comprese tra 2 e 4.
CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = ROW ON PARTITIONS(1),
DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4 ) ) ;
GO
Nell'esempio seguente viene modificata la compressione dell'indice partizionato.
ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL
WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO
J. Modifica della compressione di più partizioni in un indice partizionato
La sintassi REBUILD PARTITION = ... consente di ricostruire solo una partizione. Per ricostruire più partizioni, è necessario eseguire più istruzioni oppure eseguire l'esempio seguente per ricostruire tutte le partizioni utilizzando le impostazioni di compressione correnti per le partizioni non specificate.
ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1),
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4)
) ;
GO