Data e Backup Compression in SQL Server 2008

La compressione dei dati e quella dei backup sono due tra le più importanti novità di MS-SQL Server 2008. Il loro scopo è contenere, rispettivamente, la dimensione del database - più precisamente dei file di dati - e quella dei file di backup. Inoltre, la backup compression garantisce tempi di esecuzione migliori, mentre in particolari contesti la data compression migliora le performance delle letture/scritture.

Al momento entrambe le caratteristiche sono disponibili nell'Enterprise e nella Developer edition della versione 2008 e 2008 R2, e la Backup compression anche nella Standard edition della 2008 R2. Non è possibile ripristinare un database con oggetti compressi su un'istanza la cui edition non supporta la data compression, mentre è possibile il ripristino di un backup compresso su una qualsiasi edition di SQL Server - persino se si tratta di SQL Server Express Edition - purché sempre della stessa versione 2008 o 2008 R2.

Scopo di questo articolo è presentare le due tecnologie e definire le linee guida fondamentali per valutarne l'adozione.

Un avvertimento: per entrambe le tecnologie bisogna fare i conti con un maggiore utilizzo della CPU che è impegnata nelle attività di compressione/decompressione. Perciò, in caso di loro adozione è bene accertarsi di poter accomodare sufficiente CPU o mettere in conto una sua espansione.

Inside Data Compression

Oggetto della compressione dei dati sono le tabelle e gli indici di un database. In questo articolo, si parlerà anche più in dettaglio di Heap, indice Clustered, indici Non-Clustered e Partizioni (per la loro trattazione si rimanda ai precedenti articoli Indici B+Tree di SQL Server 2005 e Table Partitioning con SQL Server 2005). Brevemente ricordo che:

• per Heap s'intende una tabella senza indice Clustered e che una tabella può avere un solo indice Clustered e fino a 249 Non-Clustered.
• una pagina di dati rappresenta l'unità minima di allocazione di dati in SQL Server, la sua dimensione è di 8 Kb ed è organizzata internamente in righe e generalmente tutte le righe contenute in una pagina si riferiscono a un'unica tabella.
• a partire da MS-SQL Server 2005 è possibile partizionare le tabelle/indici ovvero suddividere logicamente e fisicamente le pagine della tabella/indice in funzione del valore assunto da una determinata colonna.

Esistono due tipi di compressione dei dati profondamente differenti tra loro: la Row compression e la Page compression. È possibile adottarne una per partizione su un singolo elemento di allocazione di tabella (dove per elemento intendiamo Heap, indice Clustered o Non-Clustered). Oggetto della compressione può essere un solo elemento (per es. uno specifico indice Non-Clustered), o tutti o solo alcuni elementi della stessa tabella (per es. la Heap e un indice Non-Clustered). Inoltre i due tipi di compressione possono coesistere nella stessa tabella, quindi si può avere una tabella con elementi non compressi, altri compressi in Row e altri ancora in Page. All'atto della compressione avviene una ricostruzione dell'indice/tabella e negli indici avviene la compressione delle sole pagine del livello foglia.

La compressione di tipo Row opera a livello di riga ottimizzando la registrazione dei dati definiti con data type a lunghezza fissa, il cui formato di registrazione viene convertito in lunghezza variabile dalla compressione. Quest'operazione comporta un costo di 4 bits per colonna convertita, che servono a registrare la lunghezza reale del dato presente in colonna. Una colonna che contiene il valore NULL o lo 0 (zero) occuperà solo 4 bits, questo indipendentemente dal tipo di dato a lunghezza fissa coinvolto.

La compressione di tipo Page opera a livello di pagina ottimizzando la registrazione delle righe contenute in una singola pagina minimizzando i valori di colonna ridondanti. Più in dettaglio, essa usa congiuntamente due compressioni:

• la Prefix compression che opera sulle ridondanze nei prefissi dei dati della singola colonna lungo tutte le righe della pagina;
• la Dictionary compression che opera sulle ridondanze del dato esatto lungo tutte le colonne e tutte le righe della pagina.

Il livello di compressione della Row compression è in genere sensibilmente più basso di quello che raggiunge la Page compression.

Cosa accade in fase di inserimento/modifica/cancellazione

In presenza di Row compression, le nuove righe inserite vengono compresse in modalità Row così come quelle modificate. In caso di Page compression, la nuova riga è compressa in modalità Page se e solo se:

• la pagina che ospita la nuova riga è già compressa
• in assenza di Indice Clustered, l'inserimento avviene mediante BULK INSERT (o INSERT INTO … SELECT) adottando l'opzione TABLOCK

In tutti gli altri casi la compressione è di tipo Row. È possibile, successivamente, portare la compressione a livello Page lanciando una REBUILD della tabella/indice Non-Clustered con l'opzione COMPRESSION = PAGE.

Si noti che, in presenza di Indice Clustered, viene applicata la Page compression solo al riempimento della pagina e che in caso di page-split questo viene preceduto da un tentativo di compressione. Di fatto, con la Page compression, non tutte le pagine di una tabella/indice sono compresse in modalità Page, bensì come abbiamo visto, in modalità Row. Si consideri anche che SQL Server decide il da farsi a sua discrezione sulla base di un soglia interna di risparmio dello spazio.

Per quanto riguarda gli aggiornamenti in caso di Page compression, non sempre questi determinano un ricalcolo della compressione di pagina. Come nel caso degli inserimenti, SQL Server ricalcola la compressione di pagina in funzione di una soglia interna .

Comprimere o non comprimere?

Adottare la compressione su un determinato oggetto implica la valutazione di diversi aspetti.

Prima di tutto è il caso di chiedersi se "l'impresa vale la spesa", in altre parole se il risparmio di spazio che produrrebbe la compressione è significativo. Per fare ciò è disponibile la stored procedure di sistema sp_estimate_data_compression_savings che è in grado di stimare la dimensione di una tabella/indice dopo l'applicazione della compressione Row o Page.

Per raggiungere il suo obiettivo, essa realizza una compressione di un campione di pagine nel database Tempdb. Perciò, per tabelle di grandi dimensioni è ragionevole fare questa valutazione in un ambiente di test perché potrebbe durare molto tempo influenzando così la normale attività del Tempdb di produzione. È bene sapere che i dati cosiddetti out of the row, ovvero i tipi di dato che eccedono la dimensione di una pagina (per es. varchar(max), image, text), e i dati FILESTREAM non sono comprimibili.

Una volta desunto un reale risparmio di spazio sul disco, occorre valutare l'impatto della compressione sul carico di lavoro della tabella/indice oggetto della compressione. Di fatto, SQL Server ha bisogno di decomprimere il dato in memoria prima di aggiornare il dato o prima di restituirlo in risposta a una query. Questa decompressione comporta un maggiore utilizzo di CPU. Di contro, poiché la compressione implica una riduzione del numero di pagine di cui si compone la tabella/indice, essa produce un riduzione del numero di letture/scritture di pagine in memoria (Logical I/O) e di disco (Physical I/O). Ciò significa anche un minor utilizzo di CPU coinvolta nell'operazione di I/O che, in taluni contesti, va a compensare l'aumento di CPU della compressione/decompressione con la possibilità di produrre addirittura tempi di risposta migliori. In particolare, potranno beneficiare della compressione le operazioni di scansione della tabella/indice, in quanto implicano un numero elevato di Physical/Logical I/O.

L'incremento di CPU che scaturisce dalla Row compression si stima generalmente intorno al 10%. La Page compression richiede in genere un impegno di CPU maggiore della Row compression. Quello che ci serve ora è un metodo per misurare l'impatto della compressione di una tabella/indice sulla CPU. Una soluzione può essere analizzare i seguenti due fattori relativamente alla singola tabella/indice che si vuole comprimere:

• Update: la percentuale di aggiornamenti rapportata al totale delle operazioni eseguite sull'oggetto
• Scan: la percentuale di scansioni rapportata al totale delle operazioni eseguite sull'oggetto

Un elevato tasso di Update e un basso tasso di Scan dovrebbero bastare a toglierci dalla testa di adottare un qualsiasi tipo di compressione. Un basso tasso di Update e un elevato tasso di Scan, invece, fanno di una tabella/indice un ottimo candidato alla compressione. Quanto più queste due tendenze sono accentuate (bassa % Update e alta % di Scan) e tanto più saggia sarà la decisione di adottare la Page compression. Lo stesso vale per le tabelle append-only, cioè quelle in cui si fanno solo insert (per es. le tipiche tabelle di log delle login/logout nel sistema), per le quali si ha un tasso di Update prossimo allo 0%.

Di seguito una query in grado di rilevare la percentuale di aggiornamento di tutte le heap/indici di un database:

SELECT
        Schema_Name = schema_name(o.schema_id),
        Table_Name = o.name,
        Index_Name = x.name,
        Partition = i.partition_number,
        Index_Type = x.type_desc,
        Index_ID = i.index_id,
        Percent_Update = i.leaf_update_count * 100.0 /
                (i.range_scan_count + i.leaf_insert_count
                        + i.leaf_delete_count + i.leaf_update_count
                        + i.leaf_page_merge_count + i.singleton_lookup_count )
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i
INNER JOIN sys.objects o ON o.object_id = i.object_id
INNER JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id
WHERE (i.range_scan_count + i.leaf_insert_count
       + i.leaf_delete_count + leaf_update_count
       + i.leaf_page_merge_count + i.singleton_lookup_count) <> 0
AND objectproperty(i.object_id,'IsUserTable') = 1
ORDER BY Percent_Update ASC

Questa query, invece, rileva la percentuale di scan sempre di tutte le heap/indici di un database:

SELECT
        Schema_Name = schema_name(o.schema_id),
        Table_Name = o.name,
        Index_Name = x.name,
        Partition = i.partition_number,
        Index_Type = x.type_desc,
        Index_ID = i.index_id,
        Percent_Scan = i.range_scan_count * 100.0 /
           (i.range_scan_count + i.leaf_insert_count
                + i.leaf_delete_count + i.leaf_update_count
                + i.leaf_page_merge_count + i.singleton_lookup_count )
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i
INNER JOIN sys.objects o ON o.object_id = i.object_id
INNER JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id
WHERE (i.range_scan_count + i.leaf_insert_count
       + i.leaf_delete_count + leaf_update_count
       + i.leaf_page_merge_count + i.singleton_lookup_count) <> 0
AND objectproperty(i.object_id,'IsUserTable') = 1
ORDER BY Percent_Scan DESC

Anche le operazioni di manutenzione degli indici risentono della presenza di compressione in termini di impiego di CPU: stime ufficiali parlano di un fattore di crescita della CPU durante la ricostruzione di un indice pari a 1.5 per la Row compresson e da 2 a 5 per la Page compression. In ogni caso, una linea guida è quella di adottare inizialmente la Row compression per avere modo di monitorare le performance del sistema senza correre il rischio di comprometterne la stabilità, quindi in presenza di feedback positivi passare al livello Page.

Infine, prima di passare a comprimere uno o più oggetti, assicuriamoci di avere spazio a sufficienza nei file di dati per compiere l'operazione. Questo perché l'oggetto in fase di compressione e quello originale coesistono fino al termine dell'operazione. Lo spazio necessario è deducibile sempre con la stored procedure di sistema sp_estimate_data_compression_savings .

La seconda parte di questo articolo verrà pubblicata martedì prossimo, 8 dicembre.

Data compression in pratica

Di seguito un esempio d'istruzione T-SQL per comprimere in modalità Row un indice Non-Clustered:

USE AdventureWorksLT2008
ALTER INDEX IX_Customer_EmailAddress
ON SalesLT.Customer
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)

In questo esempio viene compressa in modalità Page l'indice Clustered o in sua mancanza la Heap e, implicitamente, tutti i suoi indici Non-Clustered:

USE AdventureWorksLT2008
ALTER TABLE SalesLT.Customer
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

Per riportare l'oggetto in uno stato di non compressione, si usa la parola NONE:

ALTER INDEX IX_Customer_EmailAddress
ON SalesLT.Customer
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)

Mediante il Management Studio è disponibile un Data Compression Wizard che consente di valutare prima il risparmio di spazio che otterremmo con la compressione Row o Page, quindi di generare il codice T-SQL per applicarla. Il wizard si attiva con il tasto destro sull'oggetto che si vuole comprimere, quindi scegliendo la voce Storage > Manage Compression.

Di seguito un'interessante stored procedure in grado di comprimere:

• tutte le tabelle/indici di un database;
• tutti gli oggetti delle tabelle appartenenti a uno specifico schema di database;
• un singolo oggetto di tabella (indice o heap).

CREATE PROC dbo.stp_compress_tables (
	@compression_type VARCHAR(30) = 'PAGE', --(NONE, ROW, PAGE)
	@schema_name sysname = NULL,
	@table_name sysname = NULL,
	@index_name sysname = NULL
)
AS
DECLARE
	@sql_string NVARCHAR(4000),
	@edition VARCHAR(30),
	@version INT;
SET @table_name = COALESCE(QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name), NULL);
;WITH a AS (
	SELECT
		edition = CONVERT(VARCHAR, SERVERPROPERTY ('Edition')),
		VERSION = CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion'))
)
SELECT
	@edition = edition,
	@version = CONVERT(INT, LEFT(VERSION, CHARINDEX('.', VERSION)-1))
FROM a;
IF @edition LIKE 'Enterprise Edition%'
	OR @edition LIKE 'Developer Edition%'
	AND @version >= 10
BEGIN
	;WITH cte AS
	(
		SELECT table_name = QUOTENAME(SCHEMA_NAME(tbl.schema_id)) + '.' + QUOTENAME (tbl.name),
			index_name = idx.name
		FROM sys.tables AS tbl
		INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id
		INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS INT)
		AND p.index_id=idx.index_id
		WHERE @compression_type <> (CASE p.data_compression
																	WHEN 0 THEN 'none'
																	WHEN 1 THEN 'row'
																	WHEN 2 THEN 'page'
																END)
	)
	SELECT table_name, index_name
	INTO #t1
	FROM cte
	WHERE (@table_name IS NULL OR @table_name = table_name)
	AND (@index_name IS NULL OR @index_name = index_name);
	SET @table_name = '';
	SET @index_name = '';
	DECLARE c CURSOR STATIC
	FOR SELECT table_name, index_name FROM #t1;
	OPEN c;
	FETCH NEXT FROM c INTO @table_name, @index_name;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @index_name IS NULL --it's a Heap
			SET @sql_string = 'ALTER TABLE @table_name REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = @compression_type)';
		ELSE --it's an Clustered or Non-Clustered index
			SET @sql_string = 'ALTER INDEX @index_name ON @table_name REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = @compression_type)'; 
		SET @sql_string = REPLACE(@sql_string, '@table_name', @table_name)
		SET @sql_string = REPLACE(@sql_string, '@index_name', COALESCE(@index_name,''))
		SET @sql_string = REPLACE(@sql_string, '@compression_type', @compression_type)
		PRINT @sql_string;
		EXEC sp_executesql @sql_string;
		FETCH NEXT FROM c INTO @table_name, @index_name;
	END
	CLOSE c;
	DEALLOCATE c;
END
ELSE
BEGIN
	PRINT 'La edition/versione del SQL Server non è compatibile con la data compression.'
	PRINT 'Edition: ' + @edition;
	PRINT 'Versione: ' + @version;    
END;
GO

Infine, una query che ci consente di rilevare lo stato di compressione per tutti gli oggetti di un database:

SELECT
    table_name = quotename(schema_name(tbl.schema_id)) + '.' + quotename (tbl.name),
    index_name = quotename(idx.name),
    object_type = CASE idx.index_id
     WHEN 0 THEN 'Heap'
     WHEN 1 THEN 'Clustered'
        else 'Non-Clustered'
    END,
    partition_number = p.partition_number,
    row_count = CAST(p.rows AS float),
    filegroup_name = fg.name,
    data_compression = case p.data_compression
        WHEN 0 THEN 'None'
        WHEN 1 THEN 'Row'
        WHEN 2 THEN 'Page'
    END
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id
INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int)
AND p.index_id=idx.index_id
INNER JOIN sys.indexes AS indx ON p.object_id = indx.object_id
AND p.index_id = indx.index_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = indx.data_space_id
AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.partition_schemes AS ps ON ps.data_space_id = indx.data_space_id
LEFT OUTER JOIN sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id
OR fg.data_space_id = indx.data_space_id

Backup Compression

L'uso della Backup Compression è generalmente molto meno problematico della Data Compression. Premesso che le due tecnologie sono disgiunte, quindi possiamo adottare l'una e/o l'altra sullo stesso database, va detto che l'unica problematica da considerare per la Backup Compression è il significativo incremento di CPU che essa determina durante l'operazione di backup, che sarà ulteriormente influenzato dalla presenza di compressione dei dati, e leggermente attenuato in caso di backup in rete.

Di contro un backup compresso compie meno operazioni di scrittura sul disco, quindi è più veloce - fino a due volte - soprattutto se il backup è su una share di rete, e occupa molto meno spazio - fino a quattro volte più piccolo.

Per far fronte all'incremento di CPU durante i backup compressi, Microsoft suggerisce l'utilizzo del Resource Governor, il quale è in grado di bilanciare il carico della CPU in caso questa sia insufficiente a sostenere il carico di lavoro. Inoltre, poiché la compressione dei backup è fortemente "parallelizzata", la presenza di più CPU ne riduce ulteriormente i tempi di esecuzione.

Da un punto di vista operativo, di default i backup non sono compressi. Si può modificare questo default a livello di istanza SQL attraverso la stored procedure di sistema sp_configure:

sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'backup compression default', 1
reconfigure with override
go

oppure mediante il SQL Server Management Studio, impostando la checkbox Compress Backup nella sezione Database Settings delle Server Properties. In ogni caso, è possibile definire il tipo di backup attraverso l'opzione WITH NO_COMPRESSION o WITH COMPRESSION della classica istruzione di BACKUP. Anche nel SQL Server Management Studio, nella finestra Options della finestra di Backup database si può effettuare una scelta differente rispetto al default a livello di istanza attraverso il menu Set backup compression.

Conclusioni

La Backup compression è senz'altro la meno complessa delle due. Il suo impatto sul sistema si limita a un incremento di CPU in fase di backup, che in caso rappresenti un problema per le operazioni concorrenti è possibile limitare con il Resource Governor. La Data compression, invece, richiede attente valutazioni poiché generalmente impatta negativamente sulle performance dell'oggetto che si va a comprimere, ma al netto di queste criticità garantisce un risparmio notevole di spazio su disco che ben si addice ai Datawarehouse o più in generale ai cosiddetti VLDB (Very Large Database).