Performance Studio w SQL 2008 (część 2)
|
Zawartość strony
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.
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.
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]
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
< 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
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.
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 |