SQL Server Instant Initialization

Published 22 febbraio 12 11.25 | abenedetti

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

image

Aggiungo, quindi, il mio account …

image

… 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):

image

Con instant initialization:

image

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.

Comments

# FCanepari said on febbraio 23, 2012 01.41 :

Sei mancato per un po'... ma questa è una chicca (non mi dispiace nemmeno il calendario)  ,-)

# abenedetti said on febbraio 23, 2012 06.22 :

Si, anche a me era mancata la community ;-)

Grazie del commento!

# sgovoni said on febbraio 24, 2012 01.09 :

>> Sei mancato per un po'... ma questa è una chicca

+1 :)

# Andrea Benedetti Blog said on agosto 23, 2012 05.38 :

Le dimensioni dei file database di SQL Server possono aumentare automaticamente rispetto ai valori originari

# Andrea Benedetti Blog said on settembre 6, 2012 12.21 :

Un’istanza SQL Server ha un solo tempdb e, per questo e per il fatto che molte attività lo possono utilizzare

This Blog

Syndication