Performance Studio w SQL 2008 (część 1)

Przeczytaj także: • Mechanizm śledzenia zmian w SQL Server 2008 • Mechanizm szyfrowania baz danych (TDE) w SQL Server 2008

Zawartość strony

	Wstęp
	Czym jest Performance Studio ?
	Pierwsza konfiguracja
	Przegląd kolekcji systemowych
	Budowanie własnych kolektorów
	Podsumowanie

Wstęp

Jednym z głównych zadań administratora systemu, w tym wypadku systemu bazodanowego, jest monitorowanie środowiska.

Powodów,dla których odbywa się monitoring środowiska jest wiele, wśród nich znajdują się m.in:

Na bazie informacji zebranych z wykorzystaniem w/w narzędzi administrator, może swobodnie opracowywać raporty z pracy serwera SQL. Jeśli do wspomnianych narzędzi dodany zostanie zestaw predefiniowanych raportów, korzystający m.in z DMV, znanych jako Performance Dashboard Reports (http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en ) można by zaryzykować stwierdzenie, że administrator posiada już wszystko co niezbędne do optymalnego monitorowania i raportowania pracy serwera.

SQL Server 2008 to wprowadzenie przez Microsoft nowej funkcjonalności znanej już pod nazwą Performance Studio. Podobnie jak wspomniane raporty, Performance Studio oparte zostało m.in o wprowadzone w wersji 2005 widoki DMV, z jedną olbrzymia zaletą – możliwością kolekcjonowania danych w dedykowanej w tym celu bazie danych.

Niniejszy artykuł ma zaprezentować możliwości nowej funkcjonalności, pokazać administratorom jak szybko i wydajnie gromadzić dane, a następnie wykorzystać je do budowania raportów z pracy serwera.

Do początku strony

Czym jest Performance Studio ?

Performance Studio to nowa funkcjonalność dostarczona jako element SQL Server 2008. Funkcjonalność, a raczej mechanizm został zintegorwany z uługą SQL Server Agent, platformą SQL Server Integration Services (SSIS) oraz oczywiście główną usługą SQL Server - silnikiem bazy danych.

Idea pracy Performance Studio oparta została o komponent, jakim jest kolektor danych. Kolektor  nie wymaga odrębnej instalacji i istnieje możliwość jego wykorzystania zaraz po instalacji systemu SQL Server. Kolektor może działać w trybie ciągłym lub podlegać manualnej aktywacji przez administratora systemu. Wspomnianą zaletą wykorzystania kolektora jest możliwość przechowywania danych monitoringu w bazie danych zwanej Management Data Warehouse (MDW).

Przyglądając się dokładnie zasadom funkcjonowania mechanizmu, zauważyć można że  są one oparte na tworzeniu kolekcji danych (collection set), w których zawarte zostały elementy odpowiadające zapytaniom T-SQL pobierającym informacje o obiektach baz danych m.in. poprzez odpytanie odpowiednich widoków dynamicznych, bądź to pobierając dane bezpośrednio z elementów systemowych.

Pobieranie danych odbywać się może cyklicznie, lub na żądanie, a ich zebranie realizowane jest poprzez wykonanie zadania (job) z poziomu usługi SQL Server Agenta. Za transfery danych do tymczasowych magazynów (katalogu tymczasowego) danych oraz do bazy MDW odpowiada silnik SSIS.

Niestety wykorzystanie opisywanej funkcjonalności zostaje ograniczone jedynie do baz danych o poziomie zgodności z SQL Server 2008, toteż wykorzystanie Performance Studio dla baz z wcześniejszych wersji SQL Server nie jest możliwe .

Z punktu widzenia wydajnościowego, uaktywnienie na serwerze kolektora danych wzmaga wykorzystanie procesora na poziomie 3-4%, co można swobodnie uznać, za niewielki wzrost obciążenia dla serwera przy relatywnie bardzo dużej ilości korzyści płynących z zastosowania omawianej funkcjonalności

Zapisy dotyczące wykorzystania Performance Studio w tym logi, pakiety SSIS oraz zadania SQL Server Agent przechowywane są bezpośrednio w bazie MSDB.

Krótko podsumowując opisywany mechanizm:

W drugiej części artykułu zaprezentuję praktyczne elementy dot. Performance Studio.

Do początku strony

Pierwsza konfiguracja

Przystępując do konfiguracji Performance Studio ( Data Collection) po raz pierwszy należy dokonać ustawienia dwóch elementów:

Rysunek 1 -Data Collection w SSMS.

Krok 2 – Rozpoczęcie konfiguracji (wybierając opcję Configure Management Data Warehouse)

Rysunek 2

Krok 3 – Okno powitalne kreatora, który prowadzi przez cały proces konfiguracji

Rysunek 3

Krok 4 – Wybór opcji dot. rozpoczęcia nowej konfiguracji, bądź aktualizacji istniejącego środowiska przechowywania danych

Rysunek 4 Pierwsza konfiguracja wiąże się z koniecznością wskazania serwera i bazy w której przechowywane będą  wyniki monitorowania.

Krok 5 – Wybór serwera, na którym będzie przechowywana baza – warto w tym miejscu zauważyć, że lokalizacją docelową dla przechowywanych danych może być inny serwer niż ten, z którego będą dane zbierane.

Rysunek 5 Wskazanie serwera na którym utrzymywan będzie baza MDW – w tym wypadku jest to serwer Evaluation

Krok 6 – W przypadku, gdy implementowana jest funkcjonalność Data Collection po raz pierwszy należy dokonać utworzenia bazy.

Planując przestrzeń pod bazę przechowującą wyniki monitorowania serwera, warto przyjąć pewne założenia dotyczące przestrzeni dyskowej potrzebnej pod taką bazę. Opierając wiedzę na przeprowadzanych testach można stwierdzić, że średni przyrost wielkości takiej bazy może wahać się pomiędzy 250-500 MB dziennie (!!!), w zależności od ilości gromadzonych danych, aktywności kolektorów

Rysunek 6 Tworzenie bazy MDW, wraz z określeniem jej wielkości i dodatkowych parametrów

Rysunek 7

Krok 7 – Konfiguracja uprawnień dostępu do bazy MDW

W trakcie konfiguracji Performance Studio ( Data Collection ) utworzona została dedykowana pod omawianą funkcjonalność baza danych. We wspomnianej bazie zakładane są dodatkowo 3 role bazodanowe:

Członkowie tej roli mają jedynie prawo odczytywania danych z bazy, a celem działania użytkownika mającego uprawnienia wynikające z członkowstwa w tej roli jest analiza zebranych danych.

Dzięki tym rolom można szybko zarządzać uprawnieniami dostępu do zasobów bazy MDW, zwłaszcza gdy gromadzone w niej dane mają charakter poufny.

Rysunek 8 Przypianie uprawnień użytkownikowi  do bazy MDW ( W tym wypadku użytkownik SQL Server zostanie członkiem roli MDW Admin ).

Krok 8 – Podsumowanie konfiguracji i jej implementacja na serwerze

Rysunek 9 Podsumowanie konfiguracji MDW.

Rysunek 10 Implementacja konfiguracji.

Krok 9 – Konfiguracja i uaktywnienie gromadzenia danych

Po poprawnym zakończeniu konfiguracji bazy pod gromadzenie danych, kolejnym krokiem jest konfiguracji kolektora i jego aktywacja. Proces konfiguracji rozpoczyna się od ponownego wywołania okna konfiguratora z poziomu Data Collection w SSMS.

Rysunek 11 Druga faza konfiguracja - aktywacja kolekcji danych.

Krok 11 – Wskazanie serwera i bazy, na którym utworzono bazę dedykowaną pod monitorowanie ( baza MDW)

Rysunek 12 - Serwer Evalution to serwer, na którym w poprzednich krokach osadzona została baza MDW

Rysunek 13

Krok 12 -  Wskazanie katalogu tymczasowego, do którego wykonywane będą zrzuty monitoringu, miejsce z którego pakiety SSIS będą wykonywały wczytanie danych do bazy MDW.

Rysunek 14

Rysunek 15

Rysunek 16

Rysunek 17 - Podusmowanie konfiguracji Data Collection.

Rysunek 18 - Podusmowanie konfiguracji Data Collection.

Rysunek 19 - Aktywna kolekcja danych - dostępne kolekcje systemowe

Do początku strony

Przegląd kolekcji systemowych

Domyślna konfiguracja Performane Studio daje możliwość natychmiastowego rozpoczęcia gromadzenia danych z wykorzystaniem trzech predefiniowanych kolekcji systemowych:

Pierwszy z zaprezentowanych Disk Usage Collection Set odpowiedzialny jest za pobieranie       (domyślnie co 6 godzin) danych dotyczących przestrzeni wykorzystywanej przez  plik danych i logu transakcyjnego dla każdej z baz utrzymywanej na serwerze. Informacje zebrane z wykorzystaniem tej kolekcji przechowywane są w bazie domyślnie przez 730 dni, co daje możliwość bardzo dokładnie zbadać trend dotyczący przyrostu wielkości poszczególnych baz danych. W dowolnym momencie, możliwe jest wygenerowanie raportu prezentującego w bardzo przejrzysty sposób wyniki działającej kolekcji.

Aby wygenerować raport dla kolekcji Disk Usage Summary należy na poziomie Management w SSMS wskazać obiekt Data Collection po wyświetleniu menu (po kliknięciu prawym przyciskiem) wybrać Reports - > Management Data Warehouse -> Disk Usage Report

Rysunek 20 - Generowanie Raportu podsumowującego dla danych zebranych z wykorzystaniem Disk Usage Collection Set

Rysunek 21 - Przykładowy raport wykorzystania przestrzeni dyskowej przez bazy

Query Activity Collection Set pobiera dane dotyczące aktywności zapytań realizowanych na serwerze. Dokładnie co 15 minut wczytywane są do bazy informacje o zapytaniach i przechowywane w bazie przez 14 dni. Kolekcja Query activity do pobrania informacji, aktywnych sesji wykorzystuje widok DMV(dm_exec_query_stats) i odpytując go co 10 sekund pobiera informacje o 3 najbardziej „intersujących” zapytaniach (o „atrakcyjności” zapytania decydują m.in czas wykorzystania CPU, czas wykonywania, Total I/O, Physical Reads or Logical Writes) oraz zapytaniach, dla których plan wykonania uległ zmianie.

Raport Query Activity generowany jest analogicznie jak raport wykorzystania przestrzeni dyskowej, a jego wynik prezentuje Rysunek 22

Rysunek 22 – Raport na podstawie kolekcji Query Activity

Trzecia z systemowych kolekcji Server Activity Collection Set wydaje się gromadzić najciekawsze z punktu widzenia administratora dane, dzięki którym można podjąć trafne decyzje dotyczące optymalizacji pracy serwera. Dane z wykorzystaniem tej kolekcji wczytywane są do bazy również co 15 minut i przechowywane domyślnie przez 14 dni, natomiast próbkowanie ( pobieranie danych z serwera) odbywa w zależności od konkretnego licznika działającego w obrębie kolekcji co 10 lub 60 sekund. Wśród danych zgromadzonych przez kolekcję Server Activity znajdują się informacje dot.:

Podobnie jak dla poprzednich kolekcji dostępny jest raport podsumowujący zgromadzone dane.

Rysunek 23 - Raport wygenerowany na podstawie danych zgromadzonych przez Server Activity Collection Set.

Do początku strony

Budowanie własnych kolektorów

Do tej pory zaprezentowane możliwości Performance Studio mogą nie wydać się mocno interesujące, chociażby ze względu na bardzo ograniczoną ilość danych, które zaraz po konfiguracji można gromadzić. Jednakże jedną z olbrzymich zalet Performance Studio jest funkcjonalność pozwalająca na tworzenie własnych kolektorów danych.

Kolektor może zostać utworzony przez administrator na conajmniej dwa sposoby. Jeden ze sposobów to wykorzystanie TSQL do zbudowania kolektora od podstaw. Drugi to wstępne przygotowanie założeń funkcjonalnych dla działania kolektora i jego implementacja poprzez doraźne wykorzystanie SQL Server Profiler. Jak się okazuje, wykorzystanie Profiler pozwala bardzo szybko zbudować definicję kolektora i dostarczyć ją administratorowi w postaci skryptu.

W opcjach nowego Profilera pojawiła się nowa funkcjonalność – mowa tutaj o opcji Exportu->Script Trace Definition->For Trace Collection Set.

Dzięki tej funkcjonalności w dowolnym momencie można przygotować kod do wykonania na serwerze, który utworzy i skonfiguruje nowy kolektor. Zobaczmy na przykładzie jak wygląda utworzenia nowego kolektora, gromadzące w bazie informacje dot. zakleszczeń (deadlocks) na poziomie wybranej bazy danych - AdventureWorks

Rysunek 24

Rysunek 25

Rysunek 26

Skrypt definujący żądany kolektor

/*************************************************************/

-- SQL Trace collection set generated from SQL Server Profiler

-- Date: 11/26/2008  00:59:04 AM

/*************************************************************/

USE msdb

GO

BEGIN TRANSACTION

BEGIN TRY

-- Define collection set

-- ***

-- *** Replace 'SqlTrace Collection Set Name Here' in the following script

-- *** with the name you want to use for the collection set.

-- ***

DECLARE @collection_set_id int;

EXEC [dbo].[sp_syscollector_create_collection_set]

@name = N'SqlTrace Collection Set Name Here',

@schedule_name = N'CollectorSchedule_Every_15min',

@collection_mode = 0, -- cached mode needed for Trace collections

@logging_level = 0, -- minimum logging

@days_until_expiration = 5,

@description = N'Collection set generated by SQL Server Profiler',

@collection_set_id = @collection_set_id output;

SELECT @collection_set_id;

-- Define input and output varaibles for the collection item

DECLARE @trace_definition xml;

DECLARE @collection_item_id int;

-- Define the trace parameters as an XML variable

SELECT @trace_definition = convert(xml,

N'<ns:SqlTraceCollector xmlns:ns="DataCollectorType" use_default="0">

<Events>

<EventType name="Locks">

<Event id="25" name="Lock:Deadlock" columnslist="7,15,55,8,32,56,64,1,9,25,41,49,57,2,10,26,58,66,3,11,35,51,4,12,52,60,13,6,14,22" />

</EventType>

</Events>

<Filters>

<Filter columnid="35" columnname="DatabaseName" logical_operator="AND" comparison_operator="LIKE" value="AdventureWorks" />

</Filters>

</ns:SqlTraceCollector>

');

-- Retrieve the collector type GUID for the trace collector type

DECLARE @collector_type_GUID uniqueidentifier;

SELECT @collector_type_GUID = collector_type_uid FROM [dbo].[syscollector_collector_types] WHERE name = N'Generic SQL Trace Collector Type';

-- Create the trace collection item

-- ***

-- *** Replace 'SqlTrace Collection Item Name Here' in the following script

-- *** with the name you want to use for the collection item.

-- ***

EXEC [dbo].[sp_syscollector_create_collection_item]

@collection_set_id = @collection_set_id,

@collector_type_uid = @collector_type_GUID,

@name = N'SqlTrace Collection Item Name Here',

@frequency = 900, -- specified the frequency for checking to see if trace is still running

@parameters = @trace_definition,

@collection_item_id = @collection_item_id output;

SELECT @collection_item_id;

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION;

DECLARE @ErrorMessage nvarchar(4000);

DECLARE @ErrorSeverity int;

DECLARE @ErrorState int;

DECLARE @ErrorNumber int;

DECLARE @ErrorLine int;

DECLARE @ErrorProcedure nvarchar(200);

SELECT @ErrorLine = ERROR_LINE(),

@ErrorSeverity = ERROR_SEVERITY(),

@ErrorState = ERROR_STATE(),

@ErrorNumber = ERROR_NUMBER(),

@ErrorMessage = ERROR_MESSAGE(),

@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

RAISERROR (14684, @ErrorSeverity, 1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);

END CATCH;

GO

Rysunek 27 - Utworzona nowa kolekcja rejestrująca informacje o Dead Lockach w bazie AdventureWorks .

W wyniku pojawił się nowy kolektor, który może zostać wykorzystany do wykonywania zdefiniowanych zadań.

Do początku strony

Podsumowanie

Niniejszy artykuł miał na celu zaprezentowanie nowych możliwości monitorowania serwera SQL z wykorzystaniem mechanizmu, który pojawił się w wersji 2008 SQL Server.

Duża ilość możliwości w kwestii kolekcjonowania danych da administratorom  swobodę budowania rozwiązania monitoringu w pełni odpowiadającego ich oczekiwaniom.

Na podstawie zgromadzonych danych można z wykorzystaniem Report Builder przygotować sobie dość interesujące raporty.

O tym jak przygotować raporty na podstawie własnych kolekcji utworzonych w Performance Studio postaram się w najbliższym czasie napisać w drugim artykule dedykowanym tematyce wykorzystania danych zgromadzonych przez Performance Studio.

Bartłomiej Graczyk (MCT, MCITP: DBA, Developer, MCSA, MCSE) Uczestnik wielu projektów, w których pełni role konsultanta i architekta rozwiązań opartych o platformę SQL Server, ze szczególnym uwzględnieniem rozwiązań wysokiej dostępności dla systemów i metod ich monitorowania. Wśród zrealizowanych projektów znaleźć można autorskie rozwiązania programistyczne z wykorzystaniem SQL Server 2000/2005/2008, które tworzone były na potrzeby największych międzynarodowych i polskich firm m.in. Mars Polska, Polskapresse. Inicjator offlinowej grupy Microsoft w Łodzi. Od 2007 roku trener Microsoft. W chwili obecnej prowadzi własną firmę consultingowo-szkoleniową, współpracuje również z największymi autoryzowanymi ośrodkami szkoleniowymi Microsoft w Polsce m.in ABC Data Centrum Edukacyjne. W wolniejszych chwilach jako odskocznią od systemów bazodanowych, zajmuje sie systemami bezpieczeństwa informacji, w tym audytami na zgodność z systemami takimi jak ISO 27001:2005.

Do początku strony