Pagine

lunedì 12 marzo 2012

Come Transaction Log Growth e Virtual Logs impattano sulle Performance ?

Il Transaction Log è un file che molto spesso, quando si pensa alle performance, viene messo nel dimenticatoio.
Se riuscissimo a "gestire" il T-Log in modo ottimale, ed è possibile, potremmo migliorare senza alcuno sforzo le performance del nostro DB.
I fattori che comportano un miglioramento o un peggioramento delle performance sono : Policy di Growth e numero di Virtual Log.

Policy di Growth

Vediamo cosa succede ad impostare "malamente" la crescita dei file.
Creo un DB impostando la dimensione iniziale dei file ai valori minimi consentiti, 1 MB per il T-Log e 3MB per il file dati.
Come policy di crescita abilito l'autogrowth ma lo imposto a solo 1 MB per entrambi i file.

Creo poi un DB con una dimensione iniziale identica al precedente ma con policy di autogrowth differenti : 200MB per entrambi i file.

--Database con growth di 1MB per file
CREATE DATABASE [SmallGrowth] ON  PRIMARY 
( NAME = N'SmallGrowth', FILENAME = N'C:\Sql Server 2008 R2\User Database\SmallGrowth.mdf' , SIZE = 3MB , FILEGROWTH = 1MB )
 LOG ON 
( NAME = N'SmallGrowth_log', FILENAME = N'C:\Sql Server 2008 R2\User Database\SmallGrowth_log.ldf' , SIZE = 1MB , FILEGROWTH = 1MB )
GO

ALTER DATABASE [SmallGrowth] SET RECOVERY SIMPLE
GO

--Database con growth di 200MB per file
CREATE DATABASE [GoodGrowth] ON  PRIMARY 
( NAME = N'GoodGrowth', FILENAME = N'C:\Sql Server 2008 R2\User Database\GoodGrowth.mdf' , SIZE = 3MB , FILEGROWTH = 200MB )
 LOG ON 
( NAME = N'GoodGrowth_log', FILENAME = N'C:\Sql Server 2008 R2\User Database\GoodGrowth_log.ldf' , SIZE = 1MB , FILEGROWTH = 200MB )
GO

ALTER DATABASE [GoodGrowth] SET RECOVERY SIMPLE
GO


Come potete vedere le dimensioni dei file dei DB sono esattamente le stesse.


Creiamo in entrambi i Database una tabella disegnata in modo che con poche righe si raggiungano velocemente circa 130MB di dimensione ( non è di sicuro l'esempio di una tabella ben disegnata..... ;-)

Create table MyTestLogTable
(
 id int primary key,
 Valore char(3500)
)


Ora popoliamo il db [SmallGrowth] con 32500 record quindi circa 130MB e con il profiler monitoriamo gli eventi Data File Auto Growth e Log File Auto Growth...

Set statistics time on
;with CtePopolaTab
as
(Select 1 R union All Select 1 R),
Cte4 as (Select A.* from CtePopolaTab A,CtePopolaTab B),
Cte16 as (Select A.* from Cte4 A,Cte4 B),
Cte256 as (Select A.* from Cte16 A,Cte16 B),
Cte65536 as (Select A.* from Cte256 A,Cte256 B),
Ctex as (Select A.* from Cte65536 A,Cte65536 B),
Result as (Select ROW_NUMBER() over(Order by r) Riga from Ctex )
Insert Into MyTestLogTable
Select Riga,'Valore Riga ' + CAST(Riga as varchar(10)) from Result
 Where Riga <= 32500
Set statistics time Off

Come potete vedere si sono verificati tantissimi eventi di crescita per entrambi i file

Set Statistics Time On ha restituito il seguente risultato:

SQL Server Execution Times:
CPU time = 1186 ms, elapsed time = 35913 ms.

(32500 row(s) affected)

Ora eseguiamo la stessa operazione sul db [GoodGrowth] ed anche in questo caso monitoriamo con il profiler gli eventi Data file growth e Log file Growth... Come potete vedere il numero di operazioni di growth è drasticamente diminuito....
Ed anche il tempo impiegato per eseguire la query è calato, Set Statistics Time On ha restituito il seguente risultato:

SQL Server Execution Times:
CPU time = 811 ms, elapsed time = 14234 ms.

(32500 row(s) affected)

Vediamo le dimensioni dei file dei DB :
La differenza in termini di performance è data dal numero di growth che si sono verificati.... Nel DB SmallGrowth si sono verificati centinaia di growth che hanno rallentato l'esecuzione della query. Nel DB GoodGrowth si è verificato un solo growth per file di 200 MB. Meno operazioni di Growth implemento più veloce vado. Scegliamo quindi un parametro di growth adeguato alle nostre necessità e che si verifichi molto raramente.


Numero di Virtual Log

SQL Server suddivide i file di log in "settori" chiamati Virtual Log. Il numero di virtual log contenuti dipende dalla modalità di growth del file log:
Growth minore di 64MB : per ogni growth vengono creati 4 Virtual log file
Growth maggiore di 64MB e minore di 1GB : per ogni growth vengono creati 8 Virtual log file
Growth maggiore di 1GB : per ogni growth vengono creati 16 Virtual log file
Il numero di Virtual Log elevato (>50) potrebbe peggiorare le performance del database. Per scoprire quanti Virtual Log abbiamo nel nostro DB dobbiamo eseguire il comando
DBCC LOGINFO()

Il numero di righe restituite definisce il numero di virtual log. Nel caso dell'esempio precedente per il db [SmallGrowth] otteniamo 644 VLF
mentre per il db [GoodGrowth] otteniamo 12 VLF
Eseguiamo ora un'operazione di Delete su entrambi i database. Tenete presente che le tabelle dei database sono esattamente le stesse, hanno la stessa struttura e la stessa quantità dai dati contenuti....
Checkpoint  --Implemento un checkpoint per poter "riciclare lo spazio" del file log
Set statistics time on
DELETE FROM MyTestLogTable Where id <= 10000
Set statistics time off

Nel Db [SmallGrowth] otteniamo

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 187 ms, elapsed time = 2477 ms.

(10000 row(s) affected)

Mentre nel Db [GoodlGrowth] otteniamo
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 234 ms, elapsed time = 913 ms.

(10000 row(s) affected)

L'unica differenza tra i due DB è il numero di Virtual log..... il primo 644, il secondo 12. Un numero di VLF elevato può peggiorare le performance delle Insert, update e delle delete che effettuiamo. Inoltre potrebbe anche peggiorare di moltissimo le operazioni di recovery Redo ed Undo del Db. Il blog di Kimberly Tripp consiglia 8 operazioni per ottimizzare il throughput del log.

Cerchiamo quindi di trovare una policy di growth adeguata alle nostre esigenze tenendo in considerazione anche il numero di Virtual log. Pochi Growth e pochi VLF potrebbero migliorare le performance del DB senza dover metter mano a tabelle indici etc...









Nessun commento:

Posta un commento