Archiwa blogu

Statystyki w SQL Server: GEEK LEVEL BONUS

Jak zapewne pamiętacie kilka dni temu prowadziłem prelekcję na spotkaniu Trójmiejskiej Zawodowej Grupy .Net. W ramach tego spotkania pokazałem demo, które jest odpowiedzią na bardzo specyficzny problem 🙂

Definicja problemu

Wyobraźmy sobie sytuację, w której jesteście osobami z działu developmentu i dostajecie zgłoszenie odnośnie problemów wydajnościowych związanych z działaniem waszej aplikacji. Standardowym sposobem byłoby podłączenie się do instancji i stworzenie odpowiedniego trace-a. Tym razem jest niestety nieco inaczej – dane klienta są tajne i nie możecie w żaden sposób ich podejrzeć. Oczywiście możecie prosić o różnego rodzaju zrzuty wydajnościowe, jednak działanie „po omacku” i przy pomocy osób trzecich jest czasem problematyczne …

Co możemy w takim razie zrobić?

I tutaj do akcji wchodzi mój GEEK LEVEL BONUS :). Może by oszukać optymalizator i sprawić by „myślał”, że jest u naszego klienta? Tylko jak to zrobić …

Na początek imitacja bazy klienta.

Załóżmy, że naszą bazą klienta jest baza AdventureWorks2012, którą można pobrać z witryny Codeplex, a problematyczna instrukcja SELECT wygląda następująco:

SELECT *
fROM AdventureWorks2012.Sales.SalesOrderDetail sod
JOIN AdventureWorks2012.[Sales].[SalesOrderHeader] soh
ON soh.SalesOrderID = sod.SalesOrderID
WHERE sod.CarrierTrackingNumber = '4911-403C-98'

A teraz nasza baza developerska

Jako bazę developerską użyjemy również bazy AdventureWorks2012. Jedyne co, to odzyskamy ją z kopii zapasowej pod inną nazwą (np. AdventureGeek) i usuniemy informacje z tabel , tak aby zasymulować brak danych na serwerze developerskim:

DELETE AdventureGeek.Sales.SalesOrderDetail

DELETE AdventureGeek.Sales.SalesOrderHeader

Gdy odpalimy nasz selekt na bazie developerskiej zobaczymy następujący plan zapytania:

SELECT *
fROM AdventureGeek.Sales.SalesOrderDetail sod
JOIN AdventureGeek.[Sales].[SalesOrderHeader] soh
ON soh.SalesOrderID = sod.SalesOrderID
WHERE sod.CarrierTrackingNumber = '4911-403C-98'

GEEK_LEVEL_BONUS_01

No tak … ale co w tym planie jest nie tak? Oczywiście mamy pewne ostrzeżenie optymailizatora, ale mimo wszystko plan zapytania na pierwszy rzut oka wygląda w miarę OK (no, może cześć z was zauważy niuans naprowadzający na rozwiązanie). Dlaczego tak się stało – po prostu mamy inne dane niż nasz klient (a dokładnie nie mamy danych). Diagnostyka, co może być nie tak przeprowadzona w ten sposób może okazać się dość kłopotliwa.

No dobrze, ale wspomniałem coś o oszukiwaniu optymalizatora 🙂

Na początek użyjemy instrukcji DBCC SHOW_STATISTICS, która służy do pokazania informacji na temat rozkładu danych w tabeli. Instrukcja ta zawiera nie do końca udokumentowaną opcję o nazwie STATS_STREAM:

DBCC SHOW_STATISTICS ('AdventureWorks2012.Sales.SalesOrderDetail', 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID')
WITH STATS_STREAM

GEEK_LEVEL_BONUS_02

Gdy zajrzymy w SQL Server Books Online możemy o tej opcji przeczytać następujące zdanie:

STATS_STREAM is Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Jeżeli namówimy osobę po stronie klienta by skopiowała i udostępniła nam jej wyniki, to może być tylko lepiej 🙂

W drugim kroku użyjemy instrukcji UPDATE STATISTICS (już na naszej bazie), przepisując wartości ROWCOUNT, PAGECOUNT I STATS_STREAM na zgodne z wypisanymi we wcześniejszym kroku:

UPDATE STATISTICS AdventureGeek.Sales.SalesOrderDetail PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
WITH ROWCOUNT = 121317, PAGECOUNT = 1237,
STATS_STREAM = -- tutaj wklejamy dane STATS_STREAM z poprzedniego kroku :)

No i oczywiście odpalmy nasze zapytanie w bazie AdventureGeek. Dla łatwiejszego porównania wyników zamieszczam obie instrukcje SELECT:

-- baza developerska
SELECT *
FROM AdventureGeek.Sales.SalesOrderDetail sod
JOIN AdventureGeek.[Sales].[SalesOrderHeader] soh
ON soh.SalesOrderID = sod.SalesOrderID
WHERE sod.CarrierTrackingNumber = '4911-403C-98'

-- baza klienta
SELECT *
FROM AdventureWorks2012.Sales.SalesOrderDetail sod
JOIN AdventureWorks2012.[Sales].[SalesOrderHeader] soh
ON soh.SalesOrderID = sod.SalesOrderID
WHERE sod.CarrierTrackingNumber = '4911-403C-98'

GEEK_LEVEL_BONUS_03

Jak widać podpowiedź optymalizatora w obu przypadkach jest taka sama – utworzyć indeks na kolumnie CarrierTrackingNumber. Oczywiście oba zaprezentowane plany są nieco odmienne. Na bazie developerskiej obie tabele nie są wypełnione wartościami, a my podmieniliśmy tylko jedną ze statystyk. Pozostałe kolumny nadal pozostają dla SQL Server-a puste.  

Mimo wszystko, na powyższym przykładzie widać, że nasza idea jak działa. Podmieniając kolejne statystyki dla obu tabel w bazie AdventureGeek moglibyśmy doprowadzić do wygenerowania identycznych planów.

Podsumowanie

Mam nadzieję, że moje rozwiązanie związane z podmianą statystyk wam się podoba. Jedynie co, to proszę – nie róbcie tego typu „sztuczek” na serwerach „u klienta”. Oczywiście chcielibyśmy, aby działały ładnie jak developerskie u nas, lecz nie jestem przekonany, czy ta sztuczka będzie działać w obie strony 🙂

PS. Pamiętajcie, że te rozwiązanie jest totalnie niewspierane !!!! (nawet przez mnie :))

Reklamy

Plany zapytań w cache-u a zmiana MAXDOP na poziomie serwera

SQL Server obsługując zapytania użytkowników potrafi sam zadecydować z ilu rdzeni ma w danym momencie skorzystać. Do kontroli maksymalnej ilości wątków służy nam opcja  o nazwie nazwie „max degree of parallelism„.

Celem wpisu nie jest jednak wyjaśnienie wam jak ta opcja działa, lecz pokazanie jaki jest poboczny skutek jej przestawienia 🙂

Co nas może nas zaskoczyć?

Na początek zalogujmy się do naszego serwera i sprawdźmy planów zapytań znajduje się w naszym CECHE-u:

SELECT COUNT(*) AS ilePlanow
FROM sys.dm_exec_cached_plans

Na  obrazku powyżej widać, że jest ich 49. Zmieńmy w takim razie konfigurację:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'max degree of parallelism', 4
RECONFIGURE

I odpytajmy się ponownie o ilość planów w CACHE-u:

SELECT COUNT(*) AS ilePlanow
FROM sys.dm_exec_cached_plans

Tym razem planów jest dwanaście 🙂

Czyżby SQL Server wyczyścił CACHE?

Wygląda na to, że TAK.  Czy jednak jesteśmy w stanie udowodnić ten fakt w niedający złudzeń sposób … poniżej przedstawiam wycinek logu, który może nas zainteresować:

 

Informacja tam zawarta jest dość jasna:

SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‚Object Plans’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

Możemy być pewni, że to zmiana MAXDOP na poziomie serwera wyczyściła nam zbuforowane plany zapytań, co może nieść negatywny skutek wydajnościowy przez kilka kolejnych minut działania serwera.

Podsumowanie

Wszelkie zmiany ustawień produkcyjnych serwerów SQL powinny być przez nas przemyślane. O ile opcja ‚max degree of parallelism’ jest powszechnie znana, to skutek poboczny jej przestawienia – już nie koniecznie.  Zanim zaczniemy ją zmieniać, przemyślmy czy może zrobić to w czasie, gdy nasz procesor nie jest zbyt obciążony pracą i wygenerowanie nowych planów nie będzie wpływać na pracę użytkowników końcowych.

Mity o SQL Server: Ilość rdzeni na serwerze a ilość plików tempdb

Na początku maja rozpocząłem serię postów, mówiącą na temat różnego rodzaju mitów i niedomówień związanych z działaniem SQL Server. Zgodnie z obietnicą tematem drugiego wpisu będzie następujący mit:

„Ilość plików w tempdb powinna być równa ilości rdzeni na jakich pracuje SQL” 

Mit ten jest dość popularny i jak zauważył mmoskit można na ten temat znaleźć oficjalne wzmianki w white paper-ach wydanych przez Microsoft, np:

http://www.microsoft.com/en-us/download/details.aspx?id=18473

„Make as many tempdb files as you have physical CPU, accounting for any affinity mask settings. Do not factor in hyperthreading or dual cored CPUs into the count ”

W dalszej części tego wpisu chciałbym pokazać, dlaczego nie warto stosować się do tego zalecenia „z automatu”., gdyż może ono prowadzić do utraty wydajności zainstalowanej instancji.

Środowisko testowe:

Zanim zaczniemy test, przedstawię nieco środowisko testowe. Konfiguracja sprzętowa wygląda następująco:

  1. SERWER HP DL 360 GS
  2. 2 x Intel Xeon  E5420 Quad Core 2,5 GHz
  3. 8 GB RAM
  4. 2 x HDD 146 GB,  10000rpm – zestawione w RAID 1
  5. 2 x  HDD 74GB 15000rpm – widoczne pojedynczo (oddzielnie)

Jeśli chodzi o zainstalowane oprogramowanie, to posłużyłem się:

  1. WINDOWS 2008R2 SP1 w Enterprise
  2. SQL Server 2012 Enterprise
  3. Przykładową bazą danych ContosoRetailDW

Całość została ustawiona przeze mnie w następujący sposób:

  1. System wraz z silnikiem SQL i bazami (za wyjątkiem tempdb) na dyskach 146GB 10000 obr/min
  2. Pliki danych tempdb na dysku 74 GB 15000 obr/min (zamontowanym jako dysk logiczny F)
  3. Plik logu tempdb na dysku 74 GB 15000 obr/min (zamontowanym jako dysk logiczny G)
  4. Affinity Mask Serwera SQL ustawiona na zero (używam wszystkich rdzeni)
  5. Min Server Memory = 1000MB, Max Server Memory = 6000 MB

Ustawienie zgodnie z zaleceniem MS

Wykonany test zacząłem od  ustawienia instancji zgodnie z przytoczonym white paper-em. Ponieważ mój serwer jest ośmiordzeniowy, to baza tempdb powinna mieć 8 plików danych o takiej samej zaalokowanej ilości miejsca na dysku:

Następnie stworzyłem proste zapytanie, które będzie obciążać bazę tempdb:

SELECT *
INTO #t
FROM ContosoRetailDW.dbo.[FactITMachine]

DROP TABLE #t

Test właściwy polegał na odpaleniu powyższego skryptu w 10 wątkach po 100 razy za pomocą programu SQLQueryStress. Wynik tej operacji  – nieco ponad minuta (próba była powtarzana 3 razy, do zestawienia brany był najniższy wynik):

Ustawienie niezgodne z zaleceniem MS

Drugą częścią testu było ustawienie bazy tempdb w taki sposób, aby miała tylko jeden plik danych:

Następnie odpaliłem stworzony wcześniej skrypt. Ponownie w 10 wątkach po 100 razy. Czas wykonania był tym razem około 30% krótszy, gdyż wynosił 45 sekund:

Dlaczego ustawienie zgodne z zaleceniem jest GORSZE ?!!!

Przyczyną zmniejszonej wydajności operacji I/O w bazie tempdb jest „fizyczność” dysku twardego, na którym posadowiłem pliki z danymi. Konfigurując bazę tempdb na oddzielnym dysku i tworząc w niej 8 równych plików z danymi sprawiliśmy, że SQL Server zaczął używać mechanizmu proporcjonalnego wypełnienia. Sprawdźmy w jaki sposób ten algorytm zadziałał w badanym przypadku:

1. Ustawmy baze tempdb tak, aby miała 8 plików z danymi

2. Odpalmy pierwszą część skryptu używając SQL Server Management Studio:

SELECT *
INTO #t
FROM ContosoRetailDW.dbo.FactITMachine

3. Sprawdzamy w jakich plikach są przechowywane dane naszej tabeli:

SELECT REPLACE(LEFT(sys.fn_PhysLocFormatter(%%physloc%%), 2), '(', '') AS FileID,
  COUNT(*) As NumRows
FROM #t
GROUP BY LEFT(sys.fn_PhysLocFormatter(%%physloc%%), 2)
ORDER BY FileID

Jak widać na powyższym zrzucie ekranu, SQL Server porozdzielał wiersze pomiędzy pliki. Algorytm proporcjonalnego wypełnienia sprawił, że głowica dysku twardego musiała zmieniać pozycję „skacząc” pomiędzy plikami sprawiając, że zapis na nich był wolniejszy.

Poniżej przedstawiam zrzuty ekranów z Resource Monitora, najpierw dla konfiguracji z 8 plikami:

A teraz dla jednego pliku z danymi:

Porównując oba obrazki widać, że różnica w szybkości zapisu jest znaczna (35MB vs 59MB na korzyść konfiguracji z 1 plikiem)

Podsumowanie

Stosowanie się do przytoczonego zalecenia MS odnośnie konfiguracji bazy tempdb może powodować spadek wydajności SQL Server. W erze wielordzeniowych procesorów, gdzie serwery bazodanowe mogą bez większego problemu posiadać po kilkadziesiąt rdzeni, utworzenie takiej samej ilości plików z danymi tempdb może okazać się dosyć kiepskim pomysłem. W związku z powyższym jestem zwolennikiem niestosowania się do tego zalecenia z automatu. Należy jednak w tym miejscu wspomnieć, że istnieją sytuacje w których zwiększenie ilość plików poprawia osiągi serwera. Jedną z takich sytuacji jest np. problem z dostępem do stron PFS w tempdb – co możliwe, że będzie tematem do dalszych rozważań i kolejnych wpisów 🙂

Edit: Dla niedowiarków 🙂

Aby nie być gołosłownym, sprawdziłem jak będzie wyglądać odczyt z takiej tabeli w zależności od ilości plików tempdb. Test jest dosyć prosty i wyglądał następująco:

1. Ponowna konfiguracja tempdb, tak aby miała 8 plików z danymi

2. Utworzenie tabeli testowej:

SELECT *
INTO tempdb.dbo.test
FROM ContosoRetailDW.dbo.FactITMachine

3. Cykliczne wykonanie następującego wsadu T-SQL:

-- Czyścimy RAM serwera
CHECKPOINT
DBCC DROPCLEANBUFFERS
-- odpalamy zapytanie (dane z dysku)
SELECT COUNT(*)
FROM tempdb.dbo.test
OPTION (QUERYTRACEON 8649)

Wynik tego zabiegu można zobaczyć poniżej (ponad 9 sekund):

Analogiczny test przeprowadzony dla tempdb z jednym plikiem (nieco ponad 7 sekund):

Podsumowanie dla niedowiarków 🙂

Niezależnie czy piszemy, czy odczytujemy dane z bazy tempdb ustawionej zgodnie z zaleceniem MS, to wydajność serwera jest niższa niż w przypadku posiadania tylko 1 pliku tempdb przy badanej konfiguracji dyskowej.

Resource Governor a plany wykonania zapytań

Ostatnio zająłem się nieco dokładniej Resource Governorem w SQL Server i chciałbym się z wami podzielić pewnym spostrzeżeniem na temat zrównoleglonych planów zapytań.

Krótkie wprowadzenie

Zarządca zasobów w SQL Server został wprowadzony wraz z ukazaniem się SQL Server 2008. Jest to narzędzie pozwalające konfigurację limitów związanych z pamięcią RAM i procesorem. Dzięki niemu możemy zapobiec sytuacjom, w których jedna aplikacja zużywa większość zasobów serwera i tym samym spowalnia inne programy łączące się z tą samą instancją.

Jak skorzystać z  Resource Governor można przeczytać na stronach polskiego technetu: http://technet.microsoft.com/pl-pl/library/akademia-sql—czesc-2-resource-governor.aspx

Konfiguracja środowiska

Jedną z możliwości jakie daje na, zarządca zasobów w SQL Server jest ustawienie maksymalnej ilości wątków, w których mogą być wykonane instrukcje przypisane do danej WORKLOAD GROUP.  Aby to zrobić należy ustawić opcję MAX_DOP na odpowiednią wartość. Ja podczas swoich testów ustawiłem ją na jeden, zabraniając tym samym zrównoleglenia.

Przykładowy skrypt tworzący WORKLOAD GROUP i przypisujący ją do domyślnej puli zasobów przedstawiam poniżej:

CREATE WORKLOAD GROUP GrupaBezRownoleglosci2
WITH (
 MAX_DOP = 1 --zapytania tylko jednowątkowe
)
USING "default"

Aby móc z niej skorzystać należy utworzyć odpowiednią funkcję klasyfikującą:

CREATE FUNCTION dbo.fsKlasyfikator()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
 DECLARE @grupa sysname 

 SET @grupa = CASE WHEN ORIGINAL_LOGIN() = 'TestA' THEN 'GrupaBezRownoleglosci'
   ELSE 'default'
 END
RETURN @grupa
END

A następnie podpiąć ją do Resource Governora:

ALTER RESOURCE GOVERNOR
WITH ( CLASSIFIER_FUNCTION = dbo.fsKlasyfikator )

ALTER RESOURCE GOVERNOR
RECONFIGURE

Dodatkowo na potrzeby testów utworzony przez mnie wcześniej login TestA powinien mieć uprawnienie VIEW ANY DEFINITION na poziomie serwera:

GRANT VIEW ANY DEFINITION
TO TestA

Sprawdzamy czy działa 🙂

Po ukończonej konfiguracji łączymy się z instancją za pomocą loginu „TestA” i sprawdzamy wynik poniższego zapytania:

SELECT s.session_id, g.name
FROM sys.dm_exec_sessions s
JOIN sys.resource_governor_workload_groups g
ON s.group_id = g.group_id
WHERE s.session_id = @@SPID

Jak widać aktualne połączenie jest przypisane do naszej grupy zasobów.

Sprawdźmy w takim razie plan wykonania następującej instrukcji dla przykładowej bazy AdventureWorks:

SELECT *
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
ORDER BY Style

Na powyższym obrazku widać kilka miejsc, w których występuje zrównoleglenie. Dziwna sprawa, zważywszy na fakt, że sesja dla której sprawdziliśmy przewidywalny plan została przypisana do WORKLOAD GROUP z MAX_DOP = 1.

Wykonajmy zapytanie zaznaczając opcję „Include Actual Execution Plan”:

SELECT *
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
ORDER BY Style

Niestety wygląda podobnie. Operatory zrównoleglenia nadal wystepują.

Co się w takim razie stało, czyżby Resource Governor nie działał?

Na szczęście nie jest aż tak źle. Ogólnie rzecz ujmując Resource Governor zrobił swoje zadanie i dział wyśmienicie. To wyświetlany plan zapytania stwarza błędne wrażenie zrównoleglenia.  Aby się o tym przekonać należy kliknąć prawy przyciskiem na znacznik „Parallelism” przedstawionego wcześniej planu, co spowoduje wyświetlenie okienka z właściwościami tego operatora.

Na obrazku powyżej nie znajdziemy wartości związanych z wykonaniem, takich jak np. „Actual Number of Rows”. Oznacza to, że faktyczne operator ten nie został użyty. W przypadku gdybyśmy odpalili zapytanie dla WORKLOAD GROUP pozwalającej na zrównoleglenie, to właściwości tego operatora wyglądałyby następująco:

Podsumowanie

Resource Governor jest całkiem fajnym narzędziem pozwalającym na dostosowanie zarządzanej instancji do naszych potrzeb. Analizując plany zapytań należy jednak wziąć poprawkę na to, że wykonanie instrukcji może zostać zmienione i nie wszystkie wyświetlone operatory zostaną wykonane. Taka sytuacja ma miejsce, gdy np. zabronimy zrównoleglenia wykonania dla wybranej grupy obciążeniowej.

Ilość pików VLF a kopia logu transakcyjnego w SQL Server

Mój poprzedni wpis na temat plików VLF spowodował pewną dyskusję na temat wydajności przywracania kopii dziennika transakcyjnego a wielkości jego wirtualnych plików. Ponieważ czas wykonania restore logu zależy od jego konfiguracji obiecałem opisać ten problem 🙂

Dla tych z was, którzy są zainteresowani tematem polecam kliknięcie w tag VLF mojego blogu, gdzie można odnaleźć nieco (moim zdaniem) interesujących informacji związanych z wirtualnymi plikami dziennika 🙂

Wielkość plików VLF a zajętość logu transakcyjnego

Pierwszą sprawą na która należy zwrócić uwagę jest fakt, że im mniejsze są nasze plik VLF, tym więcej miejsca potrzebuje SQL Server na przechowywanie dziennika transakcji. Jest to związane z wewnętrzną budową logu oraz tym, że wydzielenie jej logicznej części zabiera nieco miejsca na dysku.

Jako przykład przeanalizujmy następującą sytuację:

Mamy do dyspozycji dwie bazy danych. Obie mają ten sam rozmiar pliku bazy (2500MB) oraz dziennika transakcji (6000MB). Pierwsza z nich, o nazwie TestA ma 24000 VLF-ów, natomiast druga – TestB – jedynie 16. Dla obu wyczyściłem log transakcyjny, tak aby nie zawierał zbędnych wpisów. Dla pewności możemy sprawdzić zajętość logów dla obu baz:

DBCC SQLPERF('LOGSPACE')

Na zrzucie ekranu przedstawionym powyżej można zaobserwować dość interesującą sytuację. Zajętość logu transakcyjnego dla bazy TestA wynosi 12,5%, a wiec dość sporo jak na fakt, że przed chwilą go wyczyściłem poprzez utworzenie kopii zapasowej (dodatkowo dodam, że w bazie nie ma żadnej otwartej transakcji).  Aby zbadać te dziwne zjawisko sprawdźmy aktualny minimalny i maksymalny numer transakcji dla tej bazy:

SELECT MIN([Current LSN]) AS MinLSN, MAX([Current LSN]) AS MaxLSN
FROM fn_dblog(NULL, NULL)

Na obrazku powyżej można zaobserwwać, że aktualnie log transakcyjny zawiera niewiele wpisów, co więcej wszystkie znajdują się w pliku VLF o numerze sekwencyjnym 57612 (E10C w systemie szesnastkowym). Dla pewności użyjmy jeszcze instrukcji DBCC LOGINFO:

DBCC LOGINFO('testA')

Obrazek powyżej potwierdza, że jedynym aktywnym VLF-em w tej bazie jest plik o numerze 57612 (jako jedyny ma status różny od zera). Zajętość logu, którą pokazała instrukcja DCBB SQLPERF(‚LOGSPACE’) wynosząca 12,5% nie wynika z wypełnienia dziennika transakcji przez aktywne wpisy

Sprawdźmy w ile zajmie kopia logu obu baz

Na początek wykonajmy pełną kopię zapasową:

BACKUP DATABASE testA
TO DISK = 'testA_FULL_20120304A.bak'
WITH CHECKSUM, FORMAT

BACKUP DATABASE testB
TO DISK = 'testB_FULL_20120304A.bak'
WITH CHECKSUM, FORMAT

Następnie wykonajmy kopie dzienników transakcji:

BACKUP LOG testA
TO DISK = 'testA_LOG_20120304A.bak'
WITH FORMAT

BACKUP LOG testB
TO DISK = 'testB_LOG_20120304A.bak'
WITH FORMAT

Jeżeli porównamy wielkość stworzonych w ten sposób kopii zauważymy dość sporą różnicę:

Plik kopii logu dla bazy testA zajmuje około 24MB, a wiec dosyć sporo jak na dziennik zawierający jeden aktywny VLF o wielkości 1/4 MB 🙂 Dla porównania zaznaczyłem także kopię dziennika transakcji bazy testB, która zajęła na dysku 97KB.

Sprawdźmy czasy odtworzenia 

Oczywiste jest, że wielkość kopii dziennika transakcji będzie miała wpływ na czas przywrócenia bazy. Chciałbym jednak pokazać jak duże znaczenie ma fakt, ile plików VLF zawierała kopia logu:

SET STATISTICS TIME ON
-- odzyskanie bazy z 24 000 VLF-ów
RESTORE DATABASE TestA_new
FROM DISK = 'testA_LOG_20120304A.bak'
WITH NORECOVERY

Na powyższym obrazku widać, że czas odzyskania dziennika transakcji nie zawierającego prawie żadnych żadnych wpisów związanych z operacjami na bazie wyniósł nieco ponad 128 sekund. Co więcej rozmiar kopii z której odzyskiwaliśmy tę bazę nie jest na tyle duży, aby czekać ponad 2 minuty. Nawet biorąc pod uwagę fakt, że testy były robione na moim komputerze domowym, to wydajność dzisiejszych dysków twardych nie jest aż tak zatrważająca…

Dla porównania przedstawiam wynik dla drugiej z baz:

Czas trwania operacji restore zabrał co najwyżej 0,5 sekundy.

Podsumowanie

Ilość plików VLF ma znaczenie podczas odtwarzania kopii zapasowych logu transakcyjnego. W przypadku gdy log transakcyjny jest podzielony na dużą ilości wirtualnych plików, to rozmiar kopii zapasowej zwiększa się, co ma wpływ na jej czas tworzenia. Odtwarzanie dziennika z takiego backupu również ulega wydłużeniu, przy czym wydłużenie to nie jest adekwatne do rozmiaru samej kopii (jest znacząco większe).  Dla przypomnienia dodam, że w testowanym przeze mnie przypadku restore „prawie pustej” kopii z jednym aktywnym VLF-em zajął ponad 2 minuty 🙂