Jasne i ciemne strony baz danych

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

SQL Server – Listowanie dzienników zdarzeń Windows (CLR)

Posted by C3PO w dniu 9 lipca 2010


Jeden z czytelników mojego bloga (Michał, pozdrawiam) napisał mi w mailu, że chciałby mieć funkcję w SQL Server dzięki której mógłby przeglądać dzienniki zdarzeń Windows. Co prawda w Management Studio można przeglądać dzienniki zdarzeń Windows razem z logiem samego SQL Servera. Ale jeżeli ktoś chce działać kodem T-SQL lub móc z jakichś powodów przerzucać dane z dzienników zdarzeń do tabel w bazach danych, przydałaby się funkcja, która umożliwi dostęp do wybranego dziennika na wybranej maszynie.

Kod takiej funkcji może wyglądać tak:

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Diagnostics;
public partial class UserDefinedFunctions
{
  [Microsoft.SqlServer.Server.SqlFunction(
      Name = "ufn_clr_GetEventLog",
      FillRowMethodName = "FillEventLogRow",
      TableDefinition = "EntryType nvarchar(50), " +
                        "TimeWritten datetime, " +
                        "Source nvarchar(4000), " +
                        "InstanceId bigint, " +
                        "Category nvarchar(255), " +
                        "Message nvarchar(max)"
  )]
  public static IEnumerable ReadLog(SqlString LogName, SqlString MachineName)
  {
    EventLog log = new EventLog();
    return new EventLog(LogName.Value, MachineName.Value).Entries;
  }
  public static void FillEventLogRow(
    Object Obj,
    out SqlString EntryType,
    out SqlDateTime TimeWritten,
    out SqlString Source,
    out SqlInt64 InstanceId,
    out SqlString Category,
    out SqlString Message
  )
  {
    EventLogEntry entry = (EventLogEntry)Obj;
    EntryType = entry.EntryType.ToString();
    TimeWritten = new SqlDateTime(entry.TimeWritten);
    Source = entry.Source;
    InstanceId = new SqlInt64(entry.InstanceId);
    Category = entry.Category;
    Message = entry.Message;
  }
};

 Szybki komentarz do kodu:

  • jest to klasyczna funkcja tabelaryczna CLR (zwraca kolekcję zgodną z interfejsem IEnumerable),
  • w atrybucie SqlFunction definiujemy schemat tabeli zwracanej przez funkcji,
  • metoda FillEventLogRow jest typowym „wypełniaczem” pojedynczego wiersza kolekcji zwracanej przez funkcję.

Kod wklejamy w Visual Studio do pliku .cs w projekcie opartym o szablon Database Project (C#). Wrzucamy assembly (PERMISSION_SET = UNSAFE)  i funkcję do bazy danych (w Visiaul Studio w menu głównym kliknij Build – Deploy <nazwa_projektu>). Wywołanie tej funkcji może wyglądać tak (oczywiście, tu trzeba zadbać, by konto Windows, w kontekście którego działa SQL Server, miało możliwość podlgądania wskazanego dziennika zdarzeń):

SELECT * FROM dbo.ufn_clr_GetEventLog('Application', 'MojSerwer');

Przykładowy wynik:

Dziennik zdarzeń

Oczywiście, nie polecam za często uruchamiać tej funkcji na logach zawierających dużo wpisów. Lepiej z jej pomocą zaimportować wpisy zdarzeń do bazy danych do zaindeksowanej tabeli i później już zapytaniami filtrować, listować, grupować i co tam jeszcze przychodzi komu do głowy.

Uff, dwa posty w ciągu jednego dnia? Chyba oszalałem ;-) Obiecuję już tak nie robić więcej. I chwilowo dość pisania o CLR. Miłego weekendu.

Reklamy

Posted in MS SQL Server | Otagowane: , , | 5 Komentarzy »

SQL Server – REPLACE (T-SQL vs. CLR)

Posted by C3PO w dniu 9 lipca 2010


Geneza

Jakiś czas temu czytałem przemyślenia Adama Machanica na temat możliwości zastąpienia niektórych funkcji systemowych SQL Servera ich odpowiednikami napisanymi .NET (CLR). Testy były ciekawe, a wyniki obiecujące. Ja, że jestem niedowiarkiem, wichrzycielem i w ogóle, zżerająca mnie rdza nie daje spokoju, postanowiłem zrobić mały test na własną rękę.

Aktorzy przedstawienia

Jako jeden z przykładów została podana funkcja REPLACE, która w T-SQL służy do zastępowania we wskazanym tekście wskazanego tekstu wskazanym tekstem :-) Znaczy się, sygnatura jest jak niżej:

REPLACE (tekst_wejściowy, tekst_do_zastąpienia, tekst_zastępujący)

Napisałem sobie taki kawałek kodu C#, który jest niejako odpowiednikiem funkcjonalnym funkcji REPLACE dostępnej w SQL Server:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
  [Microsoft.SqlServer.Server.SqlFunction]
  [return: SqlFacet(MaxSize = -1)]
  public static SqlString ufn_clr_Replace(
      [SqlFacet(MaxSize = -1)] 
        SqlString Input,
      [SqlFacet(MaxSize = -1)] 
        SqlString ToReplace,
      [SqlFacet(MaxSize = -1)] 
        SqlString Replacement)
  {
    return new SqlString(Input.Value.Replace(ToReplace.Value, Replacement.Value));
  }
};

Kodu chyba nie ma co tłumaczyć (funkcja skalarna, pobiera trzy parametry typu nvarchar(max) i zwraca napis typu nvarchar(max) – tu użyłem atrybutu SqlFacet, by wymusić typ max po stronie SQL Servera). Kod skompilowałem, dodałem assembly (PERMISSION_SET oczywiście SAFE) do bazy o znajomo brzmiącej nazwie test (moja baza-poligon, w simple recovery). Przykładowe użycie tej funkcji:

SELECT test.dbo.ufn_clr_Replace('Ala ma kota', 'Ala', 'Ola') -- wynik: Ola ma kota

Benchmark

Pomyślałem, że test powinien zbadać, jak wypada ta funkcja CLR-owa ufn_clr_Replace w porównaniu z T-SQL-ową funkcją REPLACE, gdzie kryterium oczywiście będzie czas wykonywania operacji przy zmiennych długościach napisów i zmiennej ilości znaków do zastąpienia. Aby taki test przeprowadzić, napisałem kawałek kodu T-SQL:

USE test;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID('dbo.TestTable', 'U') IS NOT NULL
  DROP TABLE dbo.TestTable;
GO
PRINT '-- Creating dbo.TestTable';
CREATE TABLE dbo.TestTable (
  SampleText nvarchar(max) NOT NULL
);
GO
IF OBJECT_ID('dbo.ReplaceTestResults', 'U') IS NOT NULL
  DROP TABLE dbo.ReplaceTestResults;
GO
PRINT '-- Creating dbo.ReplaceTestResults';
CREATE TABLE dbo.ReplaceTestResults (
  FunctionName sysname NOT NULL,
  StringLength int NOT NULL,
  CharsToReplace int NOT NULL,
  Duration int NOT NULL
);
GO
DECLARE
  @i int,
  @j int = 10,
  @t datetime,
  @fake nvarchar(max),
  @ToReplace nvarchar(max) = N'a',
  @ReplaceWith nvarchar(max) = N'x';
WHILE @j <= 1000
BEGIN 
  SET @i = 1;
  WHILE @i <= @j
  BEGIN
    PRINT '-- Testing ' + CONVERT(varchar(4), @j) + ' chars, '
      + CONVERT(varchar(4), @i) + ' to replace';
    WITH CTE AS (
      SELECT 1 AS n
      UNION ALL
      SELECT n + 1
      FROM CTE
      WHERE n < 1000
    )
    INSERT INTO dbo.TestTable (SampleText)
    SELECT REPLICATE('a', @i) + REPLICATE('b', @j - @i)
    FROM CTE OPTION (MAXRECURSION 0);
    CHECKPOINT;
    DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
    DBCC FREEPROCCACHE WITH NO_INFOMSGS;
    SET @t = GETDATE();
    SELECT @fake = dbo.ufn_clr_Replace(
      SampleText, @ToReplace, @ReplaceWith
    )
    FROM dbo.TestTable;
    INSERT INTO dbo.ReplaceTestResults (
      FunctionName, StringLength, CharsToReplace, Duration
    )
    SELECT 'ufn_clr_Replace', @j, @i, DATEDIFF(ms, @t, GETDATE());
    DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
    DBCC FREEPROCCACHE WITH NO_INFOMSGS;
    SET @t = GETDATE();
    SELECT @fake = REPLACE(SampleText, @ToReplace, @ReplaceWith)
    FROM dbo.TestTable;
    INSERT INTO dbo.ReplaceTestResults (
      FunctionName, StringLength, CharsToReplace, Duration
    )
    SELECT 'REPLACE', @j, @i, DATEDIFF(ms, @t, GETDATE());
    TRUNCATE TABLE dbo.TestTable;
    SET @i = @i + CASE WHEN @i = 1 THEN 9 ELSE 10 END;
  END;
  SET @j = @j + 10;
END;

Słowo wyjaśnienia:

  • tworzę tabelę TestTable, do której wrzucam na początku każdej iteracji na nowo 1000 rekordów, ale za każdym razem wartości w kolumnie SampleText są inne (zmienia się długość – @j oraz ilość znaków do zastąpienia – @i),
  • w każdej iteracji zbieram czas wykonania zapytania zwracającego wszystkie rekordy z tabeli TestTable potraktowane porównywanymi funkcjami T-SQL i CLR,
  • aby wyniki były niezależne od tego, co siedzi w cache’ach, opróżniam przed każdym testem buffer cache i plan cache (polecenia DBCC),
  • po każdej iteracji czyszczę tabelę TestTable (TRUNCATE TABLE).

Po wszystkim mam wyniki zapisane w tabeli ReplaceTestResults.

Na mojej maszynie (laptop Core 2 Duo, 4GB RAM, 64-bit Windows 7 i SQL Server 2008 R2 wersja RTM) test trwał około godziny czasu. Powtórzyłem go kilka razy, żeby sprawdzić, czy wyniki są powtarzalne (i są).

The winner is…

Wyniki można by było pewnie zaprezentować na sto różnych sposobów. Ja postanowiłem użyć dwóch wykresów.

Wykres 1

Powyższy wykres pokazuje, w ilu procentach przypadków funkcja CLR działała szybciej, niż funkcja T-SQL w zależności od długości napisów. Widać, że równowaga zaczyna się gdzieś w okolicach 500-550 znaków.

Wykres 2

Powyższy wykres pokazuje, w ilu procentach przypadków  funkcja CLR działała szybciej, niż funkcja T-SQL w zależności od ilości zastępowanych znaków. Od 400 znaków w zasadzie nie ma już wątpliwości, które rozwiązanie działało szybciej.

Czy można zatem powiedzieć wprost, które rozwiązanie (T-SQL czy CLR) jest lepsze? Oczywiście, NIE. Gdy tekst wejściowy jest krótki lub do zastąpienia jest niewiele znaków, stara, dobra funkcja REPLACE działa szybciej, niż jej CLR-owy odpowiednik. Ale jeżeli mamy długi tekst (setki lub tysiące znaków), a dodatkowo ilość znaków do zastąpienia jest także pokaźna (setki), opłaca sie mieć w arsenale funkcję CLR.

What’s next?

Co można zrobić dalej?

  • porównać wydajność REPLACE i funkcji CLR napisanej z wykorzystaniem klasy RegEx (wyrażenia regularne)
  • porównać wydajność innych przypadków funkcji systemowych (SUBSTRING, STUFF, PATINDEX,…) z ich odpowiednikami CLR
  • porównać, ile zasobów zużywają zapytania wykorzystujące odpowiedniki T-SQL i CLR
  • i oczywiście – można przeprowadzić powyższy test lepiej, dokładniej, dogłębniej i dać mi znać, gdzie popełniłem błędy w sztuce.

Posted in MS SQL Server | Otagowane: , , , | 14 Komentarzy »

SQL Server – Kontrola dzienników transakcji

Posted by C3PO w dniu 7 lipca 2010


Jednymi z problemów najczęściej poruszanych na forum poświęconym SQL Serverowi na portalu WSS.pl są wszelkiej maści kłopoty wynikające z zaniedbania dziennika transakcji bazy danych. Rekordziści prześcigają się w wielkości plików dziennika transakcji (185 GB dla bazy kilkugigabajtowej to dobry przykład). Często padają pytania, jak to wszystko (recovery model, dziennik transakcji, backup dziennika transakcji) działa. Nie zamierzam się rozpisywać o podstawach logowania operacji DML czy o tym, jak zbudowany jest dziennik transakcji. Pomyślałem za to, że fajnie byłoby napisać skrypt dla DBA lub konsultanta, dzięki któremu będzie można, wchodząc na „cudzy” (lub, o zgrozo, także swój!) serwer, zdiagnozować podstawowe problemy związane z higieną dziennika transakcji (a w zasadzie z brakiem tej higieny). Wspomniany skrypt prezentuje się tak:

SET NOCOUNT ON;
IF OBJECT_ID('tempdb.dbo.#VLFs', 'U') IS NOT NULL
  DROP TABLE #VLFs;
CREATE TABLE #VLFs (
  FileId int NULL,
  FileSize bigint NULL,
  StartOffset bigint NULL,
  FSeqNo bigint NULL,
  Status tinyint NULL,
  Parity smallint NULL,
  CreateLSN numeric(25,0) NULL
);
IF OBJECT_ID('tempdb.dbo.#NumberOfVLFs', 'U') IS NOT NULL
  DROP TABLE #NumberOfVLFs;
CREATE TABLE #NumberOfVLFs (
  DatabaseName sysname NOT NULL,
  NumberOfVLFs int NOT NULL
);
DECLARE @DBName sysname, @SQL nvarchar(4000);
DECLARE DBCursor CURSOR LOCAL STATIC
FOR
  SELECT name FROM sys.databases WHERE state_desc = 'ONLINE';
OPEN DBCursor;
FETCH NEXT FROM DBCursor INTO @DBName;
WHILE @@FETCH_STATUS = 0 BEGIN
  DELETE FROM #VLFs;
  SET @SQL =
  'INSERT INTO #VLFs
   EXEC ' + QUOTENAME(@DBName)
     + '.dbo.sp_executesql N''DBCC LOGINFO WITH NO_INFOMSGS'';
   INSERT INTO #NumberOfVLFs (DatabaseName, NumberOfVLFs)
   SELECT ' + QUOTENAME(@DBName, '''') + ', COUNT(*)
   FROM #VLFs;';
  EXEC (@SQL);
  FETCH NEXT FROM DBCursor INTO @DBName;
END
CLOSE DBCursor;
DEALLOCATE DBCursor; 
IF OBJECT_ID('tempdb.dbo.#LogHealth', 'U') IS NOT NULL
  DROP TABLE #LogHealth;
CREATE TABLE #LogHealth (
  DatabaseName sysname NULL,
  LogSizeMB numeric(38,2) NULL,
  LogSpaceUsed numeric(4,2) NULL,
  Status tinyint NULL
);
INSERT INTO #LogHealth EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS');
SELECT
  v.DatabaseName AS [Database name],
  d.recovery_model_desc AS [Recovery model],
  CASE
    WHEN d.recovery_model = 1
      AND r.last_log_backup_lsn IS NOT NULL
    THEN 1
    ELSE 0
  END AS [Was backup taken],
  q.number_of_files AS [Number of log files],
  l.LogSizeMB AS [Log size (MB)],
  l.LogSpaceUsed AS [Log space used (%)],
  v.NumberOfVLFs AS [Number of VLFs],
  d.log_reuse_wait_desc AS [Log reuse wait cause]
FROM #NumberOfVLFs AS v
INNER JOIN #LogHealth AS l
ON v.DatabaseName = l.DatabaseName
INNER JOIN sys.databases AS d
ON v.DatabaseName = d.name
INNER JOIN sys.database_recovery_status AS r
ON d.database_id = r.database_id
INNER JOIN (
  SELECT
    m.database_id,
    COUNT(*) AS number_of_files
  FROM sys.master_files AS m
  WHERE m.type = 1
  GROUP BY m.database_id
) AS q
ON d.database_id = q.database_id;
GO

Wynikiem działania powyższego kodu jest coś takiego:

Wynik

Co oznaczają kolejne kolumny?

  • Database name – nazwa bazy danych (powinny pokazać się wszystkie bazy)
  • Recovery model – tryb pracy dziennika transakcji (dla produkcyjnych baz zalecany FULL)
  • Was backup taken – wskazuje, czy został wykonany backup rozpoczynający działanie bazy w trybie FULL
  • Number of log files – ilość plików dziennika transakcji, w zasadzie zawsze powinien być 1 plik (wyjątkiem są sytuacje, gdy musimy dodać drugi lub kolejny plik dziennika transakcji z uwagi na brak miejsca na dysku przechowującym pierwszy plik dziennika)
  • Log size (MB) – rozmiar dziennika transakcji w megabajtach (wiadomo – duży może być zwłaszcza, gdy dziennik transakcji jest zaniedbany)
  • Log space used (%) – ile procent dziennika transakcji jest zajęte przez zapisane w nim transakcje (tak dowiemy się, ile brakuje do tego, by dziennik transakcji automatycznie się rozrósł)
  • Number of VLFs – ilość wirtualnych plików dziennika transakcji (ang. Virtual Log File); zalecane jest poniżej 50, ale rząd wielkości więcej – 300-500 to też nie jest wielka tragedia; ale jeżeli liczba idzie w dziesiątki / setki tysięcy, można spodziewać się problemów przy starcie bazy oraz dłuższego czasu wykonywania backupów i przywracania bazy
  • Log reuse wait cause – informacja na temat przyczyny braku możliwości ponownego zapełniania plików dziennika transakcji od pierwszych plików wirtualnych (najczęściej otwarte transakcje, ale przyczyn może być więcej)

Sam kod wykorzystuje kilka widoków i poleceń DBCC:

  • sys.databases – widok systemowy zawierający listę baz danych i ich właściwości
  • sys.master_files – widok systemowy zawierający listę plików wszystkich baz danych
  • sys.database_recovery_status – widok systemowy zawierający informacje na temat procesów recovery baz danych
  • DBCC LOGINFO – nieudokumentowane polecenie DBCC pokazujące układ wirtualnych plików dziennika transakcji dla bieżącej bazy danych (tu użyte do zliczenia wirtualnych plików)
  • DBCC SQLPERF(LOGSPACE) – polecenie DBCC zwracające listę baz danych wraz z wielkością dzienników transakcji oraz stopnia wypełnienia tych dzienników

Mam nadzieję, że powyższy kod się przyda. Działa na SQL Server 2005 i nowszych. Przy odrobinie wysiłku można kod przerobić, by działał także na wersji 2000.

I na deser kilka odnośników do postów na blogach, które przeczytałem przed przystąpieniem do pisania kodu:

Szczególnie ciekawy jest bug wymieniony na koniec ostatniego z wymienionych postów – polecam do poczytania :-)

Posted in MS SQL Server | Otagowane: , , , | 5 Komentarzy »

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.

Posted in MS SQL Server | Otagowane: , , , , , | 29 Komentarzy »

SQL Server – Konwersja kodowania CP620 (Mazovia) na CP1250 w CLR

Posted by C3PO w dniu 22 czerwca 2010


W moim pierwszym sesnownym wpisie na tym blogu (https://sqlwars.wordpress.com/2010/06/16/sql-server-jak-wylistowac-dostepne-kodowania-funkcja-tabelaryczna-clr/) podałem rozwiązanie problemu konwersji ze standardu Mazovia do Windows-1250 zaimplementowane w T-SQL. Obiecałem sobie, że przetestuję też rozwiązanie alternatywne stworzone w CLR. Oto ono:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(
        DataAccess=DataAccessKind.None,
        SystemDataAccess=SystemDataAccessKind.None,
        IsDeterministic=true,
        Name="ufn_clr_MazoviaTo1250")]
    [return:SqlFacet(MaxSize=-1)]
    public static SqlString ufn_clr_MazoviaTo1250(
      [SqlFacet(MaxSize=-1)] SqlString Input
    )
    {
      return Input.Value
        .Replace((char)260,  (char)323) //Ń
        .Replace((char)377,  (char)260) //Ą
        .Replace((char)321,  (char)211) //Ó
        .Replace((char)347,  (char)321) //Ł
        .Replace((char)8226, (char)262) //Ć
        .Replace((char)144,  (char)280) //Ę
        .Replace((char)152,  (char)346) //Ś
        .Replace((char)160,  (char)377) //Ź
        .Replace((char)711,  (char)379) //Ż
        .Replace((char)8224, (char)261) //ą
        .Replace((char)356,  (char)263) //ć
        .Replace((char)8216, (char)281) //ę
        .Replace((char)8217, (char)322) //ł
        .Replace((char)164,  (char)324) //ń
        .Replace((char)728,  (char)243) //ó
        .Replace((char)382,  (char)347) //ś
        .Replace((char)166,  (char)378) //ź
        .Replace((char)167,  (char)380); //ż
    }
};

Czemu tak na piechotę? Bo niestety, na liście obsługiwanych przez klasy z przestrzeni System.Text stron kodowych nie ma CP620 (Mazovia).

Trudność? Liczby oznaczające kody poszczególnych literek się zmieniły w porównaniu z T-SQL. Ale to dlatego, że napisy wchodzące do metod w .NET są kodowane w Unicode (UTF-16). Stąd potrzeba odrobiny cierpliwości i użycia w SQL Server funkcji UNICODE, by dowiedzieć się, jakie są kody liter i odpowiadających im znaków z kodowania Mazovia w kodowaniu UTF-16. Czyli, jeśli chciałem sprawdzić, jaki kod ma literka w UTF-16, robiłem coś takiego:

SELECT UNICODE(N'Ą');

W CLR miało być szybciej (tak twierdził jeden z uczestników dyskusji na forum portalu WSS.pl). I jest szybciej (ale musiałem sprawdzić ;-)). Wykonałem banalny test porównujący wydajność konwersji na 100 tysiącach wierszy przy użyciu obu funkcji (T-SQL i CLR):

IF OBJECT_ID('dbo.TestTableMazovia', 'U') IS NOT NULL
  DROP TABLE dbo.TestTableMazovia;
GO
CREATE TABLE dbo.TestTableMazovia (
  SampleText varchar(max) NOT NULL
);
GO
WITH CTE AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1
  FROM CTE
  WHERE n < 100000
)
INSERT INTO dbo.TestTableMazovia (SampleText)
SELECT 'Ź•śĄŁ˜ ˇ†Ť‘’¤˘ž¦§' FROM CTE OPTION (MAXRECURSION 0);
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
DECLARE @t datetime = GETDATE();
SELECT dbo.ufn_MazoviaTo1250(SampleText) FROM dbo.TestTableMazovia;
SELECT DATEDIFF(ms, @t, GETDATE());
GO
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
DECLARE @t datetime = GETDATE();
SELECT dbo.ufn_clr_MazoviaTo1250(SampleText) FROM dbo.TestTableMazovia;
SELECT DATEDIFF(ms, @t, GETDATE());
GO

Wynik:
T-SQL – 14.5 s
CLR – 2.5 s

Okazało się, że rozwiązanie T-SQL jest w tym przypadku około 6-7 razy wolniejsze (na wynik testu nie wpłynęła także zmiana typu danych kolumny na nvarchar(max) czy na varchar(100) – ani rozmiar, ani Unicode’owość nie zmieniły wyników pomiaru czasu trwania zapytań). Sprawdziłem obie funkcje także pod kątem działania na jednym, ale dość długim napisie (ponad 8000 znaków). Wynik niemal taki sam (XXX ms do XX ms).

Jedyne ale jest takie, że łatwiej jest wygenerować funkcję w T-SQL, jeśli mam daną tabelę z przejściami między poszczególnymi stronami kodowymi. Ale jeśli priorytetem jest wydajność, wydaje się, że CLR działa lepiej.

Posted in MS SQL Server | Otagowane: , , , | 4 Komentarze »

SQL Server – Jak wylistować dostępne kodowania funkcją tabelaryczną CLR?

Posted by C3PO w dniu 16 czerwca 2010


Ostatnimi czasy przesiaduję trochę na portalu WSS.pl. Na forum tegoż portalu wywiązała się całkiem interesująca dyskusja nad konwersją pomiędzy stroną kodową Mazovia (CP620) a stroną Windows-1250. Cały wątek jest tutaj. Postanowiłem drążyć temat dalej. Na razie stanęliśmy na tym, że mamy funkcję T-SQL:

IF OBJECT_ID('dbo.ufn_MazoviaTo1250', 'FN') IS NOT NULL
  DROP FUNCTION dbo.ufn_MazoviaTo1250;
GO
CREATE FUNCTION dbo.ufn_MazoviaTo1250 (@String varchar(maX))
RETURNS varchar(max)
WITH SCHEMABINDING
AS
BEGIN
  SET @String = @String COLLATE Polish_BIN;
  RETURN
    REPLACE (
      REPLACE (
        REPLACE (
          REPLACE (
            REPLACE (
              REPLACE (
                REPLACE (
                  REPLACE (
                    REPLACE (
                      REPLACE (
                        REPLACE (
                          REPLACE (
                            REPLACE (
                              REPLACE (
                                REPLACE (
                                  REPLACE (
                                    REPLACE (
                                      REPLACE (
                                        @String, CHAR(165), CHAR(209) -- Ń
                                      ), CHAR(143), CHAR(165)         -- Ą
                                    ), CHAR(163), CHAR(211)           -- Ó
                                  ), CHAR(156), CHAR(163)             -- Ł
                                ), CHAR(149), CHAR(198)               -- Ć
                              ), CHAR(144), CHAR(202)                 -- Ę
                            ), CHAR(152), CHAR(140)                   -- Ś
                          ), CHAR(160), CHAR(143)                     -- Ź
                        ), CHAR(161), CHAR(175)                       -- Ż
                      ), CHAR(134), CHAR(185)                         -- ą
                    ), CHAR(141), CHAR(230)                           -- ć
                  ), CHAR(145), CHAR(234)                             -- ę
                ), CHAR(146), CHAR(179)                               -- ł
              ), CHAR(164), CHAR(241)                                 -- ń
            ), CHAR(162), CHAR(243)                                   -- ó
          ), CHAR(158), CHAR(156)                                     -- ś
        ), CHAR(166), CHAR(159)                                       -- ź
      ), CHAR(167), CHAR(191)                                         -- ż
    ) COLLATE database_default;
END;
GO
-- Test
SELECT dbo.ufn_MazoviaTo1250 ('Ź•śĄŁ˜ ˇ†Ť‘’¤˘ž¦§'); 

Jeden z uczestników dyskusji zasugerował, by problem rozwiązać za pomocą funkcji napisanej w .NET (CLR). Wydaje się to być rozsądnym posunięciem. W końcu CLR może się spisywać lepiej w przypadku operacji na tekście (zwłaszcza długim). Na pierwszy ogień jednak postanowiłem rzucić sobie zadanie – napisanie funkcji do listowania dostępnych kodowań z poziomu .NET. Dzięki temu dowiem się, jakie kodowania mogę wykorzystać w konwersjach z użyciem metod klas z przestrzeni System.Text (np. klasy Encoding). Czyli rozchodzi się o to, by wiedzieć, jakie strony kodowe mogę konwertować niekoniecznie znak po znaku.

Po chwili skrobania wyszło mi coś takiego:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Collections;
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(
        FillRowMethodName="FillEncodings",
        Name="ufn_CLR_GetEncodings",
        TableDefinition="CodePage int,
          EncodingName nvarchar(255),
          DisplayName nvarchar(255)"
        )]
    public static IEnumerable ufn_CLR_GetEncodings()
    {
        EncodingInfo[] Encodings = Encoding.GetEncodings();
        return Encodings;
    }
    public static void FillEncodings(
      object Obj,
      out int CodePage,
      out string EncodingName,
      out string DisplayName)
    {
        EncodingInfo e = (EncodingInfo) Obj;
        EncodingName = e.Name;
        DisplayName = e.DisplayName;
        CodePage = e.CodePage;
    }
};

W razie wątpliwości, co należy z tym kodem zrobić, zajrzyj na przykład tutaj: http://geekswithblogs.net/frankw/archive/2008/05/03/a-quick-walk-through-of-clr-integration-with-sql-server.aspx.

Test wdrożonej funkcji wygląda tak:

SELECT * FROM dbo.ufn_CLR_GetEncodings() ORDER BY CodePage;

I okazuje się, że Mazovia oczywiście nie znalazła się na liście. No cóż, pozostaje zatem żmudna podmiana znak po znaku. Ale o tym już napiszę następnym razem, jak tylko napiszę właściwy kod i przetestuję szybkość rozwiązania CLR vs. szybkość rozwiązania T-SQL. Ciąg dalszy nastapi ;-)

 

Posted in MS SQL Server | Otagowane: , , , , | 7 Komentarzy »

Dobry początek (?)

Posted by C3PO w dniu 15 czerwca 2010


3…2…1…Start! Jedziemy z tym blogiem. W sumie blog – sprawa niezobowiązująca. Zatem zastrzegam od razu, że jak mi się znudzi, przestanę tu cokolwiek pisać i będzie to kolejne martwe miejsce w sieci. Ale tymczasem słowo od ojca prowadzącego (blog) n/t tego, o czym tu będzie pisane. Otóż będzie w większości o bazach danych, a z tego w większości o MS SQL Server, bo z tym zwierzem przychodzi mi się zmierzać najczęściej. Jeśli to dla Ciebie nie nudne, zapraszam.

Posted in Bez kategorii | Otagowane: , | 9 Komentarzy »