Akademia SQL - Część 2: Resource Governor

Autor: Damian Widera

Opublikowano: 9 września 2008

Zawartość strony

	Resource Governor – informacje podstawowe
	Resource Governor – laboratorium
	Resource Governor – referencje

Resource Governor – informacje podstawowe

Funcjonalność Resource Governor pozwala administratorom baz danych określać limity zasobów (pamięci i czasu procesora) dla określonych akcji i działań (workloads), takich jak zapytania, zapytania ad-hoc, zadania administracyjne, raporty, itd. co powoduje możliwość zaplanowania odpowiednio wysokiej wydajności systemu dla końcowego użytkownika. Resource Governor pozwala także nałożyć priorytety na zasoby i łączyć je z określonym użytkownikiem czy grupami użytkowników.

Mając do dyspozycji takie narzędzie jak Resource Governor administrator baz danych jest w stanie odpowiednio zaplanować zadania, aby wykonywały się nie tylko o odpowiednim czasie, ale również wykorzystywały pamięć i czas procesora przydzielone przez tego administratora. Można przydzielić duże zasoby dla wykonania raportów oraz nadać im duży priorytet, dzięki czemu mając pewność, że ten proces wykona się w odpowiednio szybko. Z kolei pewne zadania administracyjne, takie jak wykonywanie kopii zapasowej bazy danych, mogą otrzymać niski priorytet i niedużą porcję zasobów nie powodując zmniejszenia wydajności systemu.

Administrator może określać takie parametry serwera, jak:

  •  maksymalny % zużycia procesora

  •  minimalny % zużycia procesora

  •  maksymalny % zużycia pamięci

  •  minimalny % zużycia pamięci

Praca z funkcjonalnością Resource Governor polega na określeniu tzw. pul zasobów (resource pools), w których określone są limity dolne i górne przydziału pamięci oraz procesora. Następnie należy zdefiniować grupy (workload groups), które zostają przydzielone do utworzonych wcześniej pul. Ostatnim krokiem jest utworzenie skalarnej funkcji klasyfikującej zapytania (czy każdą inną aktywność) do odpowiednich grup. Funkcja zwraca nazwę grupy na podstawie dowolnych kryteriów (powiązanych oczywiście z procesem biznesowym, wiedzą administratora baz danych, porą dnia, itd.)

 Do początku strony

Resource Governor – laboratorium

Celem ćwiczenia jest stworzenie zasad przydziału zasobów w zależności od nazwy użytkownika lub używanej aplikacji. Drugim zadaniem będzie skonfigurowanie monitora wydajności (PerformanceMonitor) tak, aby obserwował odpowiednie liczniki systemowe odpowiedzialne za funkcjonalność Resource Governor.

Zadanie 1

  1.  Uruchom SQL Server Management Studio (SSMS) i połącz się z serwerem EVALUATION używając uwierzytelnienia WINDOWS.

  2.  Przejdź do folderu Management i rozwiń folder Resource Governor. Zauważ, że domyślnie ta funkcjonalność jest wyłączona. SQL Server 2008 posiada wbudowane dwie systemowe pule zasobów nazywane defaultoraz internal, które nie mogą być modyfikowane. Służą one między innymi do obsługi takich procesów systemowych jak proces opóźnionego zapisu (lazy writer) czy proces śledzenia (trace).

  3.  W następnym kroku należy utworzyć i skonfigurować nową pulę zasobów. Można to wykonać na dwa sposoby: za pomocą SSMS lub używając skryptu T-SQL. Dla utworzenia nowej puli zasobów w SQL Server Management Studio należy zaznaczyć katalog Resource Pools oraz z menu kontekstowego wybrać opcję New resource pool. W oknie właściwości należy najpierw zaznaczyć, iż Resource Governor będzie włączony a następnie dodać pulę jak pokazano na rysunku poniżej:

  4.  Ten sam efekt można osiągnąć wykonując odpowiedni skrypt T-SQL. Tym razem zdefiniowana zostanie pula o nazwie LOW i parametrach, jak poniżej:

CREATE RESOURCE POOL LOW

WITH

(

    MIN_CPU_PERCENT=0,

    MAX_CPU_PERCENT=10,

    MIN_MEMORY_PERCENT=5

)

GO

  5.  Każda pula zasobów powinna mieć zdefiniowaną grupę. Dodawanie, modyfikacja oraz usuwanie grupy może być przeprowadzone na dwa sposoby – omówione już przy okazji pracy nad pulami zasobów. W pierwszym podejściu zdefiniujemy grupę za pomocą SSMS. W tym celu należy zaznaczyć katalog Workload Groups znajdujący się pod interesującą nas pulą zasobów i z menu kontekstowego wybrać opcję New workload group:

  6.  W oknie własności należy zdefiniować takie parametry jak ilość obsługiwanych żądań, czas procesora oraz % udostępnionej pamięci, pamiętając, iż wskazanie wartości 0 oznacza ‘wszystko’ bądź ‘automatycznie’ (w ramach zdefiniowanej puli).

  7.  Podobny efekt można osiągnąć za pomocą wykonania skryptu T-SQL, który definiuje dwie grupy w ramach puli LOW.

CREATE WORKLOAD GROUP Group1Low

WITH

(

    IMPORTANCE = High

)

USING LOW

GO



CREATE WORKLOAD GROUP Group2Low

WITH

(

IMPORTANCE = Low,

MAX_DOP=1,

GROUP_MAX_REQUESTS=2

)

USING LOW

GO

  8.  W następnym kroku należy zdefiniować funkcję klasyfikującą, pozwalającą przypisać akcje, zdarzenia, zapytania, które mają miejsce na serwerze do odpowiedniej grupy, a tym samym przydzielić im zasoby znajdujące się w puli, do której wybrana grupa należy. Funkcja klasyfikująca nie przyjmuje żadnych parametrów wejściowych i zwraca nazwę grupy, do której zostanie zakwalifikowane żądanie. W naszym przypadku funkcja klasyfikująca jest dosyć prosta – dla żądania pochodzącego z SSMS zostanie wybrana grupa Group1 (z puli HIGH); jeżeli żądanie zostanie wygenerowane przez użytkownika ReportsUser, to zostanie ono przypisane do grupy Group1Low z puli LOW itd. W rzeczywistości funkcja klasyfikująca będzie zapewne bardziej skomplikowana (chociaż rekomendacje mówią, iż powinna być prosta, ponieważ funkcja klasyfikująca będzie wywoływana dla każdego żądania na serwerze i proces klasyfikacji tegoż żądania może powodować pewne opóźnienia), ponieważ może zawierać informacje klasyfikacji żądań w zależności np. od pory dnia.

CREATE FUNCTION dbo.Classification()

    RETURNS sysname

WITH SCHEMABINDING

AS

BEGIN

    DECLARE @Group as sysname

    

    IF UPPER(APP_NAME()) LIKE '%MANAGEMENT STUDIO%'

        SET @Group = 'Group1'

    IF SUSER_SNAME() = 'ReportsUser'

        SET @Group = 'Group1Low'    

    IF SUSER_SNAME() = 'sa'

        SET @Group = 'Group2'

        

        RETURN @Group

END

GO

  9.  Ostatnim krokiem jest poinformowanie usługi Resource Governor, iż ma zastosować utworzoną właśnie funkcję klasyfikującą:

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.Classification)

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

  10.  Na zakończenie zadania wykonaj poniższe zapytania i sprawdź otrzymane wyniki.

• Pierwsze z zapytań wyświetla informacje o stanie pul, ich konfiguracji oraz dostarcza podstawowych statystyk

SELECT * FROM 

sys.dm_resource_governor_resource_pools

• Drugie zapytanie zwraca informacje o konfiguracji grup oraz ich powiązaniu do określonych pul zasobów

SELECT * FROM 

sys.dm_resource_governor_workload_groups

• Trzecie zapytanie pozwala otrzymac informacje o stanie usługi Resource Governor (włączona, zatrzymana) oraz o funkcji klasyfikującej

SELECT * FROM

sys.resource_governor_configuration

• Czwarte zapytanie informuje o aktualnym stanie konfiguracji usługi. Jest to o tyle istotne zapytanie, iż w polu is_reconfiguraction_pending administrator otrzymuje informacje, czy usługa wymaga rekonfiguracji lub nawet restartu usługi SQL Server. Konieczność zmiany konfiguracji może być spowodowana zmianą puli zasobów lub grupy (dodanie nowych, aktualizacja bądź usunięcie istniejących obiektów wchodzących w skład Resource Governor)

SELECT * FROM

sys.dm_resource_governor_configuration

Zadanie 2

  1.  Uruchom konsolę monitora wydajności (Performance Monitor)

  2.  Z paska narzędzi konsoli dodaj nowe liczniki, zawarte w obiekcie SQLServer:Resource Pool Stats

  3.  Wybierz licznik CPU usage %

  4.  Z listy instancji wybierz jedną bądź obydwie utworzone pule. Można również monitorować pule systemowe

  5.  Uruchom SSMS i połącz się do serwera EVALUATION używając loginu ReportsUser i uruchom skrypt znajdujący się na dysku: C:\SQLAdmin\ResGovernor\Queries.sql

  6.  Połącz się do serwera EVALUATION używając loginu sa i uruchom skrypt znajdujący się na dysku: C:\SQLAdmin\ResGovernor\Queries.sql

  7.  Połącz się do serwera EVALUATION używając uwierzytelniania Windows i uruchom skrypt znajdujący się na dysku: C:\SQLAdmin\ResGovernor\Queries.sql

  8.  Przy odpowiednio małej wartości Min Memory dla puli LOW może nawet przerwanie wykonania zapytania ze względu na brak możliwości alokacji wymaganych zasobów. Można w ramach tego zadania zmieniać konfigurację pul i grup i obserwować wpływ tych zmian na sposób wykonywania zapytań.

 Do początku strony

Resource Governor – referencje

Dodatkowe informacje na temat Resorce Governor można znaleźć w internecie:

[1] Resource Governor Related Dynamic Management Views (Transact-SQL) - Books Online

[2] How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL) – przykład zastosowania praktycznego

[3] Managing SQL Server Workload and Resources

[4] Co nowego w silniku bazodanowym SQL Server 2008 November CTP

Damian Widera, Project Manager & Team Lead (MCT, MCITP – DBA, MCSD.NET) Od 8 lat zajmuje się projektowaniem, tworzeniem i wdrażaniem aplikacji wykorzystujących platformę .NET, SQL Server oraz Oracle. Obecnie pracuje jako project manager dla LGBS Polska. Pracował także jako trener, programista, administrator baz danych, twórca domumentacji oraz analityk biznesowy. Aktywnie współpracuje z polskim oddziałem Microsoft publikując atykuły, webcasty oraz porady z zakresu SQL Server na stronach TechNet. Jest współautorem książki „Serwer SQL 2008. Administracja i programowanie”. Speaker na wielu konferencjach, m.in. Microsoft Heroes Happen Here, C2C, European PASS Conference, Microsoft Technology Summit, Energy Launch, TechED. Od 2004 r. posiada certyfikaty firmy Microsoft: MCT, MCITP–DBA oraz MCSD.NET. Jest współtwórcą oraz liderem jednej z najwiekszych grup pasjonatów SQL Server w Polsce – Śląskiej Regionalnej Grupy Microsoft (PLSSUG Katowice). Od listopada 2008 jest prezesem Polish SQL Server Users Group (PLSSUG) w Polsce. W styczniu 2009 nagrodzony tytułem MVP w kategorii SQL Server.

Do początku strony