Jasne i ciemne strony baz danych

… w szczególności MS SQL Server ;-)

SQL Server – Interaktywny performance dashboard dla ubogich

Posted by C3PO w dniu 30 czerwca 2010


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

Performance Monitor

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:

Dashboard

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.

Advertisements

komentarzy 28 to “SQL Server – Interaktywny performance dashboard dla ubogich”

  1. Michał said

    Wielkie dziękować za chęć dzieleniem się wiedzą, dla mnie akurat bardzo przydatną.
    Plus Duży koledze daje :)

  2. C3PO said

    Michał, dzięki za Dużego Plusa :-) Takie komentarze, jak Twój, na pewno działają na mnie pobudzająco ;-)

  3. Michał said

    A czy mógłbyć podać kod procedury „ufn_clr_GetPerfCounterValue” ? :)
    Czy może ja coś źle robie:
    1 biblioteka podpięta
    2 assembly OK

    ale zapytanko:

    SELECT
    dbo.ufn_clr_GetPerfCounterValue…

    nie działa :/

    Msg 4121, Level 16, State 1, Line 1
    Cannot find either column „dbo” or the user-defined function or aggregate „dbo.ufn_clr_GetPerfCounterValue”, or the name is ambiguous.

    o co kaman?

    jestem DBA mniej DD :)

  4. C3PO said

    Michał, idealnie by było, jakbyś po prostu użył Visual Studio i szablonu projektu Database Project. Wówczas Visual Studio robi za Ciebie wszystko i masz funkcję gotową w bazie. Natomiast kod funkcji, jeśli tworzysz ją rękami, jest mniej więcej taki:

    CREATE FUNCTION [dbo].[ufn_clr_GetPerfCounterValue](
    @CategoryName [nvarchar](4000),
    @CounterName [nvarchar](4000),
    @InstanceName [nvarchar](4000),
    @MachineName [nvarchar](4000))
    RETURNS [float]
    AS
    EXTERNAL NAME [NazwaAssembly].[UserDefinedFunctions].[ufn_clr_GetPerfCounterValue];
    GO

  5. Michał said

    Dopiero zaczynam się poruszać w temacie.
    Na drugi raz tak też zrobię :)

    Serdecznie dziękuję :)

    P.S. działa Tobie już dbmail na Expresie 2008 R2?

  6. Michał said

    SUper, dziękuje, działa :)

    Zaczynam przygodę i co raz bardziej mnie wciąga to ;)

    Czekam na opinie od MS co z tym DB Mailem.

    P.S. A Tobie działa DB Mail juz na 2008 R2 Expresie?

  7. wgrochow said

    Piszecie tutaj o DB Mail w 2008 R2 Express, czy ta funkcjonalność została dodana do 2008R2 ??

  8. C3PO said

    @Wgrochow: Obawiam się, że nie. To znaczy – można ją włączyć, ale nie wiemy, czy jest to zgodne z EULA… A obstawiamy, że nie jest.

  9. wgrochow said

    Dzięki za odpowiedź. Tak też myślałem. Chociaż czytając tutaj Wasze komentarze pojawiła się iskierka nadziei ;)

    Pozdrawiam

  10. Michał said

    Dodam coś od siebie – kod sprawdzajacy istnienie serwera w sieci przed wykonaniem Pawła skryptów.
    Można go zmodyfikować aby w pętli odczytywał serwery i wykonywał to co zamieszczam jako oddzielne linie kodu (dla każdego z poniższych serwerów.
    Taki kod potrzebny mi był aby w raporcie odpowiednio się wyśietlał.
    Union wklejany po przed ostatnim selectem, stąd ostatni „blok” kodu jest niezbędny.

    Może ktoś z Was miałby lepszy pomysł?
    -=====================================-

    declare @odpowiedz varchar(30),@systemname varchar(20),@cmdcommand sysname, @execcomsql nvarchar(max);
    BEGIN
    create table #errorlog(valid varchar(50))

    set @odpowiedz = ‚ ‚
    set @systemname = ‚sys1’
    set @cmdcommand = ‚if not exist \\’ + @systemname + ‚\c$\ @echo serwer niedostepny’
    set @execcomsql = ”
    insert into #errorlog
    exec master.sys.xp_cmdshell @cmdcommand;
    select top 1 @odpowiedz=valid from #errorlog
    delete from #errorlog
    IF @odpowiedz = ‚serwer niedostepny’
    BEGIN
    set @execcomsql = @execcomsql + ‚select ”sys1” as NazwaSystemu, ”Serwer niedostepny” as Source,0 as ErrCount
    UNION

    END
    ELSE IF @odpowiedz IS NULL
    BEGIN
    set @execcomsql = @execcomsql + ‚SELECT ”sys1” as NazwaSystemu,Source,COUNT(Source) as ErrCount FROM dbo.ufn_clr_GetEventLogLastListIle_ok(”Application”,”sys1”, 100) where EntryType like ”Error” GROUP BY Source
    UNION

    END

    set @odpowiedz = ‚ ‚
    set @systemname = ‚sys2’
    set @cmdcommand = ‚if not exist \\’ + @systemname + ‚\c$\ @echo serwer niedostepny’

    insert into #errorlog
    exec master.sys.xp_cmdshell @cmdcommand;
    select top 1 @odpowiedz=valid from #errorlog
    –select valid from #errorlog
    delete from #errorlog
    IF @odpowiedz = ‚serwer niedostepny’
    BEGIN
    set @execcomsql = @execcomsql + ‚select ”sys2” as NazwaSystemu, ”Serwer niedostepny” as Source,0 as ErrCount
    UNION

    END
    ELSE IF @odpowiedz IS NULL
    BEGIN
    set @execcomsql = @execcomsql + ‚SELECT ”sys2” as NazwaSystemu,Source,COUNT(Source) as ErrCount FROM dbo.ufn_clr_GetEventLogLastListIle_ok(”Application”,”sys2”, 100) where EntryType like ”Error” GROUP BY Source
    UNION

    END

    .
    .
    .
    .

    — i ostatnia linia kodu MUSI BĆ TAKA OSTATNIA

    set @odpowiedz = ‚ ‚
    set @systemname = ‚sysn’
    set @cmdcommand = ‚if not exist \\’ + @systemname + ‚\c$\ @echo serwer niedostepny’

    insert into #errorlog
    exec master.sys.xp_cmdshell @cmdcommand;
    select top 1 @odpowiedz=valid from #errorlog
    –select valid from #errorlog
    delete from #errorlog
    IF @odpowiedz = ‚serwer niedostepny’
    BEGIN
    set @execcomsql = @execcomsql + ‚select ”sysn” as NazwaSystemu, ”Serwer niedostepny” as Source,0 as ErrCount

    END
    ELSE IF @odpowiedz IS NULL
    BEGIN
    set @execcomsql = @execcomsql + ‚SELECT ”sysn” as NazwaSystemu,Source,COUNT(Source) as ErrCount FROM dbo.ufn_clr_GetEventLogLastListIle_ok(”Application”,”sysn”, 100) where EntryType like ”Error” GROUP BY Source

    END

    EXEC sp_executesql @execcomsql;
    DROP TABLE #errorlog;
    END;

  11. steergort said

    Yo
    It is my first post here, just wanted to say if you want to see some thing cool check out this thing
    Antyki it realy changed my day.

  12. Heya i am for the first time here. I came across this board and
    I find It truly useful & it helped me out a lot. I hope to give something back and
    aid others like you helped me.

  13. Wonderful items from you, man. I’ve consider your stuff prior to and
    you’re simply extremely excellent. I actually like what
    you’ve received here, certainly like what you are saying and the best way wherein you
    say it. You are making it entertaining and you still take care of to keep it sensible.
    I can not wait to learn far more from you. This
    is really a terrific web site.

  14. I am sure this piece of writing has touched all the internet people, its really really good article on building up new webpage.

  15. Garland said

    Hello there, You’ve done an incredible job. I will
    certainly digg it and personally suggest to my friends.
    I am sure they’ll be benefited from this site.

  16. Link exchange is nothing else except it is simply placing the other
    person’s website link on your page at appropriate place and other
    person will also doo same in favor of you.

  17. Usually I don’t discover document on weblogs, nonetheless would want to point out that this write-up incredibly obligated my family to have a look during along with take action! Your current way with words have been amazed us. Thank you so much, incredibly great document.

  18. Kendall said

    If you want to grow your familiarity simply keep
    visiting this web page and be updated with the most recent news posted here.

  19. twcu.exe said

    Please let me know if you’re looking for a author for your weblog.
    You have some really good posts and I think I would be
    a good asset. If you ever want to take some of
    the load off, I’d really like to write some articles for your blog in exchange for a link back to mine.

    Please shoot me an email if interested. Cheers!

  20. Mari said

    Wonderful blog! I found it while searching on Yahoo News.
    Do you have any suggestions on how to get listed in Yahoo News?
    I’ve been trying for a while but I never seem to get there!
    Many thanks

  21. When someone writes an paragraph he/she maintains
    the plan of a user in his/her mind that how a user can be aware of it.

    Therefore that’s why this post is amazing. Thanks!

  22. vlc.exe said

    Hi there! I could have sworn I’ve visited this blog
    before but after looking at many of the articles I realized it’s new to me.
    Anyways, I’m definitely pleased I discovered it and I’ll be book-marking it and checking back often!

  23. Hi! Quick question that’s totally off topic.
    Do you know how to make your site mobile friendly? My site looks weird when browsing from my apple iphone.
    I’m trying to find a theme or plugin that might be able
    to correct this problem. If you have any suggestions, please share.
    Appreciate it!

  24. If some one wishes expert view about blogging and site-building afger that i propose him/her to pay a
    quick visit this weblog, Keep up thhe good work.

  25. Hi, after reading this amazing post i am too delighted too
    share my experience here wwith friends.

  26. Yesterday, while I was at work, my sister stole my iPad and tested to
    see if it can survive a forty foot drop, just so she can be a youtube sensation. My apple ipad is now destroyed and she has 83 views.

    I know this is completely off topic but I had to share it with someone!

  27. machine a sous gratuit mario

    SQL Server – Interaktywny performance dashboard dla ubogich « Jasne i ciemne strony baz danych

  28. Hejka, ciekawy wpis. O ile masz czas zapraszam do rewizyty.

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Log Out / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Log Out / Zmień )

Facebook photo

Komentujesz korzystając z konta Facebook. Log Out / Zmień )

Google+ photo

Komentujesz korzystając z konta Google+. Log Out / Zmień )

Connecting to %s

 
%d blogerów lubi to: