• Articolo

Utilizzo di file e filegroup

Un database di SQL Server si basa su un set di file del sistema operativo. Le informazioni sui dati e sui log non vengono mai incluse nello stesso file e i singoli file vengono utilizzati da un solo database. I filegroup sono raccolte denominate di file che semplificano il posizionamento dei dati e le attività di amministrazione, ad esempio le operazioni di backup e ripristino. Per ulteriori informazioni, vedere Architettura di file e filegroup.

Strategia di riempimento dei file e dei filegroup

I filegroup utilizzano una strategia di riempimento proporzionale per tutti i file presenti in ogni filegroup. Quando i dati vengono scritti nel filegroup, Motore di database di SQL Server scrive una quantità di dati proporzionale allo spazio disponibile in ogni file del filegroup anziché scrivere tutti i dati nel primo file fino all'esaurimento dello spazio e quindi passa al file successivo. Ad esempio, se nel file f1 sono disponibili 100 megabyte (MB) e nel file f2 sono disponibili 200 MB, un extent verrà allocato dal file f1, due extent dal file f2 e così via. In questo modo, entrambi i file vengono riempiti quasi simultaneamente e si ottiene uno striping semplice.

Non appena si esaurisce lo spazio di tutti i file di un filegroup, Motore di database espande automaticamente i file uno alla volta in in base a un meccanismo round robin per consentire l'inserimento di ulteriori dati, a condizione che sia stato impostato l'aumento di dimensioni automatico del database. Si supponga, ad esempio, che un filegroup includa tre file per i quali è stato impostato l'aumento di dimensioni automatico. Quando lo spazio viene esaurito in tutti i file del filegroup, viene espanso solo il primo file. Quando il primo file è pieno e non è possibile scrivere altri dati nel filegroup, viene espanso il secondo file. Quando il secondo file è pieno e non è possibile scrivere altri dati nel filegroup, viene espanso il terzo file. Quando il terzo file è pieno e non è possibile scrivere altri dati nel filegroup, viene espanso nuovamente il primo file e così via.

Ottimizzazione delle prestazioni del database

L'utilizzo di file e filegroup consente di ottimizzare le prestazioni del database, perché il database può essere creato su più dischi, controller del disco o sistemi RAID (Redundant Array of Independent Disks). Ad esempio, se nel computer sono disponibili quattro dischi, è possibile creare un database che include tre file di dati e un file di log, con un file su ogni disco. L'accesso ai dati viene eseguito in parallelo da quattro testine di lettura/scrittura simultaneamente, consentendo di velocizzare le operazioni del database. Per ulteriori informazioni sulle soluzioni hardware, vedere Prestazioni del database.

I file e i filegroup consentono inoltre di posizionare i dati, perché è possibile creare una tabella in un filegroup specifico. Ciò consente di ottimizzare le prestazioni grazie al fatto che si possono indirizzare su un disco specifico tutte le operazioni di I/O relative a una tabella specifica. Ad esempio, è possibile inserire una tabella utilizzata molto frequentemente in un file di un filegroup che si trova in un disco specifico, mentre è possibile inserire le altre tabelle del database, utilizzate meno frequentemente, in altri file di un altro filegroup che si trovano in un secondo disco.

Implementazione delle strategie di backup e ripristino

È possibile ripristinare i database che includono più filegroup tramite un processo denominato ripristino a fasi. Il ripristino a fasi può essere utilizzato con tutti i modelli di recupero, ma risulta più efficiente per i modelli di recupero con registrazione completa e con registrazione minima delle operazioni bulk rispetto ai modelli con registrazione minima. Lo scenario del ripristino a fasi include le tre fasi del ripristino, ovvero la copia, il rollforward e il rollback dei dati. Per ulteriori informazioni, vedere Esecuzione di ripristini a fasi.

Se si utilizzano più filegroup, è possibile eseguire il backup e il ripristino dei singoli file di un database. Nel modello di recupero con registrazione minima, è possibile eseguire il backup di file unicamente per i file di sola lettura. I backup di file consentono di aumentare la velocità del recupero, tramite il ripristino dei soli file danneggiati e non dell'intero database. Se, ad esempio, un database è costituito da più file che si trovano in dischi diversi, in caso di errore in uno dei dischi sarà sufficiente ripristinare il file che si trova in tale disco. Per ulteriori informazioni, vedere BACKUP (Transact-SQL).

Regole per la progettazione di file e filegroup

Per i file e i filegroup sono valide le regole seguenti:

  • Un file o un filegroup non può essere utilizzato da più database. Ad esempio, i file sales.mdf e sales.ndf, che includono dati e oggetti del database sales, non possono essere utilizzati da altri database.

  • Un file può essere membro di un solo filegroup.

  • I file di log delle transazioni non fanno mai parte di un filegroup.

Indicazioni

Vengono riportate di seguito alcune indicazioni di carattere generale relative all'utilizzo di file e filegroup:

  • La maggior parte dei database funziona in modo ottimale con un singolo file di dati e un unico file di log delle transazioni.

  • Se si utilizzano più file, creare un secondo filegroup per i file aggiuntivi e impostarlo come filegroup predefinito. In questo modo, il file primario conterrà unicamente le tabelle e gli oggetti di sistema.

  • Per ottimizzare le prestazioni, creare i file o i filegroup sul numero maggiore possibile di dischi fisici locali disponibili. Posizionare in filegroup diversi gli oggetti che creano conflitti di spazio.

  • Utilizzare i filegroup per il posizionamento di oggetti su dischi fisici specifici.

  • Posizionare in filegroup diversi le diverse tabelle utilizzate nelle stesse query di join. Questa operazione consente di ottimizzare le prestazioni, grazie al fatto che i dati uniti in join vengono cercati con operazioni di I/O su disco in parallelo.

  • Posizionare in filegroup diversi le tabelle utilizzate molto frequentemente e gli indici non cluster che appartengono a queste tabelle. Questa operazione consente di ottimizzare le prestazioni, grazie al fatto che vengono eseguite operazioni di I/O in parallelo se i file si trovano su dischi fisici diversi.

  • Non posizionare il file o i file di log delle transazioni sullo stesso disco fisico in cui si trovano gli altri file o filegroup.