Jasne i ciemne strony baz danych

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

Posts Tagged ‘CLR’

SQL Server – REPLACE (T-SQL kontra CLR) – dogrywka

Posted by C3PO w dniu 28 lipca 2010


Na początku bieżącego miesiąca podjąłem próbę przetestowania pod kątem wydajności napisanego w CLR zamiennika dla systemowej funkcji REPLACE w SQL Server 2008. Jednak Marek Adamczuk (dzięki, Marek) zwrócił mi uwagę na fakt, że moja funkcja ufn_clr_Replace zawsze uwzględniała wielkość liter w napisach podawanych jako parametry funkcji. Postanowiłem więc stworzyć alternatywną implementację funkcji ufn_clr_Replace:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
  [Microsoft.SqlServer.Server.SqlFunction]
  [return: SqlFacet(MaxSize = -1)]
  public static SqlString ufn_clr_Replace(
      [SqlFacet(MaxSize = -1)]
        string Input,
      [SqlFacet(MaxSize = -1)]
        string ToReplace,
      [SqlFacet(MaxSize = -1)]
        string Replacement,
        bool IsCaseSensitive)
  {
    return new SqlString(
      Regex.Replace(
        Input,
        Regex.Escape(ToReplace),
        Replacement,
        IsCaseSensitive == false ? RegexOptions.IgnoreCase : RegexOptions.None
      )
    );
  }
};

Co się zmieniło? Przede wszystkim zrezygnowałem z użycia metody Replace klasy String. Zamiast tego użyłem metody Replace klasy Regex (implementacja wyrażeń regularnych w .NET). Dodałem parametr IsCaseSensitive, który decyduje, czy ma być rozróżniana wielkość liter.

Test przeprowadziłem za pomocą analogicznego kodu, co w przypadku mojej poprzedniej próby.

Wyniki są dość jednoznaczne. Systemowa funkcja REPLACE wypada lepiej w mniej więcej 3400 na 5300 przypadków, a w przypadkach, gdy działa wolniej, różnica w czasie działania porównywanych funkcji jest znikoma (w granicach marginesu błędu). Zbliżony wynik otrzymałem też, gdy nie użyłem metody Escape klasy Regex na parametrze ToReplace (metoda ta pozwala zignorować ewentualne wyrażenia regularne w napisie będącym wzorcem do znalezienia i zastąpienia – używając jej uzyskałem „zwyczajną” funkcję REPLACE, która nie wykorzystuje wyrażeń regularnych).

Reklamy

Posted in MS SQL Server | Otagowane: , , , | Leave a Comment »

SQL Server – Listowanie ostatnich N zdarzeń z dziennika zdarzeń (CLR)

Posted by C3PO w dniu 14 lipca 2010


Niedawno pisałem o tym, jak można czytać dzienniki zdarzeń systemu Windows za pomocą funkcji napisanej dla SQL Servera w CLR (patrz tutaj). Jednak podana funkcja ma podstawową wadę – czyta cały dziennik, a to oczywiście może trwaaaaaać :-)

Dlatego spróbowałem nieco zmienić kod C#, by dawało się określić, ile ostatnich wpisów ze wskazanego dziennika chcemy przeczytać. Powstało mi na razie coś takiego (pewnie za moment będzie ewoluować):

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,
    SqlInt32 HowMany
  )
  {
    EventLog log = new EventLog(LogName.Value, MachineName.Value);
    int size =
      (log.Entries.Count - HowMany.Value) >= 0 ?
      HowMany.Value :
      log.Entries.Count;
    EventLogEntry[] entries = new EventLogEntry[size];
    int index =
      (log.Entries.Count - HowMany.Value) >= 0 ?
      (log.Entries.Count - HowMany.Value) :
      0;
    for (int i = index; i <= log.Entries.Count - 1; i++)
    {
      entries[i-index] = log.Entries[i];
    }
    return 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;
  }
};

Doszedł trzeci parametr funkcji – HowMany, który określa, ile ostatnich wpisów chcemy zwrócić. Reszta to prosta matematyka i liczenie indeksów tablic (mam nadzieję, że nic nie pokpiłem). Zapewne da się to napisać prościej, ale nie jestem programistą .NET, więc poradziłem sobie, jak umiałem :-) Spróbuję jeszcze wydumać, jak zwracać zdarzenia pomiędzy zadanymi datami lub od zadanej daty, ale to już będzie dla mnie nie lada wyzwanie ;-)

Bez projektu Database Project po skompilowaniu do pliku .dll i wczytaniu owego pliku jako assembly (polecenie CREATE ASSEMBLY z opcją PERMISSION_SET ustawioną na UNSAFE) do bazy danych funkcję tworzymy tak:

CREATE FUNCTION [dbo].[ufn_clr_GetEventLog](
  @LogName [nvarchar](4000),
  @MachineName [nvarchar](4000),
  @HowMany [int])
RETURNS  TABLE (
    [EntryType] [nvarchar](50) NULL,
    [TimeWritten] [datetime] NULL,
    [Source] [nvarchar](4000) NULL,
    [InstanceId] [bigint] NULL,
    [Category] [nvarchar](255) NULL,
    [Message] [nvarchar](max) NULL
)
AS
EXTERNAL NAME [DBAToolbox].[UserDefinedFunctions].[ReadLog];
GO

DBAToolbox to nazwa assembly podana w poleceniu CREATE ASSEMBLY.

Przykładowe wywołanie funkcji:

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

Przyjemnego przeglądania dzienników życzę :-)

[EDYCJA | 2010-07-21]
A jeżeli chesz posortować rekordy zwracane przez funkcję ufn_clr_GetEventLog malejąco po dacie (od najświeższych wpisów do najstarszych), zastąp linijkę:

entries[i - index] = log.Entries[i];

odpowiednikiem:

entries[size - 1 - i + index] = log.Entries[i];

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

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.

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 – 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: , , , , , | 28 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 »