Autoshrink... no grazie

Datamaze
19.06.20 11:25 AM Comment(s)

Uno dei motivi per cui si abilita l'opzione AUTO_SHRINK è quella di cercare di recuperare dello spazio inutilizzato nel database e quindi garantire maggiore spazio libero sul disco. Ci sono però degli effetti collaterali che spesso superano i benefici attesi.


Cos'è l'Autoshrink in SQL Server

AUTO_SHRINK è una opzione a livello di singolo database. Quando viene abilitata, il database diventa oggetto, attraverso un task in background, dell'operazione di compattazione. Il task valuta periodicamente tutti i database che hanno questa opzione attivata ed esegue l'operazione di compattazione dei rispettivi file dati e/o dei transaction log (nel caso dei log, la compattazione avviene solo se il recovery model del database è impostato a SIMPLE oppure se è stato fatto un backup del log). Lo shrink avviene solo se lo spazio inutilizzato all'interno del file è superiore al 25%. La dimensione viene conseguentemente ridotta del 25% senza superare il limite definito dalla dimensione del file al momento della sua creazione. Non possono essere compattati database di tipo read-only.


Autoshrink database properties


Quali sono i contro dell'Autoshrink

Occorre prestare molta attenzione quando si imposta questa opzione sui database di una istanza SQL Server. Frequenti operazioni di compattazione e riallocazione di spazio possono condurre a diversi problemi di performance:

  1. La compattazione del database è il modo più veloce per generare frammentazione. Le pagine dati del database vengono spostate partendo dalla fine del file al primo spazio libero disponibile al suo interno e questa operazione viene ripetuta finché non vengono riempiti tutti i buchi. Questo crea disordine, nonostante sembri il contrario, all'interno della struttura delle pagine dati. Se questo processo è poi combinato con la ricostruzione giornaliera degli indici, operazione che allarga lo spazio allocato nel database durante il processo di rebuild o riorganizzazione, ci rendiamo conto che ci mettiamo nella classica situazione del cane che si morde la coda: compatta, allarga, compatta, allarga… inutile.

  2. Dopo questa operazione, le operazioni DML o DDL che richiedono una riallocazione di spazio del database, potrebbero rallentare significativamente per consentire al sistema operativo di allocare lo spazio necessario su file system.

  3. Il task in background che si occupa della compattazione potrebbe consumare un quantitativo di risorse importante (CPU e disco) se questa operazione fosse richiesta frequentemente e per numerosi database.

  4. L'operazione richiede anche di acquisire dei lock sul database che potrebbero interferire con le normali attività di accesso ai dati da parte degli utenti.

di Cristiano Gasparotto, pubblicato il 28 maggio 2019
Monitoring SQL Server