Aumento automatico file & Trace Flag. To be or not to be…

Le dimensioni dei file database di SQL Server possono aumentare automaticamente rispetto ai valori originari.
Grazie a questa caratteristicha, prima che lo spazio assegnato al file si esaurisca, le sue dimensioni possono aumentare in maniera automatica (senza alcun intervento di manutenzione) in base all'incremento specificato (sia esso in termini percentuali o fissi).
 
Se un filegroup include più file, le loro dimensioni non aumentano automaticamente fino al momento in cui lo spazio di tutti i file non si va ad esaurire.

L'aumento delle dimensioni avviene quindi in base a un meccanismo round robin.

Questo comportamento di default potrebbe non essere il comportamento che preferiamo.

Da qui il titolo del post che potrei riassumere meglio in: non sono mai stato un amante dei Trace Flag di SQL Server “a priori”, ma qualcuno  può essere sempre preso in considerazione.

Partiamo da un esempio:

• Costruisco un database con 4 file dati nello stesso filegroup
• Effettuo INSERT in modo tale da terminare lo spazio definito in orgine (5 MB)
• Verifico le dimensioni dei file e lo spazio utilizzato da ciascuno

 --> 1) CREATE DATABASE
SET NOCOUNT ON
CREATE DATABASE TraceFlagDemo
ON
(
    NAME = fileData_01,
    FILENAME = 'C:\data\fileData_01.mdf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
),
(
    NAME = fileData_02,
    FILENAME = 'C:\data\fileData_02.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
),
(
    NAME = fileData_03,
    FILENAME = 'C:\data\fileData_03.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
),
(
    NAME = fileData_04,
    FILENAME = 'C:\data\fileData_04.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
LOG ON
( NAME = fileData_log,
    FILENAME = 'C:\data\fileLog.ldf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
GO


--> 2) INSERT SOME DATA
USE TraceFlagDemo
GO

CREATE TABLE fileData_TestData
(
    Id uniqueidentifier default newid(),
    Created Datetime2 default sysdatetime(),
    DataValues char(6000)
)
GO

INSERT INTO fileData_TestData (DataValues)
VALUES ('some data ...')
GO 20000


--> 3) FILE SIZE / SPACE USED
USE TraceFlagDemo
GO

SELECT
 file_id,
 physical_name,
 [File Size (MB)] = CONVERT(DECIMAL(12,2),ROUND(size/128.000,2)),
 [Space Used (MB)] = CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(name,'SPACEUSED')/128.000,2))
FROM sys.database_files
WHERE (data_space_id = 1)
GO

 Questo quello che ottengo:

Vediamo cosa succedere andando ad aggiungere, ai parametri di startup, il Trace Flag 1117.

Per farlo è sufficiente impostare il valore all’interno della videata di configurazione del servizio (in SQL Server 2012 c’è una tab dedicata):

Oppure andare direttamente nel registro:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer\Parameters

Oppure, avendo un'istanza nominata:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012\MSSQLServer\Parameters

ed inserire il valore, come in figura:

Con il flag abilitato, eseguendo gli stessi script:

Ciascun file, adesso, contiene la stessa percentuale di spazio utilizzato, con i dati ugualmente distribuiti.

Attenzione.

• Questo flag, una volta abilitato, interessa tutta l’instanza, quindi tutti i database.
• Naturalmente un autogrow che interessa un filegroup, non interessa eventuali altri filegroup presenti nel medesimo database.
• Naturalmente, come spiegato sopra, è inutile andare ad impostare il flag se il nostro database ha un solo file per filegroup.

Giusto per aggiungere qualche altra riga di TSQL, ecco come posso vedere “al volo” quanti file sono contenuti nei filegroup:

 select 
    FG.name as [FileGroup Name], 
    count(1) as [Nr of Files]
from sys.database_files F
join sys.filegroups FG on F.data_space_id = FG.data_space_id 
group by FG.name
order by FG.name

Per vedere Trace Flag abilitati sull’istanza:

 DBCC TRACESTATUS 
/*
Status: Indicates whether the trace flag is set ON of OFF, either globally or for the session.
Global: Indicates whether the trace flag is set globally
Session: Indicates whether the trace flag is set for the session 
*/

Ricordo sempre, soprattutto se deciderete di abilitare questo flag, che è buona norma avere attiva la funzionalità di Instant File Inizialitazion (vedi qui).

Altre informazioni sui Trace Flags le potete trovare qui.