SQL 2012 e VLFs
Un post veloce per mostrare una funzionalità molto gradita introdotta da SQL Server 2012. Come sicuramente saprete, troppi VLF (virtual log files) possono impattare negativamente le operazioni del transaction log.
Fino SQL Server 2008 R2 bisognava attivamente documentarsi per avere informazioni sul numero di VLF (ad esempio con DBCC LOGINFO). SQL Server 2012, invece, mostra un messaggio di warning nell'error log se, durante la fase di redo, l'engine identifica un database con più di 10000 (si, diecimila) VLF.
Il messaggio è il seguente:
Database testVLF has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.
Volete provare voi stessi? Potete usare questo script:
use [master];
GO
CREATE DATABASE testVLF;
ALTER DATABASE testVLF SET RECOVERY FULL;
BACKUP DATABASE testVLF TO DISK='nul';
BACKUP LOG testVLF TO DISK='nul';
USE [master]
GO
ALTER DATABASE [testVLF] MODIFY FILE ( NAME = N'testVLF_log', FILEGROWTH = 1KB )
GO
USE [testVLF]
GO
CREATE TABLE test(testo CHAR(1200));
GO
PRINT 'Inserimento 100 righe...'
GO
INSERT INTO test SELECT 'somedata';
GO 100
PRINT 'Inserimento massivo righe...';
GO
INSERT INTO test
SELECT * FROM test;
GO 14
DBCC LOGINFO(testVLF);
GO
USE [master];
GO
BACKUP DATABASE [testVLF] TO DISK='I:\Samples\TooManyVLFs\testVLF.bak' WITH COMPRESSION;
GO
DROP DATABASE testVLF;
GO
RESTORE DATABASE [testVLF] FROM DISK = N'I:\Samples\TooManyVLFs\testVLF.bak' WITH STATS=1;
GO
EXEC xp_readErrorLog
Sostanzialmente stiamo creando un database nuovo a cui impostiamo l'autogrowth a 1024 bytes (ma che SQL alzerà al minimo che è 262144 bytes). A questo punto riempiamo il tlog fino a generare almeno 10000 vlf (da cui avremo un tlog di almeno 2.5 GB circa). Ora effettuiamo un backup-drop-restore (potreste dover cambiare il percorso di backup sulla vostra macchina di test).
L'error log riporterà un warning simile a questo:
Nota: non considerate "giusto" il numero di VLF se è minore di 10000. Il valore di 10000 è preso come soglia oltre la quale i VLF sono decisamente troppi. Come sempre, l'unico vero valore giusto è quello che soddisfa le vostre esigenze. In altre parole, testate :). Lo scopo di questo warning è di evidenziare, a beneficio dei DBA, una situazione che sta sfuggendo di controllo.
Happy coding,