SQL Server Instant Initialization

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

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