Anzahl von VLFs schätzen

Wer überlegt, wie man seine LDFs richtig dimensioniert, um nach beendeten Wachstum eine überschaubare Anzahl von VLFs zu erhalten, kann das Verhalten relativ exakt vorausberechnen.

Wie bereits in meinen anderen Artikeln zu dem Thema angedeutet unterscheidet sich die Berechnung ab der Version SQL Server 2014, so dass wir hier zwei Varianten berücksichtigen müssen. Das folgende Skript ermittelt also die Version der aktuellen Instanz und verwendet im Normalfall auch die aktuelle Größe des LDF-Files. Wer dies nicht möchte, kann auch einen fixen Zielwert dafür angeben.

Ab SQL Server 2014 ist die Berechnung anders

Ist das Wachstum kleiner als 1/8 der aktuellen Log-File Größe?

Ja: lege nur noch 1 neues VLF an, welches die komplette Größe umfasst, die als Wachstum angegeben wurde.
Nein: Dann verwende weiterhin die Formel, die vorher angewendet wurde.

Also werden nur noch die ersten 8 Vergrößerungen nach der Formel oben gemacht

Die Anzahl für ein gleichmäßiges Wachstum von Anfang an ergibt sich aus

LDF-Größe in MB = ((8 * Faktor) / Faktor) * Schrittweite + X * Schrittweite
<==> LDF-Größe in MB / Schrittweite = ((8 * Faktor) / Faktor) + X
<==> LDF-Größe in MB / Schrittweite - ((8 * Faktor) / Faktor) = X
<==> LDF-Größe in MB / Schrittweite - 8 = X
Die Gesamtzahl ist dann
8 * Faktor + X = N
8 * Faktor + (LDF-Größe in MB / Schrittweite - 8) = N

Bei 1024 MB und 64 MB Schrittweite ergeben sich also eine weitere Vergrößerung um X VLFs
1024 / 64 - 8 = X
8 = X

Die Gesamtzahl ist dann
(8 * 4) + (1024 / 64) - 8 = 40
32 + 8 = 40

Falls die Grenze überschritten wird, ergibt sich die neue Größe aus
(8 * Schrittweite) + X * Schrittweite = S
(8 + X ) * Schrittweite = S
(8 + (LDF-Größe in MB / Schrittweite - 8)) * Schrittweite = S

(8 + (1024 / 64 - 8)) * 64 = S

7 Vergrößerungen erfolgten bei 128, 192, 256, 320, 384, 448, 512 macht insgesamt 8 * 4 = 32 VLFs (32/4 * 64)
Weitere 8 mit je 1 VLF bei 576, 640, 704, 768, 832, 896, 960, 1024 8 * 1 = 8 VLFs ( 8 * 64)

32 VLFs (32/4 * 64)
+8 VLFs +( 8 * 64)
--------------- -------------
40 VLFs 1024

Berechne den idealen Wert für LDF Vergrößerung

Dieses Skript nimmt also die Größe für das aktuelle LDF oder einen fixen Wert als Zielwert an und gibt für unterschiedliche Vergrößerungen an, wieviele VLFs am Ende zu erwarten sind und welche Größe tatsächlich erreicht wird. Das Skript gibt es auch noch einmal komplett als Anhang zu diesem Posting.

-- Berechne den idealen Wert für LDF Vergrößerung für die aktuelle Datenbank
-- oder für einen fixen Wert
DECLARE @MB         integer,
       @Version    CHAR(2);

SELECT @Version = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)),2);

SELECT @MB = ROUND(size * 8.0 / 1024, 0) FROM sys.sysfiles WHERE fileid = 2;
-- Set @MB = 20*1024;  -- hier kann auch eine feste Größe angegeben werden


WITH Simple_Old
AS
(                                                                  -- je VLF
SELECT    CEILING(@MB / 64.0 * 4) AS Variante_64_old              
       , CEILING(@MB / 256.0 * 8) AS Variante_256_old             
       , CEILING(@MB / 512.0 * 8) AS Variante_512_old             
       , CEILING(@MB / 800.0 * 8) AS Variante_800_old             
       , CEILING(@MB / 1024.0 * 16) AS Variante_1024_old          
       , CEILING(@MB / 2048.0 * 16) AS Variante_2048_old          
),
Simple
AS
(                                                                  -- je VLF
SELECT    CEILING( (@MB / 64.0) - 8) AS Variante_64_X      
       , CEILING( (@MB / 256.0) - 8)AS Variante_256_X                 
       , CEILING( (@MB / 512.0) - 8)AS Variante_512_X                 
       , CEILING( (@MB / 800.0) - 8)AS Variante_800_X                 
       , CEILING( (@MB / 1024.0) - 8)AS Variante_1024_X               
       , CEILING( (@MB / 2048.0) - 8)AS Variante_2048_X               
)
SELECT @MB AS Size_Ziel,
CASE WHEN @MB > 64.0 * 8 AND @Version > '11' THEN 8 * 4 + Variante_64_X   ELSE o.Variante_64_old   END AS VLFs_64,           CASE WHEN @MB > 64.0 * 8  AND @Version > '11' THEN  (8 + Variante_64_X) * 64    ELSE CEILING(Variante_64_old / 4) * 64 END AS MB_64,
CASE WHEN @MB > 256.0 * 8    AND @Version > '11' THEN 8 * 8 + Variante_256_X ELSE o.Variante_256_old END AS VLFs_256,      CASE WHEN @MB > 256.0 * 8 AND @Version > '11' THEN  (8 + Variante_256_X) * 256  ELSE CEILING(Variante_256_old / 8) * 256 END AS MB_256,
CASE WHEN @MB > 512.0 * 8    AND @Version > '11' THEN 8 * 8 + Variante_512_X ELSE o.Variante_512_old END AS VLFs_512,      CASE WHEN @MB > 512.0 * 8 AND @Version > '11' THEN  (8 + Variante_512_X) * 512  ELSE CEILING(Variante_512_old / 8) * 512 END AS MB_512,
CASE WHEN @MB > 800.0 * 8    AND @Version > '11' THEN 8 * 8 + Variante_800_X ELSE o.Variante_800_old END AS VLFs_800,      CASE WHEN @MB > 800.0 * 8 AND @Version > '11' THEN  (8 + Variante_800_X) * 800  ELSE CEILING(Variante_800_old / 8) *  800 END AS MB_800,
CASE WHEN @MB > 1024.0 * 8   AND @Version > '11' THEN 8 * 16 + Variante_1024_X ELSE o.Variante_1024_old END AS VLFs_1024,  CASE WHEN @MB > 1024.0 * 8    AND @Version > '11' THEN  (8 + Variante_1024_X) * 1024    ELSE CEILING(Variante_1024_old / 16) * 1024 END AS MB_1024,
CASE WHEN @MB > 2048.0 * 8   AND @Version > '11' THEN 8 * 16 + Variante_2048_X ELSE o.Variante_2048_old END AS VLFs_2048,  CASE WHEN @MB > 2048.0 * 8    AND @Version > '11' THEN  (8 + Variante_2048_X) * 2048    ELSE CEILING(Variante_2048_old / 16) * 2048 END AS MB_2048
FROM Simple s, Simple_Old o
;