SQL Server - Algoritmo Proportional fill, Autogrow e loro effetto sulle dimensioni dei file di un filegroup

  • Article

Algoritmo di Proportional fill

L'algoritmo di Proportional fill (Proportional fill and how to check whether it works, Using Files and Filegroups) è l'algoritmo utilizzato per allocare extent (Understanding Pages and Extents) all'interno dei datafile di un filegroup (Files and Filegroups Architecture) che contiene da due a più datafile.

Sql server alloca un quantità di extent , proporzionale allo spazio libero all'interno di ciascun datafile di un filegroup, mantenendo in questo modo la stessa percentuale di spazio utilizzato su ciascun file.

Il calcolo della proporzione da utilizzare per allocare lo spazio nei file viene effettuato con le seguenti modalità:

  • la percentuale di Proportional fill viene ricalcolata dopo aver allocato 8192 extent
  • il numero degli extent liberi del file con più spazio libero, viene diviso per il numero degli extent liberi degli altri file
  • il rapporto considera solo numeri interi

Tramite l'utilizzo dell'algoritmo del Proportional fill , i file all'interno di un filegroup si riempono contemporaneamente.

Nel caso di datafile con uguali dimensioni iniziali, dovremmo aspettarci un uguale utilizzo dello spazio su tali file man mano che il database cresce di dimensione.

L'algoritmo di Proportional fill considera solo interi, per cui si possono determinare uguali allocazioni anche con diverso spazio libero tra i file , ad esempio quando il rapporto tra lo spazio libero tra i file non è esattamente di 1 a 2 .
Avendo, all'interno di un filegroup, un datafile con 10 Gb di spazio libero e un datafile con 19,9 Gb di spazio libero , il rapporto tra lo spazio libero dei due datafile non è esattamente di 1 a 2, per cui il tasso di allocazione sui datafile sarà identico. In questa situazione, lo spazio verrà allocato nella stessa percentuale anche in file aventi una diversa quantità di spazio libero, con conseguente sbilanciamento dello spazio occupato tra di essi.

Siccome l'algoritmo è ricalcolato frequentemente, gli errori dovuti al considerare solo numeri interi, dovrebbero bilanciarsi, arrivando generalmente a situazioni in cui tutti i file hanno la stessa percentuale di spazio utilizzato e arrivano in contemporanea ad occupare tutto lo spazio libero.

Autogrow

Le impostazioni di autogrow dei file di un database controllano il comportamento di tali file nel momento in cui raggiungono la loro massima dimensione Database Properties (Files Page)

Ho spesso affrontato problemi di performance causati da una crescita per autogrow dei datafile o log file di un database. Il consiglio per evitare che l’autogrow venga scatenato durante l’attività sul database, causando problemi di performance, è di controllare periodicamente che ogni file abbia necessario spazio libero all'interno. In tal modo, lo spazio verrà allocato internamente al file e si eviterà la necessità di crescita automatica a causa del raggiungimento della massima dimensione.

L’autogrow dovrebbe essere utilizzato solo per evitare problemi causati da crescite inaspettate e non pianificate del database e non come sostituto di un’opportuna analisi sull’andamento delle dimensioni del database.

Attenzione è anche da riservare alla percentuale impostata di autogrow.

Consideriamo un database che ha raggiunto la dimensione massima e su cui l’applicazione tenta di inserite dati. All’inserimento di dati, siccome non è più presente spazio libero, l’autogrow viene attivato.

Se l’autogrow è impostato al 10% delle dimensioni del database e il database è di circa 30 GB, l’autogrow tenterà di aumentare le dimensioni del database di 3 GB. Tale espansione potrà richiedere molto tempo e terminare prima del timeout applicativo , determinando il fallimento dell’operazione di insert con un generico errore di timeout.

Di seguito alcuni articoli relativi alle considerazioni sulle impostazioni di autogrow

L'autogrow oltre a causare problemi di performance , può indirettamente causare uno sbilanciamento tra le dimensioni dei datafile di uno stesso filegroup, sebbene tali file siano stati impostati inizialmente alla stessa dimensione e con identiche configurazioni di autogrow.

Di seguito la spiegazione di come ciò possa avvenire

Autogrow e sbilanciamento dello spazio allocato tra i file di un filegroup nonostante il funzionamento del Proportional Fill

Sql server, allo startup, crea una lista dei datafile disponibili e calcola la percentuale di Proportional fill come sopra specificato.

Dopo il verificarsi di un autogrow, Sql server conserva in memoria l'informazione di quale file nella lista sopra indicata è aumentato di dimensione per autogrow.

In caso di mancanza di spazio su un database, il file successivo viene preso in considerazione per eventuali allocazioni.

Al restart di Sql server, l'informazione relativa al file che ha subito l'autogrow, non viene mantenuta, per cui il primo file della lista viene nuovamente preso in considerazione in casi di mancanza di spazio , anche se l'autogrow si è appena verificato su di esso.

A causa di questo meccanismo può capitare che nonostante il Proportional fill, uno dei file di un filegroup , possa raggiungere dimensioni maggiori rispetto agli altri sebbene le configurazioni iniziali siano state identiche.

Soluzione

Al fine di evitare lo sbilanciamento delle dimensioni di file configurati inizialmente con le stesse dimensioni all'interno di un filegroup si suggerisce di :

  • aumentare manualmente e contemporaneamente la dimensione dei file di un filegroup

  • verificare lo spazio libero all'interno dei file e aumentarli prima che si verifichi l'autogrow

  • Per Sql server 2005, la seguente select , mostra lo spazio libero e allocato all'interno dei file

    select
    dbfiles.file_id,
    [FILE_SIZE_MB] =
    convert(decimal(12,2),round(dbfiles.size/128.000,2)),
    [SPACE_USED_MB] =
    convert(decimal(12,2),round(fileproperty(dbfiles.name,'SpaceUsed')/128.000,2)),
    [FREE_SPACE_MB] =
    convert(decimal(12,2),round((dbfiles.size-fileproperty(dbfiles.name,'SpaceUsed'))/128.000,2)) ,
    NAME = left(dbfiles.NAME,15),
    FILENAME = left(dbfiles.physical_name,30)
    from
    sys.database_files dbfiles

  • In Sql server 2008, è possibile utilizzare il trace flag -T1117 per attivare l' autogrow di tutti i file di un filegroup contemporaneamente, garantendo in questo modo che tutti i file rimangano alla stessa dimensione

 

Raffaella Canobbio
Senior Support Escalation Engineer
Microsoft Enterprise SQL Support