Monthly Archives: Marzec 2012

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.

Reklamy

70-659 MCTS: Windows Server 2008 R2, Server Virtualization

Kolejny egzamin certyfikacyjny za mną 🙂 Tym razem podszedłem do egzaminu 70-659 MCTS: Windows Server 2008 R2, Server Virtualization, który ma na celu sprawdzenie wiedzy z zakresu platformy wirtualizacyjnej dostarczanej przez Microsoft.

Na pytanie „Skąd pomysł na ten egzamin?” mogę powiedzieć, że w zasadzie są dwa powody:

Pierwszym jest fakt, że wirtualizacja coraz mocniej „wchodzi do serwerowni”. Wraz z postępem technologicznym zaczynamy wirtualizować środowiska, które z uwagi na spore zapotrzebowanie na zasoby do niedawna stały na specjalnie wydzielonych maszynach. Do takich środowisk niewątpliwie możemy zaliczyć relacyjne bazy danych, a w szczególności SQL Server.

Drugi powód, bardziej przyziemny, to moje wystąpienie w ramach konkursu speaker idol na IT Camp Gdańsk. Ponieważ wybrany przeze mnie temat jest przekrojowy (“Wirtualizacja baz danych a Dynamic Memory”) to stwierdziłem, że warto usystematyzować swoją wiedzę na temat Hyper-V i technologii pokrewnych 🙂

TS: Windows Server 2008 R2, Server Virtualization

Nie ma to jak spotkania offline: IT Camp Gdańsk i SQL Day 2012

Są takie chwile w życiu każdego geek-a, gdy trzeba wstać od komputera, wyruszyć z domu (lub pracy) i porozmawiać z innymi ludźmi. Oczywiście takie nagłe wyjście „na zewnątrz” może być szokiem dla organizmu zatwardziałego zapaleńca komputerowego. Dla takich (najbardziej skrajnych) przypadków została wymyślone spotkania grup offline. Mają one na celu wywabić geek-ów z domu by … no właśnie … porozmawiać o komputerach :). Ja zapisałem się na dwie tego typu imprezy:

IT Camp Gdańsk: Usługi IT w Dynamicznym Centrum Danych

Spotkanie to odbędzie się 3 kwietnia 2012 w Qubus Hotel w Gdańsk. Jest ono prowadzone w ramach popularyzacji rozwiązań wirtualizacyjnych firmy Microsoft i stanowi jedną z czterech form aktywności w ramach akcji vGuru: Zostań guru wirtualizacji. Uczestnictwo w nim jest całkowicie bezpłatne, a forma ma mieć luźny charakter wymiany doświadczeń ze specjalistami rozwiązań chmury prywatnej. Sama agenda przedstawia się następująco:
  1. Planowanie infrastruktury Dynamicznego Centrum Danych
  2. Zarządzanie usługami IT w nowoczesnym Centrum Danych
  3. Automatyzacja i samoobsługa (Self-Service) w usługach IT
  4. Sesja wystąpień uczestników IT Camp

Namawiając was do uczestnictwa, mogę powiedzieć, że podczas tej imprezy nie będę jedynie biernym uczestnikiem. Podczas czwartej z sesji zostanie rozegrany konkurs Speaker Idol, w którym zamierzam wziąć udział. Tematem mojego wystąpienia będzie: „Wirtualizacja baz danych a Dynamic Memory”, serdecznie zapraszam 🙂

SQL Day 2012

Druga imprezą w której będę uczestniczył jest największy zlot SQL-owców w Polsce – SQL Day. Jest on zorganizowany w dniach 24-26 maj 2012 we Wrocławiu terenie Dolnośląskiej Szkoły Wyższej „Edukacja”, w centrum szkoleniowym Synergy Trainings. Podczas trzech dni odbywać się tam będą różnego rodzaju warsztaty techniczne oraz sesje związane z moim ulubionym produktem bazodanowym. Z uwagi na mnogość tematów nie będę przytaczał pełnej agendy, a podam jedynie tę część, której sam będę uczestniczył:

24.05.2012
W1: Advanced SQL Server 2008 Troubleshooting
25.05.2012
Deep Dive Technical Keynote
We Have Control – Controlling Resources in SQL Server
Monitorowanie SQL Server z wykorzystaniem Extended Events
Optimizing SQL Server Performance in a Virtual Environment
26.05.2012
SQL Server 2008 Database Internals
Build a monitoring system with Powershell Remoting
SQL Azure Federations – The what, why and how of database scalability in the cloud
Why are we Waiting…
Maximizing CPU, Storage and Memory utilization for higher performance and lower TCO

W tym roku impreza jest płatna, lecz na pewno warta uczestnictwa. Pierwszym argumentem za jest fakt, że będzie ona trwała aż trzy dni, drugim – osoby prowadzące, które są ekspertami w dziedzinie relacyjnych baz danych, ściągniętymi zarówno z naszego kraju jak i z zagranicy.

Podsumowanie

Nawet najbardziej zatwardziali maniacy komputerowi powinni raz na jakiś czas oderwać się od monitorów. Mam nadzieję, że dzisiejszym wpisem namówiłem „najtwardszych” z was, to tego aby przyzwyczajać organizm do „wyjścia na zewnątrz” 🙂

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 🙂