Un database di MS-SQL Server è una collezione di oggetti (tabelle, viste, stored procedure, etc..) ed è costituito da almeno un file di dati che ospita le pagine di dati ed un file di log che ospita tutte le transazioni prima che queste vengano completate o annullate.
Una pagina di dati è l'allocazione minima su disco di una tabella di database la cui dimensione è pari a 8Kb, di cui 8.060 bytes riservati ai dati. Molto spesso, lo sviluppatore di applicazioni può ignorare completamente l'esistenza di tali file: egli fa riferimento agli oggetti del database indipendentemente da dove essi siano fisicamente situati o dalle loro dimensioni, quantità e posizionamento. Un amministratore di database, invece, deve fare i conti inevitabilmente con questi file, come nel caso della compressione del database.
Perché comprimere?
Un database nasce con almeno due file, uno per i dati e uno per il log delle transazioni. In fase di creazione, il DBA può specificare una dimensione non inferiore a 1 MB per il file di dati, oppure lasciare che venga ereditata dal database di sistema, denominato "model". Tutti i nuovi DB, infatti, ereditano tabelle, viste di sistema e tutte le impostazioni da model.
Dal momento della sua creazione, la vita del database è caratterizzata, oltre che da operazioni di lettura, da operazioni di inserimento, modifica e cancellazione. Gli inserimenti e le modifiche di dati, accrescono lo spazio occupato, sia dalle pagine di dati nei file di dati, sia dalle transazioni nei file di log. Raggiunto lo spazio massimo del file assistiamo all'autoincremento delle sue dimensioni (sempre che l'incremento automatico sia stato impostato).
Le cancellazioni invece, e in qualche misura le modifiche, liberano spazio dal file di dati senza però influenzarne le dimensioni, in altre parole senza rilasciare questo spazio non più utilizzato al sistema operativo.
Ecco quindi che può rendersi necessario eliminare lo spazio inutile ed ottenere un database compatto (ovvero ottenere file dati e file di log compatti) e lasciare lo spazio libero per altri usi.
Come operare
Per ottenere file compatti e più efficienti abbiamo tre possibilità a disposizione:
1. L'istruzione DBCC SHRINKFILE
Serve per compattare un determinato file di database (dati o log) e, per default, rilascia lo spazio libero al sistema operativo.
Sintassi di DBCC SHRINKFILE
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)[WITH NO_INFOMSGS]
Mediante l'opzione target_size permette di specificare la dimensione desiderata del file dopo la compattazione in termini di MB. Ovviamente il file si ridurrà al massimo fino alla dimensione dello spazio occupato dai dati per i file di dati, o dalle parti attive del log per i file di log.
Mediante le opzioni NOTRUNCATE e TRUNCATEONLY è possibile incidere ulteriormente sull'operazione:
NOTRUNCATEcompatta i dati (spostandoli dalla fine del file alle pagine libere all'inizio del file) e non rilascia lo spazio liberoTRUNCATEONLYrilascia ogni spazio libero non effettuando alcuno spostamento e ignorando l'opzione target_size
Esempio di applicazione di DBCC SHRINKFILE
-- ci si sposta nel contesto del database pubs
USE pubs
GO
-- compatta il file pubs, specificando il target_size di 1 MB
DBCC SHRINKFILE (pubs, 1)
GO
2. L'istruzione DBCC SHRINKDATABASE
Compatta tutti i file di un database. A seguito di uno SHRINKDATABASE, la dimensione dei singoli file non potrà essere inferiore alla dimensione minima prevista per ciascuno. Per dimensione minima si intende quella di creazione oppure quella impostata successivamente con ALTER DATABASE o SHRINKFILE.
Sintassi di DBCC SHRINKDATABASE
DBCC SHRINKDATABASE
( database_name | database_id | 0 [ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)[WITH NO_INFOMSGS]
È possibile specificare con l'opzione target_percent la percentuale di spazio libero desiderato nel file "post-compattazione". Inoltre, ammette le opzioni NOTRUNCATE e TRUNCATEONLY come lo SHRINKFILE con la differenza che il TRUNCATEONLY ignora il target_percent.
Esempio di applicazione di DBCC SHRINKDATABASE
-- compatta l'intero database rilasciando lo spazio libero
DBCC SHRINKDATABASE (pubs)
GO
3. L'impostazione di database AUTOSHRINK
Se impostata a ON l'opzione di database AUTOSHRINK, il sistema provvede automaticamente a compattare i file ad intervalli di 30 minuti. Il risultato che si ottiene ogni volta è equivalente a lanciare l'istruzione:
DBCC SHRINKDATABASE (dbname, 25)
ovvero una compattazione del database che lascia il 25% di spazio libero e rilascia il resto al sistema operativo nei limiti dettati dal comando SHRINKDATABASE. Questa terza soluzione, non è assolutamente consigliabile in ambiente di produzione perchè introduce un overhead significativo inficiando le performance del server.
Impostare la compattazione automatica
-- imposta l'autoshrink a ON
ALTER DATABASE Pubs SET AUTO_SHRINK ON
GO
Discorso particolare va fatto per i file di log, le cui dimensioni restano immutate nonostante AUTOSHRINK o "shrink" espliciti se prima non avviene un troncamento delle loro parti inattive, cioè le transazioni processate dal gestore del log durante la fase di CheckPoint. Il troncamente del log delle transazioni avviene quando:
- si verifica un CheckPoint ed il modello di recupero (Recovery Model) del database è impostato a
SIMPLE(oppure, in alternativa, l'opzione di database trunc.log on Chkpt. è impostata aTRUE) - in presenza di un modello di recupero
FULLoBULK LOGGED, si effettua un backup del log (BACKUP LOG) oppure si esplicita un troncamento del log mediante l'istruzioneBACKUP LOG dbname WITH TRUNCATE_ONLY (o NO_LOG). È bene ricordare che per fare un backup del log occorre aver fatto almeno una volta un backup completo del database.
Esempio (a)
-- imposta il modello di recupero a SIMPLE
ALTER DATABASE Pubs SET RECOVERY SIMPLE
GO
-- legge l'impostazione appena fatta
SELECT DATABASEPROPERTYEX( 'Pubs' , 'Recovery' )
GO
Esempio (a)
-- imposta il modello di recupero a FULL
ALTER DATABASE Pubs SET RECOVERY FULL
GO
---- esegue un backup completo
BACKUP DATABASE Pubs TO DISK = 'C:ProgrammiMicrosoft SQL ServerMSSQLBACKUPPubs.bck'
GO
---- esegue un backup del log
BACKUP LOG Pubs TO DISK = 'C:ProgrammiMicrosoft SQL ServerMSSQLBACKUPPubs.bck'
GO
-- esegue un troncamento esplicito del log
BACKUP LOG Pubs WITH TRUNCATE_ONLY
GO
Conclusioni
È importante sottolineare che la compattazione non ha lo scopo di migliorare le prestazioni del database, anzi, in genere una compattazione peggiora - indirettamente - le performance delle future operazioni di inserimento e aggiornamento perchè esse richiederanno necessariamente un incremento delle dimensioni del file di dati che si traduce in una richiesta di spazio su disco da parte del DBMS al sistema operativo. La compattazione di un database non è sinonimo di riorganizzazione dei dati - che invece avviene con la riorganizzazione degli indici -, bensì di rilascio di spazio libero al sistema operativo da parte del DBMS.
