Autogrow come il cortisone

Iniziamo bene: come il cortisone anche l’autogrow salva la vita.

Ma, come per il cortisone, è il caso di non abusarne ma, piuttosto, di utilizzarlo solo quando non se ne può fare a meno.

L’inizio del post è un pò “farmaceutico” ok, ma credo che possa far arrivare meglio il concetto e le motivazioni che devono spingere ad una determinata scelta di modellazione dei nostri database.

Che cosa succede quando creiamo un nuovo database con quelle che sono le caratteristiche di default (5 MB per il file di dati, con un fattore di autoincremento pari ad 1 MB) o anche con altre impostazioni non sufficienti a contenere, così come sono, tutti i dati che andremo a memorizzare?

Supponendo che, a regime, il nostro db arrivi a contenere XX GB di dati, quali sono gli stress (in termini di attività e di tempi) che verranno sostenuti dal nostro server?

Molto spesso non ci si pone il problema per un semplice motivo: ci pensa il database engine, in autonomia, permettendo ai nostri archivi di aumentare il loro spazio ogni volta che ne avranno bisogno e fino a che il sistema operativo avrà spazio da fornire.

Invece, le domande dovremmo farcele. Perchè un’attività come questa provoca tempi di attesa, frammentazione fisica, carichi di lavoro tranquillamente evitabili.

Proviamo a vedere cosa succede, facendo in modo che un nostro db cresca dalla sua dimensione iniziale (per ipotesi 5 MB), a quella che sarà la sua dimensione effettiva.

Nel test ragioniamo su una sola tabella che arriverà a “pesare” 2 GB.

 /* CREATE DATABASE */
SET NOCOUNT ON;
USE master;
GO

CREATE DATABASE dbTest1MB ON PRIMARY
( 
    NAME = N'dbTest1MB' , FILENAME = N'C:\DATA\dbTest1MB.mdf' , 
    SIZE = 5120 KB , FILEGROWTH = 1024 KB 
)
LOG ON
( 
    NAME = N'dbTest1_log' , FILENAME = N'C:\DATA\dbTest1_log.ldf' , 
    SIZE = 1024 KB , FILEGROWTH = 10% 
);
GO
ALTER DATABASE dbTest1MB SET COMPATIBILITY_LEVEL = 110;
ALTER DATABASE dbTest1MB SET PAGE_VERIFY CHECKSUM;  
GO

USE [dbTest1MB];
GO
IF NOT EXISTS( SELECT name
                 FROM sys.filegroups
                 WHERE is_default = 1 AND name = N'PRIMARY' )
    BEGIN
        ALTER DATABASE dbTest1MB MODIFY FILEGROUP [PRIMARY] DEFAULT;
    END;
GO

/* CREATE TABLE */
CREATE TABLE test( idRecord int PRIMARY KEY
                                IDENTITY( 1 , 1 ) , 
                   column1 char( 200 ));
GO

Inseriamo 10.000.000 di righe:

 /* INSERT DATA */
DECLARE @t datetime;
SET @t = GETDATE( );
INSERT INTO test( column1 )
SELECT TOP 10000000 REPLICATE( 'a' , 200 )AS Col1
  FROM Master.sys.All_Columns C CROSS JOIN Master.sys.All_Columns C2;
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );
GO

Tempo impiegato: 74163 msec.

Queste le dimensioni dei file:

 SELECT CAST( sysfiles.size / 128.0 AS int )AS FileSize , 
       sysfiles.name AS LogicalFileName , 
       sysfiles.filename AS PhysicalFileName , 
       CAST( sysfiles.size / 128.0 - CAST( FILEPROPERTY( sysfiles.name , 'SpaceUsed' )AS int ) / 128.0 AS int )AS FreeSpaceMB , 
       CAST( 100 * CAST((sysfiles.size / 128.0 - CAST( FILEPROPERTY( sysfiles.name , 'SpaceUsed' )AS int ) / 128.0) 
          / sysfiles.size / 128.0 AS decimal( 4 , 2 ))AS varchar( 8 ))AS FreeSpacePct , 
       GETDATE( )AS PollDate
  FROM dbo.sysfiles;

Sul nostro disco:

Due domande:

1. quante sono le attività di autogrow che sono state effettuate, in autonomia (senza che noi, apparentemente, ce ne potessimo accorgere)?
2. qual è la frammentazione fisica, a livello di disco, che queste operazioni hanno portato?

Questo il numero di “allargamenti” richiesti dal db engine ed effettuati dal sistema operativo:

 SELECT [Num di Autogrow] , 
       Event , 
       CONVERT( varchar( 10 ) , StartTime , 103 )AS Data , 
       [File]
  FROM( 
        SELECT COUNT( 1 )AS [Num di Autogrow] , 
               te.Name AS Event , 
               DATEADD( dd , DATEDIFF( dd , 0 , StartTime ) , 0 )AS StartTime , 
               gt.FileName AS [File]
          FROM fn_trace_gettable
       (
          ( 
             SELECT CAST( value AS varchar( 100 ))
             FROM fn_trace_getinfo( DEFAULT )
             WHERE property = 2 AND value IS NOT NULL 
          ) , DEFAULT 
       ) gt 
       JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
        WHERE EventClass IN( 92 , 93 ) AND gt.DatabaseName = DB_NAME( DB_ID( ))
        GROUP BY te.name , DATEADD( dd , DATEDIFF( dd , 0 , StartTime ) , 0 ) , 
        gt.FileName 
       )T
  ORDER BY StartTime;

Per la frammentazione, invece, è più complesso poter fare una valutazione “seria”, vista l’attività che viene fatta in un tempo limitato, su una macchina che fa ben poco altro durante le attività di inserimento.

Guardandola comunque, questi sono i risultati:

La stessa prova di inserimento dati la effettuo su un database opportunamente dimensionato, in modo tale che non venga effettuata alcuna attività di grow.

Tempo di esecuzione: 38760 msec (contro i 74163 msec della prima prova – praticamente la metà del tempo per fare la stessa operazione!)

Questa la frammentazione (naturalmente inesistente) del mio file dati:

Mi verrebbero diverse domande:

• avete mai verificato quanti autogrow sono stati eseguiti (e quando) sui vostri db?
• avete mai controllato la frammentazione fisica dei file dei vostri db?

Enjoy.