Akademia SQL - Część 14: Studio monitoringu

Autor: Damian Widera

Opublikowano: 5 lutego 2009

Zawartość strony

	Studio monitoringu - informacje podstawowe
	Studio monitoringu - laboratorium
	Studio monitoringu – referencje

Studio monitoringu - informacje podstawowe

Serwer SQL 2008 udostępnia administratorom studio monitoringu (ang. Performance Studio), przy pomocą którego można analizować dane diagnostyczne zebrane z wielu źródeł. Dane mogą pochodzić nie tylko z serwera SQL, ale także z systemu operacyjnego oraz wszystkich innych źródeł, do których dostęp można uzyskać z poziomu serwera SQL.

Podstawowym komponentem studia monitoringu jest kolektor danych (ang. data collector), który zostaje zainstalowany wraz z serwerem SQL. Każda instancja serwera SQL posiada swój kolektor danych, jest to aplikacja dcexec.exe, którą można odnaleźć w katalogu, do którego została zainstalowana instancja serwera SQL 2008 (w katalogu C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn dla domyślnej instancji przy zachowaniu domyślnych ustawień podczas instalacji serwera SQL 2008).

Kolektor danych zbiera i zapisuje informacje w zależności od konfiguracji usługi Agent, tzn. informacja może być zbierana cyklicznie lub na żądanie. Jak widać na rysunku 1 kolektor danych może pobierać dane z wielu kolekcji danych (na rysunku pokazano kolekcje SQL Server oraz Operating System). Jak wspomniano, dane mogą pochodzić z wyników zapytań napisanych w języku T-SQL, mogą być to również informacje uzyskane z monitora wydajności lub innych aplikcji. Kolekcje danych składają się z elementów kolekcji (ang. collection item), które są zbierane za pomocą kolektora określonego typu (ang. collector type). Typ kolektora określa źródło danych przechowywanych w kolekcji (zapytanie T-SQL, liczniki monitora wydajności).

Dane są magazynowane w bazie danych MDW (ang. Management Data Warehouse). Informacja może zostać przesłana do bazy MDW natychmiast lub zostać zmagazynowana przez określony interwał czasu w komponencie Data Collector Cache, a następnie przesłana do bazy MDW.

Baza danych MDW zawiera trzy schematy:

1. Schemat core, który zawiera obiekty wykorzystywane do organizacji i klasyfikacji zbieranych danych.
2. Schemat snapshots, zawierający obiekty niezbędne do przechowywania i utrzymywania danych zbieranych do wbudowanych kolekcji.
3. Schemat custom_snapshots zawiera obiekty niezbędne do przechowywania danych zbieranych przy użyciu kolektorów stworzonych przez użytkownika. Powstaje on dopiero wtedy, gdy administrator utworzy własną kolekcję danych.

Baza danych MDW posiada 3 predefiniowane role, które pozwalają na zarządzanie uprawnieniami:

1. Rola mdw_admin posiada prawa odczytu, zapisu, wprowadzania zmian i usuwania danych w bazie MDW. Członkowie tej roli mogą zmieniać schemat bazy oraz uruchamiać zadania mające na celu archiwizację lub usuwanie danych.
2. Rola mdw_writer posiada uprawnienia do zapisu danych do bazy MDW. Członkiem tej roli musi być każdy kolektor zapisujący dane do bazy MDW.
3. Rola mdw_reader posiada prawo odczytu w bazie MDW. Członkowie tej roli mogą przeglądać zapisane do bazy MDW dane historyczne oraz przeprowadzać diagnostykę problemów.

Uwaga! Administrator musi bezpośrednio przypisać odpowiednie role użytkownikom bazy MDW.

Baza danych msdb, oprócz przechowywania zadań usługi Agent, zawiera także zestaw procedur składowanych do konfiguracji kolekcji danych. Serwer SQL 2008 oferuje trzy wbudowane kolekcje danych, które należy uruchomić, by móc za ich pomocą zbierać dane i analizować wydajność baz danych i instancji serwera SQL. Kolekcje te są automatycznie uruchamiane po skonfigurowaniu bazy MDW oraz katalogu bufora kolektora danych.

Kolekcja Disk Usage pozwala monitorować zmiany rozmiarów plików baz danych, zarówno plików danych, jak i plików dziennika transakcji oraz śledzenie ilości wolnego miejsca w tych plikach.

Kolekcja Query Statistics służy do zbierania danych dotyczących wykonywanych zapytań (polecenia T-SQL, czas wykonywania, częstotliwość wykonywania) oraz ich planów wykonań.

Kolekcja Server Activity pozwala monitorować aktywność serwera, problemy dotyczące oczekiwania na wykonanie zadań w serwerze SQL 2008, wykorzystanie zasobów serwera (pamięć, procesory, dyski twarde). Kolekcja ta zbiera dane z dwóch źródeł:

1. Zapytania T-SQL do następujących widoków dynamicznych: sys.dm_os_wait_stats, sys.dm_os_waiting_tasks, sys.dm_os_latch_stats, sys.dm_os_process_memory, sys.dm_os_sys_memory, sys.dm_os_sys_info, sys.dm_os_memory_nodes, sys.dm_os_memory_clerks, sys.dm_os_schedulers, sys.dm_io_virtual_file_stats.
2. Liczniki monitora wydajności (w tym liczniki z obiektów systemowych takich jak: Memory, Process, Processor, LogicalDisk, System, Network Interface oraz z obiektów dostarczanych przez serwer SQL: Databases, General Statistics, SQL Statistics, Plan Cache).

Serwer SQL 2008 pozwala na wyświetlenie z poziomu konsoli SSMS standardowych raportów, z których każdy jest wizualizacją danych pochodzących z jednej z wbudowanych kolekcji danych. Konsola SSMS umożliwia także dokonanie pełnej konfiguracji kolekcji danych i ustawienie opcji bazy MDW.

Poniżej znajduje się kilka wskazówek, które warto wziąć pod uwagę podczas planowania konfiguracji studia monitoringu:

1. Do poprawnego działania procesu zbierania danych muszą działać usługi Agent i SSIS,
2. Baza danych MDW powinna znajdować się na innym serwerze, co pozwoli uniknąć jej monitorowania przy okazji zbierania danych,
3. Szacuje się, że dzienny przyrost danych w bazie MDW wynosi ok. 300MB,
4. Zbieranie danych zwiększa obciążenie procesora o ok. 5%,
5. Powyższe szacunki zależą od odpowiedniego wyboru konfiguracji bazy MDW i sposobu monitorowania danych.

 Do początku strony

Studio monitoringu - laboratorium

Celem laboratorium jest skonfigurowanie, uruchomienie oraz rozpoczęcie pracy ze studiem monitoringu.

Zadanie 1 - konfiguracja bazy danych MDW

1. Uruchom SQL Server Management Studio (SSMS) i połącz się z serwerem, na którym masz zamiar skonfigurować bazę danych MDW. Baza ta będzie przechowywała dane zebrane w procesie monitorowania innych serwerów bazodanowych.

   

2. W eksploratorze obiektów przejdź do sekcji Management, zaznacz obiekt Data Collection i z menu kontekstowego wybierz opcję Configure Management Data Warehouse

   

3. Graficzny kreator przeprowadzi Cię przez proces konfiguracji bazy danych MDW. W pierwszym oknie zaznacz opcjię Create or upgrade a management data warehouse, która pozwala skonfigurować lub aktualizowac konfigurację bazy danych MDW.

   

4. W kolejnym oknie wybierz przycisk New, ponieważ w tej chwili baza danych MDW nie jest jeszcze skonfigurowana. Możesz użyć jakiejkolwiek istniejącej bazy danych, ale zaleca się, aby baza MDW była przeznaczona tylko do przechowywania danych dla studia monitoringu.

   

5. Skonfiguruj bazę danych MDW. Jeśli zachowasz domyślne ustawienia opcji bazy MDW, to baza ta będzie działała w trybie Simple. Domyślny rozmiar początkowy pliku danych to 100 MB (z opcją automatycznego powiększania o 50 MB), zaś domyślny rozmiar początkowy pliku dziennika transakcji to 10 MB (z opcją automatycznego powiększania o 10 MB). W zastosowaniu produkcyjnym nalezy kierowac się rekomendacjami firmy Microsoft dotyczącymi tworzenia i administrowania baz danych. Po ustawieniu wszytskich wymaganych opcji, aby utworzyć bazę danych, naciśnij przycisk OK. Uwaga. Jeżeli w tym momencie wyłączysz kreatora to baza danych MDW nie zostanie usunięta i będziesz musiał to zrobić ręcznie. Aby kontynuować w oknie kreatora przycisk Next.

   

6. W oknie kreatora Map Users and Logins możesz wskazać użytkowników, którzy bedą mogli korzystać z informacji zawartych w bazie danych MDW oraz przydzielić ich do okreslonych ról.

   

7. W ostatnim oknie kreatora naciśnij przycisk Finish, co spowoduje uruchomienie trzecha akcji mających na celu

   

8. W oknie eksplorator obiektów rozwiń sekcję Databases i upewnij się, że baza danych MDW została utworzona poprawnie. Sprawdź, że baza danych MDW posiada dwa schematy: core oraz snapshot i każdy z nich składa się z róznych obiektów. Trzeci schemat — custom_snapshot zostanie wygenerowany dopiero w sytuacji, kiedy wykonasz własna kolekcję zbierająca dane. Zalecamy wygenerowanie diagramu bazy danych MDW, co pozwoli Ci lepiej zorientować się w relacjach pomiedzy obiektami w bazie danych MDW.

Zadanie 2 - konfiguracja kolekcji dostępnych w studio monitoringu

1. Uruchom SQL Server Management Studio (SSMS) i połącz się z serwerem, który masz zamiar monitorować. W tym zadaniu będziemy monitorować aktywność serwera i bazy danych AdventureWorks.

2. W eksploratorze obiektów przejdź do sekcji Management, zaznacz obiekt Data Collection i z menu kontekstowego wybierz opcję Set up data collection

3. Nastepnym krokiem jest wskazanie, gdzie znajduje się baza danych MDW. Wprowadź odpowiednie informacje pozwalające na połaczenie się do tej bazy. Drugą informacją, którą należy wprpwadzić jest wskazanie katalogu, w którym będą przechowywane informacje przed przesłaniem ich do bazy danych MDW. Pozostaw pole Cache directory puste i naciśnij przycisk Next.

4. Kolejne okno kreatora wyświetli podsumowanie przeprowadzonych do tej pory operacji. Nacisnij przycisk Finish, aby zakończyć konfigurację studia monitoringu i uruchomić domyślne kolekcje zainstalowane w serwerze SQL.

5. W eksploratorze obiektów rozwiń sekcję Data Colelction i zwróć uwagę, że wszystkie trzy wbudowane kolekcje zostały uruchomione:

6. Zaznacz kolekcję Disk Usage i z menu kontekstowego wybierz opcję Properties
7. Okno właściwości kolekcji podzielone jest na trzy strony: General, Uploads, Description. Strona Description zawiera krótki opis kolekcji, w tym przypadku zostaniesz poinformowany, że kolekcja Disk Usage zbiera informacje o rozmiarze oraz wykorzystaniu miejsca przez plik danych oraz dziennika transakcji dla każdej bazy danych.

8. Strona Uploads zawiera informacje o sposobie przesyłania informacji do bazy danych MDW. W tym momencie nie możesz zmieniać ustawień znajdujących się na tej stronie:

9. Strona General zawiera wszystkie informacje konfiguracyje kolekcji Disk Usage.

10. W polu Data collection and upload możesz zdecydować o sposobie przekazywania danych uzyskanych w procesie monitorowania do bazy danych MDW. Domyślnie, informacja będzie przesłana do bazy MDW natychmiast po (tryb Non-cached). W rozwiązaniach produkcyjnych warto rozważyć drugą opcję, która pozwala przechować dane lokalnie i przesłać do bazy danych MDW np. raz dziennie:

11. Pola Collection Items oraz Input parameters są ze sobą wzjamenie powiązane. Zaznacz pozycję Disk Usage – Data Files.

12. Zaznacz i skopiuj dane z pola Input parameters do nowego okna zapytania i sprawdź, w jaki sposób kolekcja Disk usage będzie gromadziła dane o plikach danych. Zakomentuj pierwszą linię kodu, jak pokazano poniżej:

--disk_usage



DECLARE @dbsize bigint 

DECLARE @logsize bigint 

DECLARE @ftsize bigint 

DECLARE @reservedpages bigint 

DECLARE @pages bigint 

DECLARE @usedpages bigint



SELECT @dbsize = SUM(convert(bigint,case when type = 0 then size else 0 end)) 

      ,@logsize = SUM(convert(bigint,case when type = 1 then size else 0 end)) 

      ,@ftsize = SUM(convert(bigint,case when type = 4 then size else 0 end)) 

FROM sys.database_files



SELECT @reservedpages = SUM(a.total_pages) 

       ,@usedpages = SUM(a.used_pages) 

       ,@pages = SUM(CASE 

                        WHEN it.internal_type IN (202,204) THEN 0 

                        WHEN a.type != 1 THEN a.used_pages 

                        WHEN p.index_id < 2 THEN a.data_pages 

                        ELSE 0 

                     END) 

FROM sys.partitions p  

JOIN sys.allocation_units a ON p.partition_id = a.container_id 

LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id 



SELECT 

        @dbsize as 'dbsize',

        @logsize as 'logsize',

        @ftsize as 'ftsize',

        @reservedpages as 'reservedpages',

        @usedpages as 'usedpages',

        @pages as 'pages'

13. Jak widać, kolekcja Disk usage korzysta z widoków dynamicznych sys.database_files, sys.partitions, sys.allocation_units oraz sys.internal_tables do uzyskania potrzebnych informacji. Uruchom kod i sprawdź uzyskane wyniki. Ważne jest, że uruchomienie kodu w konsoli SSMS pozwoli na uzyskanie informacji z bazy danych, w kontekście której kod został uruchomiony (w tym przypadku w kontekście bazy danych master), natomiast kolekcja Disk usage pobierze informacje z wszystkich baz danych:

14. Wróc do konfiguracji kolekcji Disk usage i w polu Collection Items zaznacz pozycję Disk Usage – Data Files. W polu Input parameters zaznacz i skopiuj kod do okna zapytania w konsoli SSMS i sprawdź, w jaki sposób kolekcja Disk usage gromadzi dane odnoszące się do użycia miejsca w dzienniku transakcji:

--log_usage



-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @tran_log_space_usage table( 

        database_name sysname

,       log_size_mb float

,       log_space_used float

,       status int

); 

INSERT INTO @tran_log_space_usage 

EXEC('DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS');

 

SELECT 

    database_name,

    log_size_mb,

    log_space_used,

    status    

FROM @tran_log_space_usage

15. Jak widać tym razem w kolekcji Disk usage wykorzystana jest komenda DBCC SQLPERF (LOGSPACE). Uruchom kod i sprawdź uzyskane wyniki:

16. Dla każdej kolekcji możesz jesczcze określić przy użyciu jakiego konta będzie następowało zbieranie danych. Domyślną opcją jest konto usługi Agent. Możesz także określić jak długo dane będą przechowywane w bazie MDW. W przypadku kolekcji Disk usage jest to 730 dni, ale inne kolekcje posiadają znacznie krótsze sugerowane czasy przechowywania informacji diagnostycznej:

17. Połącz się z serwerem SQL, na którym znajduje się baza danych MDW. Odszukaj w niej tabele snapshot.disk_usageoraz snapshot.log_usage. Zauważ, że ich schemat zawiera wszystkie informacje , które uzyskiwane są z kolekcji Disk usage. Tabela snapshot.disk_usage posiada trzy dodatkowe kolumny: database_name, collection_time i snapshot_id, które są wymagane do identyfikacji bazy danych, z której pochodzą informacje oraz czasu ich wykonania.
18. Podobne kroki (od punktu nr 6 zadania 2) przeprowadź do pozostałych wbudowanych kolekcji.

19. Zwróć uwagę, że dla kolekcji Query statisticsnie można sprawdzić, w jaki sposób serwer SQL gromadzi dane:

20. Dla kolekcji Server activity jeden z jej elementów — Server Activity – Performance Counters gromadzi dane pochodzące z liczników systemowych:

21. Wykonaj modyfikacje danych w bazie danych AdventureWorks, co pozwoli Ci zebrać dane przez omówione trzy kolekcje. Możesz skorzystać z plików dołączonych do lekcji, które generują aktywność. Pamiętaj, o zmianie zawartości plików z rozszerzeniem cmd tak, aby można było połączyć się do Twojej bazy danych.

Zadanie 3 – praca z raportami dostępnymi w konsoli SSMS

1. W eksploratorze rozwiń sekcję Management i zaznacz sekcję Data Collection. Z menu kontekstowego wybierz opcję Reports a następnie Management Data Warehouse. Konsola SSMS dla każdej wbudowanej kolekcji oferuje raport, który pozwala wyświetlić i przeanalizować zebrane informacje diagnostyczne. Użytkownik może także wykonać swój raport i podłaczyć go w sekcji Custom Reports:

   

   Dla każdej bazy danych mozna odczytać początkowy i końcowy rozmiar pliku danych oraz dziennika transkacji praz średni wzrost plików liczony w MB/dzień.

2. Z listy dostępnych raportów wybierz raport Disk Usage Summary

   

3. Kliknij w bazę danych AdventureWorks aby zobaczyć bardziej szczegółowy raport o plikach tej bazy danych

   

4. Używając panelu nawigacyjnego wróć do strony głównej raportu Disk Usage Summary

   

5. Kliknij na linii trendu dla pliku danych w bazie danych msdb, co spowoduje wyswietlenie szczegółowego raportu:

   

   Raport składa się z części graficznej i tekstowej, w której prezentowane są informacje dotyczące czasu wykonania pomiaru, wielkości pliku danych

6. Wróć do strony głównej raportu Disk Usage Summary i kliknij na linii trendu dla pliku dziennika transakcji dla bazy danych AdventureWorks, co spowoduje wyświetlenie szczegółowego raportu na jego temat:

   

   Raport prezentuje informacje na temat czasu wykonania pomiaru, wielkości pliku dziennika transakcji oraz procentowego jego zajęcia.

7. W eksploratorze obiektów zaznacz sekcję Data Collection i z menu kontekstowego wybierz raport Query Statistics History.

   

   Raport wyświetla 10 zapytań, które z punktu widzenia serwera są najbardziej uciążliwe.

   W górnej częsci raportu znajduje się pasek nawigacyjny, który służy do określania czasowych ram raportu:
   a) białe pola w pasku nawigacyjnym oznaczaja, że brak jest w danym okresie czasu informacji,
   b) sekcja w kolorze szarym oznacza, że w wybranym okresie czasu istnieją dane diagnostyczne, ale nie są one zaprezentowane na wykresach poniżej,
   c) sekcja w kolorze niebieskim (lub niebiesko-zielonym) oznacza, że dany fragment czasu został szczegółowo zaprezentowany na wykresach poniżej.
   Zmiany ram czasowych można dokonać klikając na symbol lupy lub przyciski nawigacyjne poniżej skali czasu.

8. Raport umożliwia analizę zapytań pod różnym kątem, a domyślnie wyświetlane są informacje pod kątem zużycia zasobów procesora. Dane mogą być również analizowane w ujęciu czasu trwania zapytania, ilości fizycznych odczytów i logicznych zapisów danych.

   

9. Kliknij na zapytanie, które z punktu widzenia zuzycia zasobów procesora było najbardziej uciążliwe. Wyświetlony zostanie szczegółowy raport zawierający statystyki zapytania, wykres obrazujący zużycie zasobów procesora w czasie i przy uwzględnieniu planu zapytania, z jakiego korzystała kwerenda.

   

10. Kliknij na plan nr 1 i zwróć uwagę, że wyświetlany jesttekst zapytania, informacje statystyczne oraz istnieje możliwość sprawdzenia, jakiego typu oczekiwania (ang. waits) spowodowało to zapytanie (link View sampled waits for this query) oraz obejrzeć jego graficzny plan (link View grafical query execution plan)

    

11. W eksploratorze obiektów zaznacz sekcję Data Collection i z menu kontekstowego wybierz raport Server Activity History.

    

    Raport wyświetla szczegółowe informacje dotyczące serwera, na którym zainstalowana jest monitorowana baza danych. Administrator uzyska informacje o:
    a. zużyciu zasobów procesora zarówno przez serwer SQL, jak i przez cały system
    b. zużyciu pamięci przez serwer SQL i system
    c. operacjach I/O na dyskach
    d. użyciu sieci
    Oprócz tego prezentowane są dwa wykresy — słupkowy oraz liniowy, które prezentują pogrupowane w kategorie stany oczekiwania, które występowały w serwerze SQL.

12. Kliknij na wykresie Memory Usage na niebieska linię, która obrazuje zużycie pamięci przez serwer SQL. W nowym raporcie będziesz mógł szczegółowo przeanalizować zużycie pamięci przez wewnętrzne komponenty serwera:

    

13. Wróc do raportu Server Activity History i kliknij na wykresie słupkowym jedną z grup. Zaprezentowany zostanie raport ze stanów oczekiwań w serwerze SQL w danym, wybranych okresie czasu. Jedną z najistotniejszych informacji, którą można odczytac z tego raportu jest informacja o zakleszczeniach (ang. Lock)

    

14. Rozwiń sekcję Lock i sprawdź, jakiego rodzaju zakleszczenia zaostały zarejestrowane w wybranym przez Ciebie okresie czasu:

    

15. Kliknij na odnośniku Lock i będziesz mógł sprawdzić dokładniej 10 najdłużej trwających zakleszczeń. Z wykresu będziesz mógł odczytać, kiedy zakleszczenia miały miejsce oraz również dowiedzieć się, które sesje brały w tym procesie udział. W tym celu wybierz jeden procesów blokowania (kliknij na numer na w kolumnie Chain# lub na linię na wykresie):

    

16. Na poniższym raporcie możesz przeanalizować szczegóły na temat ilości zablokowanych sesji w trakcie wybranego okresu czasu:

    

17. Klikając w jeden z linków zawierających czas otrzymasz dokładną, szczegółową informację na temat sesji, które brały udział w blokowaniu:

    

 Do początku strony

Studio monitoringu – referencje

Dodatkowe informacje na temat studia monitoringu można znaleźć w internecie:

[1] Witryna SQL Server 2008 Jumpstart

[2] TechNet Webcast: SQL Server 2008: New Performance Monitoring and Troubleshooting Using Management Studio (Level 300)

[3] SQL Server 2008 Manageability - whitepaper

[4] "Serwer SQL 2008. Administracja i programowanie”, Wydawnictwo Helion SA, grudzień 2008

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