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

Opublikowano: 22 stycznia 2009
Autor: Bartłomiej Graczyk

Zawartość strony

	Wstęp
	Typy kolekcji
	Tworzenie nowego collection set ( nowej kolekcji )
	Utworzenie definicji dla kolekcji – określenie zakresu gromadzonych danych
	Generowanie raportów z wykorzystaniem utworzonych kolekcji
	Załączniki

Wstęp

W poprzedniej części artykułu obiecałem zaprezentować Perfomance Studio w praktycznym wykorzystaniu. W niniejszym artykule zawarte zostały przykłady tworzenia własnych kolekcji danych pozwalających administratorowi realizować stały monitoring określonych obszarów serwera. Dla ułatwienia procesu prezentacji jako cel przyjęte zostaje stworzenie kolekcji zbierającej informacje o przestrzeni dyskowej zajmowanej (przez bazy danych) i dostępnej na wybranych partycjach. Na podstawie zebranych danych opracowane zostaną raporty dot. planowania przestrzeni dyskowej pod bazy danych.

 Do początku strony

Typy kolekcji

Aby przystąpić do utworzenia własnej kolekcji warto zastanowić się jaki będzie jej typ nadrzędny. Wśród dostępnych typów kolekcji znajdują się:

• Generic T-SQL Query Collector Type
• Generic SQL Trace Collector Type
• Query Activity Collector Type
• Performance Counters Collector Type

Tworząc nową kolekcje, należy określić jej typ wskazując unikalny identyfikator – informacje dot. definicji powyższych typów kolekcji należy pobrać z bazy MSDB, z tabeli dbo.syscollector_collector_types_internal

W przypadku gromadzenia danych dot. przestrzeni dyskowej, można skorzystać zarówno z typu Performance Counters Collector Type ( dane pobierane będą poprzez liczniki systemowe ( wcześniej te same liczniki wykorzystywane były (i nadal są) przez systwmowe narzędzie performance monitor), jak również z  Generic T-SQL Query Collector Type, w którym to przypadku dane zbierane są poprzez zbudowanie odpowiednich zapytań T-SQL do odpowiednich tabel systemowych.

Warto w tym miejscu wspomnieć, że jednym z elementów opisujących typ kolekcji jest schemat XML, trwale określający strukturę takiej kolekcji, a tym samym zakres danych możliwych do gromadzenia z wykorzystaniem danego typu.

Rysunek 1 - Tabele przechowujące konfigurację Performance Studio - w tym tabela z informację o typach kolekcji oraz wylistowane typy wraz z identyfiaktorami.

 Do początku strony

Tworzenie nowego collection set ( nowej kolekcji )

Wybór typu kolekcji pozwoli podjąć pierwsze kroki do utworzenia obiektu jakim jest collection set ( kolekcja). W ramach definicji collection set znajdują się informacje  m.in. o nazwie kolekcji ( parametr @name), krótki opis informaujące o celu przeznaczenia tworzonego obiektu ( @description), interwale gromadzenia danych w ramach kolekcji. Poniższy fragment kodu rejestruje na poziomie instancji serwera nową kolekcję o nazwie „Disk Usage – Stats”, która będzie zbierała dane zgodnie z określenie harmonogramu o naziwe „CollectorSchedule_Every_6h”, a dane zgromadzone przez kolekcję przechowywane  będą przez okres 90 dni ( parametr @days\_until\_expiration), w ykonaniu poniższego kodu dwie zadeklarowane zmienne przyjmą wartoci jednoznacznie identyfikujące utworzoną kolekcję

use msdb;

      Declare @collection_set_id_1 int

      Declare @collection_set_uid_2

      uniqueidentifier

      EXEC [dbo].[sp_syscollector_create_collection_set]

      @name=N'Disk Usage - Stats', @collection_mode=1,

      @description=N'Collects data about disk and log usage for all databases',

      @target=N'',

      @logging_level=1,

      @days_until_expiration=90,

      @proxy_name=N'',

      @schedule_name=N'CollectorSchedule_Every_6h',

      @collection_set_id=@collection_set_id_1 OUTPUT,

      @collection_set_uid=@collection_set_uid_2 OUTPUT

      Select @collection_set_id_1,

      @collection_set_uid_2

Alternatywnie dla w/w  kolekcji utworzona zostanie również kolekcja druga o bardzo podobnych właściwościach z tym, ze zbierająca dane nieco częsciej, bo co pięć minut.

Obie kolekcje wykorzystane zostaną w kolejnym kroku do gromadzenia danych o przestrzeniach dyskowych z tym, że każda z nich złożona będzie z kolektorów innych typów

use msdb;

      Declare @collection_set_id_1 int

      Declare @collection_set_uid_2

      uniqueidentifier

      EXEC

      [dbo].[sp_syscollector_create_collection_set]

      @name=N'Logical Disk Size',

      @collection_mode=1,

      @description=N'Collects

      logical disk counters to help in capacity planning',

      @target=N'',

      @logging_level=0,

      @days_until_expiration=5,

      @proxy_name=N'',

      @schedule_name=N'CollectorSchedule_Every_5min',

      @collection_set_id=@collection_set_id_1

      OUTPUT,

      @collection_set_uid=@collection_set_uid_2

      OUTPUT

      Select @collection_set_id_1,

      @collection_set_uid_2

Rysunek 2 - Wynik wykonania skrytpu tworzącego kolekcje Disk Usage - Stats , nowa kolekcja otrzymała identyfikator 8

Rysunek 3 - Wynik dodania kolekcji Logical Disk Size, której przydzielono identyfiaktor 9

Jak już wspomniałem, wszystkie utworzone kolekcje, zapisywane są w bazie MSDB w tableach bezpośrednio dedykowanych dla Performance Studio, w tym wypadku definiacji kolekcji znajduje się w tabeli [dbo].[syscollector_collection_sets_internal]

 Do początku strony

Utworzenie definicji dla kolekcji – określenie zakresu gromadzonych danych

Utworzone w poprzednim kroku kolekcje, nie posiadają jeszcze zdefiniowanych zakresów danych, które będą gromadzone w ramach cyklicznych operacji Collect and Upload, wykonywanych przez Microsoft SQL Server Agenta. W tym kroku zostaną określone typy kolektorów, z których korzystać będą utworzone kolekcje, a tym samym metody, którymi dane będą pozyskiwane.

Uwaga!!!

Poniższy fragment kodu jest rozwinięciem ( kontynuacją pełnej definicji kolekcji  Logical Disk Size – musi byc wykonany razem z poprzednią częścią skrytpu, tak aby odpowiedni zmienne przyjęły wymagane wartości

Declare @collector_type_uid_3 uniqueidentifier

        

        -- W tym miejscu zmiennej  @collector_type_uid_3 przypisany

        zostaje identyfiaktor typu kolekcji  Performance Counters Collector Type, a tym

        samym określony został schemat XML zgodnie z którym w ramach definicji

        przekazane zostane informacje o obiektach do monitorowania

        

        Select @collector_type_uid_3 =

        collector_type_uid From [dbo].[syscollector_collector_types] Where name =

        N'Performance Counters Collector Type';

        Declare @collection_item_id_4 int

        EXEC

        [dbo].[sp_syscollector_create_collection_item]

        @name=N'Logical Disk Collection',

        @parameters=N'

        <ns:PerformanceCountersCollector

        xmlns:ns="DataCollectorType">

        <PerformanceCounters

        Objects="LogicalDisk" 

                                       Counters="% Free Space"

                                       Instances="*" />

                        <PerformanceCounters Objects="LogicalDisk"

                                       Counters="Free Megabytes"

                                       Instances="*" />

                        <PerformanceCounters

        Objects="$(INSTANCE):Databases"

                                       Counters="Data File(s) Size (KB)"

                                       Instances="_Total" />

                        <PerformanceCounters

        Objects="$(INSTANCE):Databases"

                                       Counters="Log File(s) Size (KB)"

                                       Instances="_Total" />

          </ns:PerformanceCountersCollector>',

        @collection_item_id=@collection_item_id_4

        OUTPUT,

        @frequency=5,

        @collection_set_id=@collection_set_id_1,

        @collector_type_uid=@collector_type_uid_3

        Select @collection_item_id_4

W ramach powyższego skrytpu do kolekcji Logical Disk Size  dodana została definicja, z w której wskazane zostały liczniki systemowe wykorzystane do pobierana  danych ( blok PerformanceCounters Object wskazuje obiekt , z którego będą pochodziły liczniki, Counters, to licznik, a Instances, to zakres zbierania danych (obszar).W prezentowanym przypadku wykorzystane zostały liczniki obiektu LogicalDisk (% Free Space, Free Megabytes) oraz liczniki obioektu"$(INSTANCE):Databases (Data File(s) Size (KB)", Log File(s) Size (KB)").  Proszę zauważyć, że do całości definicji wykorzystano schemat XML, opatrzony nazwą PerformanceCountersCollector.

Uwaga!!!

Poniższy fragment kodu jest rozwinięciem ( kontynuacją pełnej definicji kolekcji  Disk Usage Stats – musi byc wykonany razem z poprzednią częścią skrytpu, tak aby odpowiedni zmienne przyjęły wymagane wartości

Uzupełnieniem drugiej definicji kolekcji – „ Disk Usage – Stats” jest skrypt dopisujący do niej definicję pobierania danych z wykorzystaniem odpowiednich zapytań T-SQL.  Warto szczególną uwagę zwrócić na wartość bloku <Query></Query>, gdzie znalazła się treść zaoytania TSQL, zwracająca żądane informacje, dopełnienie stanowi blok <OutputTable></OutputTable>, z informacją o doeclowej tabeli przeznaczonej na gromadzone dane

Declare @collector_type_uid_3 uniqueidentifier

        Select @collector_type_uid_3 =

        collector_type_uid From [dbo].[syscollector_collector_types] Where name =

        N'Generic T-SQL Query Collector Type';

        Declare @collection_item_id_4 int

        EXEC

        [dbo].[sp_syscollector_create_collection_item] @name=N'Disk Usage - Data Files

        - Stats', @parameters=N'

        <ns:TSQLQueryCollector

        xmlns:ns="DataCollectorType">

        <Query>

        <Value>

        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

        &lt; 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''

        </Value>

        <OutputTable>disk_usage</OutputTable>

        </Query>

        <Databases UseSystemDatabases="true"

        UseUserDatabases="true" />

        </ns:TSQLQueryCollector>',

        @collection_item_id=@collection_item_id_4

        OUTPUT, @frequency=5, @collection_set_id=@collection_set_id_1, @collector_type_uid=@collector_type_uid_3

        Select @collection_item_id_4

        Declare @collector_type_uid_5

        uniqueidentifier

        Select @collector_type_uid_5 =

        collector_type_uid From [dbo].[syscollector_collector_types] Where name =

        N'Generic T-SQL Query Collector Type';

        Declare @collection_item_id_6 int

        EXEC

        [dbo].[sp_syscollector_create_collection_item] @name=N'Disk Usage - Log Files -

        Stats', @parameters=N'

        <ns:TSQLQueryCollector

        xmlns:ns="DataCollectorType">

        <Query>

        <Value>

        -- 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

        </Value>

        <OutputTable>log_usage</OutputTable>

        </Query>

        </ns:TSQLQueryCollector>',

        @collection_item_id=@collection_item_id_6 OUTPUT, @frequency=5,

        @collection_set_id=@collection_set_id_1,

        @collector_type_uid=@collector_type_uid_5

        Select @collection_item_id_6

Analizują powyższy skrypt można również zuważyć, że dodaje on dwie pozycje do jednej kolekcji – pierwsza kolekcjonuje informacje dot. przestrzeni plików danych, druga, logów transakcyjnych.

Rysunek 4 - Wynik tworzenia kolekcji wraz z pozycjamia widoczny zarówno w dziale Data Collection jak i tabelach systwmowych Perfomance Studio

Utworzone kolekcje są gotowe do wykorzystania, nalezy je jedynie uaktywnić co prezentuje kolejny rysunek –Rysunek nr 5

Rysunek 5 - Uaktywnianie kolekcji

 Do początku strony

Generowanie raportów z wykorzystaniem utworzonych kolekcji

Wyniki działania kolekcji Disk Usage -  Stats można obserwować w nowo utworzonych tabelach w bazie MDW – custom_snapshot.disk_usage oraz custom_snapshot.log_usage, natomiast zapisy kolekcji Logical Disk Size zostały skrupulatnie umieszczone w tabelach

Rysunek 6 - Zapisu wartości liczników zebranych przez utworzoną kolekcję

Rysunek 7 - Zwartość tabeli disk_usage, po dwóch uruchomieniach kolekcji

Rysunek 8 - Tabela log_usage, podobnie jak disk_usage, gromadzi dane ze wszystkich uruchomień kolekcji, dzięki czemu można wykorzystać ją do szczegółowych analiz

Sposób wykorzystania zgromadzonych danych pozostaje od tego momentu zupełnie dowolny, można sięgać do nich z wykorzystaniem pojedynczych zapytań z poziomu SSMS, przygotować i opublikować na witrynie raport z wykorzystaniem Reporting Services, bądź opracować własny system, w którym administrator będzie dokonywał interpretacji zgromadzonych informacji.

 Do początku strony

Załączniki

Zawartość pliku DiskLogicalSize.sql:

use msdb;

        Declare @collection_set_id_1 int

        Declare @collection_set_uid_2 uniqueidentifier

        EXEC [dbo].[sp_syscollector_create_collection_set]

        @name=N'Logical Disk Size',

        @collection_mode=1,

        @description=N'Collects logical disk counters to help in capacity planning',

        @target=N'',

        @logging_level=0,

        @days_until_expiration=5,

        @proxy_name=N'',

        @schedule_name=N'CollectorSchedule_Every_5min',

        @collection_set_id=@collection_set_id_1 OUTPUT,

        @collection_set_uid=@collection_set_uid_2 OUTPUT

        Select @collection_set_id_1, @collection_set_uid_2





        Declare @collector_type_uid_3 uniqueidentifier

        Select @collector_type_uid_3 = collector_type_uid From [dbo].[syscollector_collector_types] Where name = N'Performance Counters Collector Type';

        Declare @collection_item_id_4 int

        EXEC [dbo].[sp_syscollector_create_collection_item]

        @name=N'Logical Disk Collection',

        @parameters=N'

        <ns:PerformanceCountersCollector xmlns:ns="DataCollectorType">

          <PerformanceCounters Objects="LogicalDisk"

            Counters="% Free Space"

            Instances="*" />

          <PerformanceCounters Objects="LogicalDisk"

            Counters="Free Megabytes"

            Instances="*" />

          <PerformanceCounters Objects="$(INSTANCE):Databases"

            Counters="Data File(s) Size (KB)"

            Instances="_Total" />

          <PerformanceCounters Objects="$(INSTANCE):Databases"

            Counters="Log File(s) Size (KB)"

            Instances="_Total" />

        </ns:PerformanceCountersCollector>',

        @collection_item_id=@collection_item_id_4 OUTPUT,

        @frequency=5,

        @collection_set_id=@collection_set_id_1,

        @collector_type_uid=@collector_type_uid_3

        Select @collection_item_id_4

Zawartość pliku DiskUsage.sql:

use msdb;

        Declare @collection_set_id_1 int

        Declare @collection_set_uid_2 uniqueidentifier

        EXEC [dbo].[sp_syscollector_create_collection_set] @name=N'Disk Usage - Stats', 

        @collection_mode=1, @description=N'Collects data about disk and log usage for all databases', 

        @target=N'', @logging_level=1, @days_until_expiration=90, @proxy_name=N'', 

        @schedule_name=N'CollectorSchedule_Every_6h', @collection_set_id=@collection_set_id_1 OUTPUT, 

        @collection_set_uid=@collection_set_uid_2 OUTPUT

        Select @collection_set_id_1, @collection_set_uid_2



        Declare @collector_type_uid_3 uniqueidentifier

        Select @collector_type_uid_3 = collector_type_uid From [dbo].[syscollector_collector_types] 

        Where name = N'Generic T-SQL Query Collector Type';

        Declare @collection_item_id_4 int

        EXEC [dbo].[sp_syscollector_create_collection_item] @name=N'Disk Usage - Data Files - Stats', @parameters=N'

        <ns:TSQLQueryCollector xmlns:ns="DataCollectorType">

          <Query>

            <Value>

              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''

            </Value>

            <OutputTable>disk_usage</OutputTable>

          </Query>

          <Databases UseSystemDatabases="true" UseUserDatabases="true" />

        </ns:TSQLQueryCollector>', @collection_item_id=@collection_item_id_4 OUTPUT, @frequency=5, 

        @collection_set_id=@collection_set_id_1, @collector_type_uid=@collector_type_uid_3

        Select @collection_item_id_4





        Declare @collector_type_uid_5 uniqueidentifier

        Select @collector_type_uid_5 = collector_type_uid From [dbo].[syscollector_collector_types] 

        Where name = N'Generic T-SQL Query Collector Type';

        Declare @collection_item_id_6 int

        EXEC [dbo].[sp_syscollector_create_collection_item] @name=N'Disk Usage - Log Files - Stats', @parameters=N'

        <ns:TSQLQueryCollector xmlns:ns="DataCollectorType">

          <Query>

            <Value>

              -- 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

            </Value>

            <OutputTable>log_usage</OutputTable>

          </Query>

        </ns:TSQLQueryCollector>', @collection_item_id=@collection_item_id_6 OUTPUT, @frequency=5, 

        @collection_set_id=@collection_set_id_1, @collector_type_uid=@collector_type_uid_5

        Select @collection_item_id_6



        GO

Bartłomiej Graczyk, analityk biznesowy i konsultant technologiczny W ostatnich latach uczestnik wielu projektów, w których pełnił rolę projektanta, konsultanta technologicznego i analityka biznesowego w zakresie rozwiązań działających na platformie Microsoft SQL Server 2000/2005/2008, Sybase ASA/ASE, Oracle. Twórca autorskich rozwiązań i aplikacji bazodanowych m.in. dla Mars Polska, Polskapresse, Polskiej Telefonii Cyfrowej (Era), Rohe Polska, AGITO SA, Polbanku, PKP SA. Od 2007 roku Microsoft Certified Trainer, prowadził autoryzowane szkolenia Microsoft w ramach współpracy z największymi ośrodkami szkoleniowymi w Polsce, m.in. ABC Data Centrum Edukacyjne i Combidata. W ramach działalności trenerskiej brał również udział w projektach szkoleniowych dla sektora publicznego: Kancelarii Prezesa Rady Ministrów, Centralnego Biura Antykorupcyjnego, Agencji Bezpieczeństwa Wewnętrznego, GIODO. Inicjator offline’owej grupy Microsoft w Łodzi, autor artykułów na stronach Microsoft TechNet i w czasopismach branżowych. Posiada certyfikaty: MCT, MCITP: Database Administrator, Database Developer, Business Intelligence Developer, MCDBA, MCSE, MCSA, Microsoft Certified Business Management Solutions Specialist & Professional for Microsoft Dynamics CRM 4.0.

Do początku strony