SQL Server – Interaktywny performance dashboard dla ubogich

Zajawka

Wiele razy widziałem w polskich firmach, jak działy IT używały różnorakich aplikacji do interaktywnego monitorowania środowisk. Na wielkich monitorach administratorzy obserwują wskaźniki wydajnościowe, a zapalające się czerwone światełko jest zazwyczaj bodźcem do działania – pewnie coś się dzieje złego. Powstaje pytanie, czy administrator SQL Servera może samemu sprokurować sobie takie narzędzie, które będzie wyświetlało wskaźniki wydajnościowe i interaktywnie informowało o potencjalnych problemach? Spróbowałem i myślę, że to możliwe. Ba, uważam, że jest do zrobienia nawet na Express Edition. Wszystkie poniższe przemyślenia dotyczą wersji 2008 R2, ale wydaje mi się, że nawet na SQL Server 2005 jest to do powtórzenia.

Mierzyć, ważyć, oceniać

Pierwsze, czego potrzebujemy, to jakieś źródło danych wydajnościowych, najlepiej „eSQueLowe” :-) Pierwsze, co przychodzi do głowy, to perfmon, ale jak wydobyć wartości poszczególnych liczników mając w ręku SQL Server Management Studio? Niby jest taki DMV o nazwie sys.dm_os_performance_counters. Problem z tym widokiem jest jednak taki, że pokazuje wyłącznie liczniki jednej instancji SQL Servera i nie ma możliwości obejrzenia kluczowych wskaźników pochodzących z Windows. A zatem trzeba użyć czegoś innego. A może by tak CLR i kasy z przestrzeni System.Diagnostics? Spróbujmy:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Diagnostics;
public partial class UserDefinedFunctions
{
  [Microsoft.SqlServer.Server.SqlFunction]
  public static SqlDouble ufn_clr_GetPerfCounterValue(
    SqlString CategoryName,
    SqlString CounterName,
    SqlString InstanceName,
    SqlString MachineName
  )
  {
    MachineName = MachineName.IsNull ? "." : MachineName.Value;
    PerformanceCounter p = new PerformanceCounter(
      CategoryName.Value,
      CounterName.Value,
      InstanceName.Value,
      MachineName.Value);
    float value = p.NextValue();
    System.Threading.Thread.Sleep(100);
    value = p.NextValue();
    return new SqlDouble(value);
  }
};

Krótki komentarz: usypianie wątku wrzuciłem, ponieważ bez tego niektóre wartości liczników dziwacznie odbiegały od rzeczywistości (można tu trochę poeksperymentować z czasem oczekiwania). Taki kod wklejamy do pliku .cs w projekcie Visual Studio założonego w oparciu o szablon Database Project i wrzucamy skompilowany plik DLL jako assembly z PERMISSION_SET = UNSAFE (działamy poza SQL Serverem) do wybranej bazy danych. Dzięki temu mamy teraz w bazie danych funkcję, która umie zwracać wartość wskazanego licznika permona. Gdyby ktoś miał wątpliwości, jak używać tej funkcji, poniżej przykład:

SELECT 
  dbo.ufn_clr_GetPerfCounterValue('Procesor',
    'Czas procesora (%)', '_Total', '.') AS CPU,
  dbo.ufn_clr_GetPerfCounterValue('Dysk fizyczny',
    'Średnia długość kolejki dysku', '0 C:', '.') AS DiskQueue,
  dbo.ufn_clr_GetPerfCounterValue('MSSQL$SQL2008R2:Buffer Manager',
    'Page life expectancy', '', '.') AS PageLifeExpectancy,
  dbo.ufn_clr_GetPerfCounterValue('MSSQL$SQL2008R2:Buffer Manager',
    'Buffer cache hit ratio', '', '.') AS BufferCacheHitRatio;

Dla każdego licznika trzeba podać: nazwę kategorii liczników (np. Procesor), nazwę licznika (np. Czas procesora (%)), instancję licznika (o ile licznik posiada instancję, np. procesor ma instacje wskazujące na poszczególne CPU logiczne oraz instancję _Total mówiącą o łącznym zużyciu wszystkich CPU), nazwę maszyny (tak, tak, jak się da, to można monitorować więcej niż jedną maszynę). Jeżeli nie wiesz, jak nazywają się kategorie, liczniki, czy instancje, otwórz perfmon.exe i dodaj interesujące Cię wskaźniki, a następnie przełącz permona w widok raportu, tam wszystko jest pokazane (patrz rysunek poniżej).

Skoro umiemy już wyświetlać wskaźniki perfmona, trzeba pomyśleć nad mechanizmem, który umożliwi informowanie administratora o ewentualnych problemach. Do tego celu założę tabelę, która będzie zawierała wskaźniki perfmona, które chcę monitorować.

USE DBAToolbox;
GO
IF OBJECT_ID('dbo.PerfCounters', 'U') IS NOT NULL
  DROP TABLE dbo.PerfCounters;
GO
CREATE TABLE dbo.PerfCounters (
  PerfCounterID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
  Category nvarchar(4000) NOT NULL,
  Counter nvarchar(4000) NOT NULL,
  Instance nvarchar(4000) NOT NULL DEFAULT '',
  Machine nvarchar(256) NOT NULL DEFAULT '.',
  Description nvarchar(4000) NULL,
  WarningMinValue float NULL,
  WarningMaxValue float NULL
);
GO
INSERT INTO dbo.PerfCounters (
  Category, Counter, Instance, Machine,
  Description, WarningMinValue, WarningMaxValue
)
VALUES
  ('Procesor', 'Czas procesora (%)', '_Total', '.',
   'Zużycie procesora. Ma być poniżej 50%.', NULL, 50),
  ('Dysk fizyczny', 'Średnia długość kolejki dysku', '0 C:', '.',
   'Kolejka dysku C:. Ma być poniżej 2.', NULL, 2),
  ('MSSQL$SQL2008R2:Buffer Manager', 'Page life expectancy', '', '.',
   'Czas życia stron w pamięci. Ma być powyżej 1000.', 1000, NULL),
  ('MSSQL$SQL2008R2:Buffer Manager', 'Buffer cache hit ratio', '', '.',
   'Procent trafień w bufor danych. Ma być powyżej 90%.', 90, NULL),
  ('MSSQL$SQL2008R2:General Statistics', 'User Connections', '', '.',
   'Ilość połączeń użytkowników.', NULL, NULL),
  ('MSSQL$SQL2008R2:Memory Manager', 'Total Server Memory (KB)', '', '.',
   'Pamięć zajęta przez SQL Server.', NULL, NULL),
  ('MSSQL$SQL2008R2:Plan Cache', 'Cache Hit Ratio', '_Total', '.',
   'Procent trafień w plan cache. Ma być powyżej 95%.', 95, NULL);
GO

W tabeli podajemy wszystkie dane dotyczące licznika, a także ustalamy, jakie mają być dolne (WarningMinValue) i górne (WarningMaxValue) wartości licznika, przy których nasz monitor powinien ostrzegać administratora, że coś nie gra. Tu można podać wartości wedle uznania / przyzwyczajeń / najlepszych praktyk :-) Na podstawie tabeli budujemy zapytanie, którego następnie użyjemy do zbudowania naszego narzędzia monitorującego.

SELECT
  Category + ' - ' + Counter +
  CASE Instance
    WHEN '' THEN ''
    ELSE ' [ ' + Instance + ' ]'
  END AS Counter,
  dbo.ufn_clr_GetPerfCounterValue(Category, Counter, Instance, Machine) AS CounterValue,
  CASE
    WHEN
      (dbo.ufn_clr_GetPerfCounterValue(Category, Counter,
        Instance, Machine) > WarningMaxValue) OR
      (dbo.ufn_clr_GetPerfCounterValue(Category, Counter,
        Instance, Machine) < WarningMinValue)
    THEN 1
    ELSE 0
  END AS Warning,
  Description
FROM dbo.PerfCounters;

Powyższe zapytanie wygeneruje zestaw danych, który oprócz nazw wskaźników, ich wartości i opisów, będzie także zawierał binarną informację, czy wartość wskaźnika mieści się w normach (kolumna Warning) – wartość 1 oznacza, że norma została naruszona.

Wyświetlać, odświeżać, reagować

Czego można użyć do zaprezentowania danych zwracanych przez powyższe zapytanie? A co jest ładne, kolorowe i dostarczone w pudełku wraz z SQL Serverem? Reporting Services oczywiście :-) A zatem raport…

Budowa raportu jest prosta, niczym konstrukcja cepa (Report Builder, Visual Studio – nieważne, czego użyjesz). Jako źródło danych podajemy zapytanie opracowane powyżej. Jedyna trudność, to wygenerowanie ostrzeżenia, gdy Warning=1. Ale przecież Reporting Services oferuje nam formatowanie warunkowe. Zaznaczamy w projekcie raportu cały wiersz danych i we właściwości BackgroundColor wpisujemy:

=IIF(Fields!Warning.Value=1,"Red","White")

I w zasadzie tyle. Wielbiciele ładnych ikonek mogą się pobawić w indicators albo gauge, ale ja wyszedłem z założenia, że raport dla DBA to nie choinka i ma być jak najlżejszy (jak najmniej fajerwerków). Mój raport prezentował się tak:

Widać, że jeden wskaźnik miał wartość poza normą i został oznaczony kolorem czerwonym.

Teraz tylko trzeba spowodować, żeby taki raport odświeżał się na wielkim monitorze :-) I tu też okazuje się, że Reporting Services mają dla nas niespodziankę (?). W definicji raportu mamy taki element, jak AutoRefresh (zaraz na początku definicji raportu). Można w nim ustawić ilość sekund, co jaką raport będzie odświeżany (oczywiście, jeżeli będziemy go oglądać w Report Managerze). Wiedząc to, ustawiamy wartość AutoRefresh na przykład na 15 sekund i wrzucamy raport na serwer raportów. A potem już tylko delektujemy się samoodświeżająym się raportem :-)

Post mortem

Wszystko, co napisałem powyżej, jest tylko małym fragmentem pomysłu na większą całość. Do tego można dodać na przykład takie „bajery”, jak:

• śledzenie tego, co można zbadać za pomocą DMV (np. można zrobić procedurę składowaną, która zbiera, co trzeba, do tabeli tymczasowej, po czym zwraca wynik w postaci jednej tabeli – i tę procedurę dajemy jako źródło raportu, zaś definicje zapytań do DMV możemy trzymać w tabeli z wskaźnikami i wykonywać je dynamicznym SQL-em),
• zapisywanie historii wskazań do bazy danych w celu analizy trendów,
• zbudowanie raportów szczegółowych na temat wybranych wskazań liczników (np. dla user connections raport pokazujący sesje),
• funkcje CLR do zwracania pełnej listy kategorii, liczników i instancji (żeby się nie męczyć w przeglądanie ich w perfmonie),
• dołożenie AutoRefresh do raportów standardowych Management Studio i Performance Dashboard Reports.

Ale generalnie pocieszające jest, że sam SQL Server oferuje nam tyle możliwości, że sami możemy konstruować narzędzia do monitorowania.

I na koniec odsyłam do ciekawego „białego papierka” Microsoftu, z którego można wyczytać wiele ciekawych rzeczy o monitorowaniu SQL Servera i rozwiązywaniu problemów wydajnościowych: Troubleshooting Performance Problems in SQL Server 2008.