Monthly Archives: Luty 2012

Jak zmienić ilość/wielkość VLF dla istniejącej bazy danych

SQL Server nie pozwala bezpośrednio określić ilości i wielkości plików VLF, na które będzie podzielony dziennik transakcji. Znana jest natomiast zasada, wg. której SQL Server dobiera ilość wirtualnych plików dziennika podczas zmiany  jego rozmiaru:

1. Dla przyrostów poniżej 64MB – 4 VLF-y

2. Dla przyrostów powyżej 64MB i mniejszych niż 1GB – 8VLF-ów

3. Dla przyrostów powyżej 1GB – 16 VLF-ów

Konfiguracja dla nowej bazy

Jeżeli tworzymy nową bazę danych i chcemy aby jej dziennik posiadał preferowaną przez nas ilość VLF-ów, to powinniśmy już podczas tworzenia bazy określić wielkość logu transakcyjnego:

CREATE DATABASE testVLFa
ON PRIMARY (
 NAME = 'testVLF_data',
 FILENAME = 'c:\test\testVLF_data.mdf',
 SIZE = 250
)
LOG ON (
 NAME = 'testVLF_log',
 FILENAME = 'c:\test\testVLF_log.ldf',
 SIZE = 500, -- log będzie zawierał 8 VLF-ów
 FILEGROWTH = 500 --i będzie przyrastał także po 8 VLF
)

Dla sprawdzenia naszych rachunków możemy posłużyć się instrukcją:

DBCC LOGINFO('testVLF')

W stworzonej bazie zadeklarowaliśmy 8 plików VLF, po około 60 MB każdy.

Konfiguracja dla istniejącej bazy danych

Zmiana wielkości plików VLF istniejącej bazy danych sprowadza się do zmniejszenia wielkości logu transakcyjnego i jego rozszerzenia wg. podanego wcześniej wzoru. W przypadku gdybyśmy chcieli zmienić wielkość VLF-ów stworzonej wcześniej bazy na 120 MB, to musielibyśmy to zrobić w następujący sposób:

1. Kopia logu i zmniejszenie pliku dziennika 

BACKUP LOG testVLF
TO DISK = 'testVLF_LOG_20120226.trn'
GO
DBCC SHRINKFILE ('testVLF_log', 1)
GO

Dla sprawdzenia efektu wykonajmy instrukcję DBCC LOGINFO:

DBCC LOGINFO ('testVLF')

Na obrazku przedstawionym poniżej widać, że zostały dwa pliki VLF. Moglibyśmy posłużyć się dalszą kombinacją kopii logu transakcyjnego i instrukcji DBCC SHRINKFILE aby się pozbyć jednego z nich (Edit: Dokładny opis jak to zrobić zawiera post „Jak sprawić by plik logu transakcyjnego miał tylko 1 VLF”). Niezależnie od tego czy to zrobimy, czy też nie, to jeden plik VLF zawsze zostanie, gdyż każdy dziennik transakcji musi zawierać przynajmniej jeden VLF.

2. Zwiększamy rozmiar dziennika transakcji i tym samym ilość VLF-ów

ALTER DATABASE testVLF
MODIFY FILE  (
    NAME = 'testVLF_log',
    SIZE = 1100 MB -- 2 plik VLF po 60MB + około 980 MB przyrostu
)

Sprawdźmy rezultat:

DBCC LOGINFO ('testVLF')

Na obrazku powyżej zaznaczyłem nowe VLF-y. Jak widać jest ich 8 i mają rozmiar około 120MB.  Pozostałością po dawnych ustawieniach są dwa pierwsze wirtualne pliki dziennika, których wielkość wynosi nieco ponad 60MB.

Podsumowanie

Nie istnieje bezpośredni sposób zadeklarowania ilości wirtualnych plików dziennika transakcji. Można natomiast tak zadeklarować wielkość logu bazy danych, by uzyskać preferowaną przez nas wartość. W przypadku gdy zechcemy przekonfigurować istniejącą bazę danych, tak aby zmienić ilość (lub wielkość) VLF-ów w dzienniku transakcji, to musimy sie liczyć z tym, że zostanie przynajmniej jeden VLF pierwotnej wielkości.

Reklamy

Imagine Cup, IT Challenge – potwierdzenie uczestnictwa i półfinały

W październiku 2011 chciałem was namówić do udziału w  konkursie  Imagine Cup, a dokładniej kategorii IT Challenge. Nie wiem ilu z was dało się przekonać, ale ja zarejestrowałem się, wziąłem udział w pierwszym z quizów i uzyskałem odpowiednią ilość punktów by przejść dalej. O moich wrażeniach podczas rejestracji i w trakcie wypełniania testu możecie przeczytać w poniższych postach:

Imagine Cup, IT Challenge – a miałem być tyko kibicem

Imagine Cup, IT Challenge – pierwszy quiz za mną

No dobrze ale to było w październiku …

Jak to zwykle bywa oficjalne wyniki podawane są po zakończeniu każdej z rund konkursu.

Niedawno zakończyła się runda pierwsza, a ja dostałem powiadomienie mailowe o tym, że zostałem zakwalifikowany do półfinałów.

Co więcej z witryny konkursu mogę już pobrać certyfikat potwierdzający ten fakt, z czego postanowiłem skorzystać 🙂

Samo potwierdzenie nie było jedyną częścią wiadomości email, na którą czekałem. W mailu znalazł się też adres URL pod jakim jest dostępne studium przypadku związane z kolejnym zmaganiem w konkursie.

Niestety nie mogę przytoczyć co jest w nim zawarte. Mogę natomiast powiedzieć, że z każdym rokiem jest ono trudniejsze, rozwiązując je zawsze uczę sie czegoś nowego, a satysfakcja z tego, że ktoś sprawdzi mój pomysł i powie „to będzie działać” jest ogromna. Nawet jeżeli nie będę jednym z sześciu wybranych 🙂

Ilość VLF a wydajność SQL Server cz. 2

W moim pierwszym poście na temat Virtual Log File pokazałem w jak wewnętrzny podział logu na części może mieć wpływ na wydajność instrukcji zapisujących informacje w dzienniku transakcji. W podsumowaniu zaznaczyłem jednak, że duży rozmiar VLF ma także swoje ciemną stronę 🙂

Opowiem w takim razie o minusach …

Istotną wadą VLF o zbyt dużej objętości jest wydłużony czas wykonania zadań, które wymagają czytania logu transakcyjnego przez SQL Server. Jako przykład takiego zadania można przytoczyć tworzenie migawki bazy.

Przygotujmy środowisko do testów

Zaczniemy od małego przypomnienia. Na potrzeby poprzedniego wpisu stworzone zostały dwie bazy testowe… Pierwsza z nich nazywała się testA i jej log składał się z 24000 VLF-ów, druga, o nazwie testB posiadała ich jedynie 16. Obie bazy cechowały się takim samym rozmiarem dziennika transakcji, który wynosi 6000MB. Różniły się jedynie rozmiarem i ilością VLF-ów.

Wpiszmy do nich nieco rekordów:

-- tu jest dużo małych VLF-ów
USE testA
GO
SELECT *
INTO dbo.testLOG
FROM sys.messages
GO
-- a tu mało dużych :)
USE testB
GO
SELECT *
INTO dbo.testLOG
FROM sys.messages
GO

Następnie sprawdźmy ile plików VLF jest w tych bazach używanych lub zużytych. Aby tego dokonać możemy użyć instrukcji DBCC LOGINFO i zbadać zawartość kolumny „Status” dla wyświetlanego przez nią wyniku. Jeżeli jej wartość wynosi 2, to dany VLF jest aktualnie używany lub też został użyty w przeszłości (i nie została zrobiona kopia zapasowa tej części logu). Gdy kolumna „Status” jest równa 0, to dany VLF jest gotowy do ponownego użytku (w szczególności pierwszego).

DBCC LOGINFO ('testA')

DBCC LOGINFO ('testB')

Jak widać na przedstawionych powyżej obrazkach dla bazy testA zostało zużytych wiele VLF-ów, zaś dla bazy testB tylko jeden. Jak będzie wyglądać sytuacja po wykonaniu kopii zapasowej dziennika transakcji?

BACKUP LOG testA
TO DISK = 'testA_LOG_20120209.trn'
WITH FORMAT
BACKUP LOG testB
TO DISK = 'testB_LOG_20120209.trn'
WITH FORMAT
DBCC LOGINFO ('testA')
DBCC LOGINFO ('testB')

Wykonanie kopii zapasowej spowodowało dziennika spowodowało „wyczyszczenie” niepotrzebnych plików VLF. Teraz obie bazy mają po jednym VLF-ie ze statusem równym 2.

Czas na wykonanie migawek 🙂

Skoro przygotowaliśmy już podłoże do testów, to wykonajmy migawki obu baz danych:

CREATE DATABASE [snTestA]
 CONTAINMENT = NONE
 ON PRIMARY
( NAME = N'TestA',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.CTP3\MSSQL\DATA\TestA_snap.mdf'
)
AS SNAPSHOT OF testA

CREATE DATABASE [snTestB]
 CONTAINMENT = NONE
 ON PRIMARY
( NAME = N'TestB',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.CTP3\MSSQL\DATA\TestB_snap.mdf'
)
AS SNAPSHOT OF testB

Na obrazku powyżej można zaobserwować, że czas wykonania migawki dla drugiej z baz jest sporo większy. Stało się tak, gdyż podczas tworzenia snapshotu SQL Server musiał przeczytać odpowiednio większą część logu niż w przypadku bazy testA. Przypomnę, że obie bazy miały po jednym aktywnym VLF-ie, rozmiar wirtualnego pliku logu dla pierwszej z nich to 0,25MB, a dla drugiej około 375 MB 🙂

Dla ścisłości dodam, że SQL Server nie czytał pełnych 375MB dla bazy testB, a jedynie tę część, która była mu potrzebna do otrzymania spójnego obrazu dla migawki. Tak czy inaczej, na pewno przeczytał więcej niż 0,25MB przy bazie testA .

Podsumowanie

Przeprowadzony przeze mnie test potwierdza, że ilość plików VLF wewnątrz logu transakcyjnego ma znaczenie wydajnościowe. Zwiększając rozmiar VLF-ów należy liczyć się ze spowolnieniem zadań, które czytają dziennik transakcji. Dobierając ilość wirtualnych plików logu należy przeanalizować stosunek zysku i strat, tak aby dokonać optymalnych ustawień dla naszych instancji.

W kolejnym wpisie pokażę jak zmienić ilość VLF dla istniejącej bazy danych 🙂