SQL Server Instant Initialization

abenedetti — Wed, 22 Feb 2012 10:25:11 GMT

Le performance dei miei database sono, da sempre, uno dei miei pallini…

E, da sempre, ricordo che sono molti i dettagli che contribuiscono a realizzare sistemi performanti.

A partire dalla versione 2005 di SQL Server è possibile utilizzare una funzionalità, chiamata “Instant Initialization”, basata sul file system NTFS.

Per poterla utilizzare il sistema operativo deve necessariamente essere un Windows Server 2003 o superiore / Windows XP Professional o superiore.

Questa funzionalità, sostanzialmente, evita la “zero initialization” invocata:

alla creazione di un file (creazione di un database, aggiunta di file a database esistenti),
ad ogni attività di autogrow (che sia manuale o automatica),
al restore di un database.

Essendo la pratica di avere pre-allocazioni di files una best practice per evitare la frammentazione dei file, poter sfruttare un’inizializzazione istantanea (e quindi risparmiare tempo) diventa una caratteristica sicuramente interessante.

Per poter sfruttare questa caratteristica, il servizio SQL deve essere abilitato al “Perform Volume Maintenance Tasks” (di default gli amministratori sono abilitati a questa policy).

Poichè il mio servizio SQL gira con un account di bassi privilegi creato ad-hoc (altra best practice), per poter sfruttare la funzionalità, devo modificare la policy locale tramite il “Local Group Policy Editor”:

Computer Configuration
Windows Settings
Security Settings
Local Policies
User Rights Assignment

Aggiungo, quindi, il mio account …

… e riavvio il servizio SQL.

Effettuo un test di performance utilizzando questo script per creare un database che abbia un file di dati di 5GB:

     DECLARE @t time(3) = SYSDATETIME()

    CREATE DATABASE Test

    ON  PRIMARY  

    (

        NAME = N'Test', 

        FILENAME = N'C:\Test\Test.mdf', 

        SIZE = 5GB, 

        FILEGROWTH = 100MB

    ) 

    LOG ON  

    (

        NAME = N'Test_log', 

        FILENAME = N'C:\Test\Test.ldf', 

        SIZE = 1MB,  

        FILEGROWTH = 10MB

    ) 

    SELECT DATEDIFF(ms, @t, CAST(SYSDATETIME() AS time(3))) 

        AS FileInitialization 

    GO

Di seguito il tempo senza instant initialization (quindi eseguito prima di aggiungere l’utente del servizio SQL alla policy):

Con instant initialization:

Sottolineo che questo meccanismo coinvolge soltanto i file di dati (MDF e NDF), non il transaction log.

Quando viene utilizzato il meccanismo di inizializzazione istantanea?

creazione di un database
aumento della dimensione di un database esistente
creazione tempdb ad ogni riavvio del servizio
esecuzione di autogrow
durante le operazioni di restore di un db (pre-allocazione dello spazio necessario)

Chiudo il post con altri appunti:

il meccanismo NON è abilitato by default
NON è una caratteristica solo della versione Enterprise di SQL Server
il Transaction Log non può essere inizializzato istantaneamente a causa della sua natura “circolare”

Uno dei meccanismi per verificare l’abilitazione (o meno) di questa funzionalità, possiamo sfruttare il comando whoami / priv tramite la procedura estesa xp_cmdshell (attenzione!):

    -- ATTENZIONE: abilito la xp_cmdshell !

    EXEC sp_configure 'show advanced options', 1

    RECONFIGURE

    GO

    EXEC sp_configure 'xp_cmdshell', 1

    RECONFIGURE

    GO

    ---------------------------------------------

    USE tempdb

    GO

    CREATE TABLE whoami 

    (

        info varchar(2000)

    )

    GO

    INSERT whoami

    EXEC xp_cmdshell 'whoami /priv'

    GO

    SELECT * FROM whoami

    WHERE info LIKE '%volume%'

    GO

    DROP TABLE whoami 

    GO

    -- Disabilito la xp_cmdshell:

    EXEC sp_configure 'show advanced options', 1

    RECONFIGURE

    GO

    EXEC sp_configure 'xp_cmdshell', 0

    RECONFIGURE

    GO

    ---------------------------------------------

Un’altra analisi di performance può essere fatta sui tempi di autogrow generati automaticamente dal sistema:

      /* Creo database per test di performance */

      USE MASTER

      GO

      CREATE DATABASE Test

      ON  PRIMARY  

      (

          NAME = N'Test', 

          FILENAME = N'C:\Test\Test.mdf', 

          SIZE = 3MB, 

          FILEGROWTH = 1MB

      ) 

      LOG ON  

      (

          NAME = N'Test_log', 

          FILENAME = N'C:\Test\Test.ldf', 

          SIZE = 1MB,  

          FILEGROWTH = 10MB

      ) 

      GO

      /* Creo una tabella di appoggio */

      USE test

      GO

      CREATE TABLE bigData

      (

          id INT PRIMARY KEY IDENTITY(1,1),

          chunk CHAR(8000)

      )

      GO

      /* 

      genero autogrow nel database 

      inserendo 50.000 righe

      e visualizzo il tempo trascorso

      */

      SET NOCOUNT ON

      DECLARE @t time(3) = SYSDATETIME()

      DECLARE @i INT

      SET @i = 0

      WHILE @i < 10000

      BEGIN

          INSERT bigData 

              VALUES 

                  ('data 1...'),

                  ('data 2...'),

                  ('data 3...'),

                  ('data 4...'),

                  ('data 5...')

          SET @i = @i + 1

      END

      SELECT DATEDIFF(ms, @t, CAST(SYSDATETIME() AS time(3))) 

          AS [Time] 

      GO

/*
Num di Autogrow Event
--------------- -------------------
391 Data File Auto Grow

Tempo senza instant initialization: 10568
Tempo con instant initialization: 9378
*/

Per approfondire l’argomento (ma non solo) credo che un’ottima lettura possa essere il whitepaper “Physical Database Storage Design” disponibile qui.

Datapage, physical storage location e fn_PhysLocFormatter

abenedetti — Mon, 16 Jan 2012 09:10:24 GMT

In alcuni casi potrebbe essere utile conoscere la posizione fisica delle nostre righe.

In sostanza poter conoscere quale sia il “contenitore” che mantiene al suo interno la nostra informazione, le datapages che contengono i dati.

USE tempdb;
GO

CREATE TABLE anagrafica(
             idAnagrafica smallint , 
             cognome varchar( 35 ) , 
             nome varchar( 35 ));
GO

INSERT INTO anagrafica
VALUES( 1 , 'aaa' , 'bbb' ) , 
      ( 2 , 'ccc' , 'ddd' ) , 
      ( 3 , 'eee' , 'fff' );
GO

SELECT
       *
  FROM anagrafica; 
GO

A questo punto mettiamo il naso dentro le strutture che abbiamo creato:

SELECT
      %%Physloc%% as Physloc,
       sys.fn_PhysLocFormatter( %%Physloc%% )AS SysStorageLocation , 
       idAnagrafica, cognome, nome
  FROM anagrafica;

La “pseudo” colonna [Physloc] ci permette di vedere l’indirizzo univoco di ciascuna riga.

Grazie alla funzione (non documentata) fn_PhysLocFormatter possiamo formattare questo indirizzo in modo da visualizzare l’indirizzo fisico della riga.

Il risultato viene esposto come:

file num
datapage
slot

Una volta che otteniamo queste info, possiamo vedere le nostre pagine grazie al comando DBCC PAGE passando:

dbname / dbid
file num
page num
livello di informazioni che vogliamo (printopt)

Dove il “livello di informazioni” può essere:

0 = header
1 = header and hex dump for rows
2 = header and the page dump
3 = header and detail row information

DBCC TRACEON( 3604 );
DBCC PAGE('TempDB',1,114,2)
DBCC TRACEOFF( 3604 );
GO

Il blog di Andrea Benedetti : internals

SQL Server Instant Initialization

Datapage, physical storage location e fn_PhysLocFormatter