Integracja SQL Server 2005 i CLR z punktu widzenia administratora baz danych, cz. II

uruchomiony kod zarządzany (użytkownika) nie może naruszyć stabilności i integralności serwera baz danych. Na operacje nie spełniające tego założenia muszą być nałożone odpowiednio wysokie poziomy zabezpieczeń.

kod zarządzany nie może uzyskać dostępu do danych użytkownika (lub innego kodu) w bazie danych. Kod powinien być uruchomiony z odpowiednimi uprawnieniami w kontekście sesji użytkownika, który ten kod wywołuje.

musi istnieć mechanizm kontroli, który uniemożliwia odwołanie się do jakichkolwiek zasobów spoza procesu SQL Server.

kod użytkownika nie może uzyskać nieuprawnionego dostępu do zasobów systemowych przy pomocy procesu, w którym jest uruchomiony (proces silnika SQL Server).

SAFE

EXTERNAL _ACCESS

UNSAFE

W przypadku, gdy assembly zostało podpisane kluczem bądź certyfikatem na etapie kompilacji, na tej podstawie SQL Server utworzy klucz asymetryczny (lub certyfikat) oraz odpowiedni login bazujący na tym kluczu. Następnie login musi otrzymać odpowiednie uprawnienia (GRANT UNSAFE (EXTERNAL_ACCESS) ASSEMBLY TO Login).

Właściciel bazy danych posiada uprawnienia EXTERNAL ACCESS ASSEMBLY (lub UNSAFE ASSEMBLY) a baza danych ma ustawiona opcję TRUSTWORTHY na ON.

.NET CLR Exceptions – licznik Excetions/Sec ostrzega przed problemami w kodzie aplikacji. Jeżeli wartość ta jest większa niż normalnie (niektórzy programiści używają wyjątków podczas normalnej pracy programu) to powinien być to bardzo wczesny sygnał ostrzegawczy o konieczności rewizji kodu.

.NET CLR Loading – SQL Server izoluje kod pomiędzy bazami danych za pomocą domen aplikacji (App Domain) – koncepcji znanej z CLR. Liczniki zawarte w tej kategorii umożliwiają określenie jak wiele domen oraz assembly jest załadowanych do pamięci.

.NET CLR Memory – dostarcza dokładnych informacji o trzech typach pamięci CLR oraz mechanizmie automatycznego czyszczenia pamięci. Liczniki te monitorują użycie pamięci przez CLR i w przypadku zbyt dużego jej zużycia należałoby się zastanowić nad rewizją kodu zawartego w assembly.

.NET Data Provider for SQL Server – dostarcza informacji o liczbie połączeń i odłączeń na sekundę. Informacja ta jest bardzo użyteczna, ponieważ umożliwia przechwycenie aktywności na poziomie bazy danych (dla kodu, który łączy się do SQL Server zamiast zwracać wartość przez parametry).

Informacja o załadowanych assembly (należy uruchomić na wybranej bazie danych):

SELECT  a.[name] AS [Nazwa assmebly],
        ad.[appdomain_name] AS [Nazwa domeny],
        clr.[load_time] AS [Czas zaladowania ]
FROM    sys.dm_clr_loaded_assemblies AS clr
        INNER JOIN sys.assemblies AS a 
        ON clr.assembly_id = a.assembly_id
        INNER JOIN sys.dm_clr_appdomains AS ad 
  ON clr.appdomain_address = ad.appdomain_address

Informacja o zużyciu pamięci przez CLR w SQL Server:

SELECT  mo.[type] AS [Typ obiektu],
        SUM(mo.pages_allocated_count * mo.page_size_in_bytes / 1024) 
          AS [Aktualna wartosc (kB)],
        SUM(mo.max_pages_allocated_count * mo.page_size_in_bytes / 1024) 
          AS [Maksymalna wartosc (kB)]
FROM    sys.dm_os_memory_objects AS mo
WHERE   mo.[type] LIKE '%clr%'
GROUP BY mo.[type]
ORDER BY mo.[type]

Informacja o czasach oczekiwania dla obiektów CLR:

SELECT  wait_type AS [Typ oczekiwania],
        waiting_tasks_count AS [Liczba oczekujacych zadan],
        wait_time_ms AS [Czas oczekiwania (ms)],
        max_wait_time_ms AS [Maksymalny czas oczekiwania (ms)]
FROM    sys.dm_os_wait_stats
WHERE   wait_type LIKE '%clr%'

Informacja o licznikach wydajności związanych z CLR:

SELECT  object_name AS [Nazwa obiektu],
        counter_name AS [Nazwa licznika],
        cntr_value AS [Wartosc],
        cntr_type AS [Typ licznika]
FROM    sys.dm_os_performance_counters
WHERE   counter_name LIKE '%CLR%'

Informacja o wydajności zapytań oraz czasie przeznaczonym na wykonywanie zapytań wykorzystujących CLR:

SELECT  qs.*,
        s.text
FROM    sys.dm_exec_query_stats AS qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS s
WHERE   qs.total_clr_time > 0
ORDER BY qs.total_clr_time DESC

Informacja o aktualnych żądaniach w stosunku do obiektów CLR:

SELECT  session_id,
        request_id,
        start_time,
        status,
        command,
        database_id,
        wait_type,
        wait_time,
        last_wait_type,
        wait_resource,
        cpu_time,
        total_elapsed_time,
        nest_level,
        executing_managed_code
FROM    sys.dm_exec_requests
WHERE   executing_managed_code = 1

Duża ilość danych pobierana do biblioteki CLR. Do operacji na zbiorach danych najlepiej nadają się zapytania T-SQL uruchamiane w silniku baz danych. Należy pamiętać o jeszcze jednym czynniku - operacje wykonywane na zbiorach w SQL Server zamieniane są na operacje na kursorach w aplikacjach pisanych w bibliotekach zewnętrznych.

Długotrwałe odwołania do zewnętrznych bibliotek. Bardzo ważnym jest upewnienie się, iż działania użytkownika nie spowodują uruchomienia wywołań do zewnętrznych aplikacji lub API. Wpływ takich wywołań jest najsilniejszy w przypadku funkcji definiowanych przez użytkownika (UDF), które mogą być wywoływane dla każdego wiersza zwracanego w wyniku działania zapytania. Wywołanie zewnętrznej funkcji, które może zająć ok. 1 sekundy na wiersz będzie nieporozumieniem w przypadku konieczności powtórzenia tego kilka tysięcy razy.

Nadużywanie w tworzeniu typów UDT oraz agregatów UDA. Podczas tworzenia własnego typu danych należy być świadomym ograniczeń wynikających ze sposobu przechowywania tego typu na stronie z danymi. Najsilniejsze ograniczenie nakładane na UDT mówi, że jego wielkość musi być mniejsza niż 8kB. Drugim istotnym ograniczeniem, o którym należy pamiętać, jest fakt iż cały typ danych lub agregat musi zostać wczytany i przepisany w momencie jego aktualizacji (przez komendę UPDATE). Wynika z tego, iż nie można odczytywać tylko poszczególnych składników typu - jest on traktowany jako spójny obiekt.

UDA i tworzenie raportów w czasie rzeczywistym. Agregaty tworzone przez użytkownika nie mogą być używane w połączeniu z indeksowanymi widokami w SQL Server, nie jest więc możliwe automatyczne wstępne preagregowanie danych dla poprawy wydajności dla raportów wykonywanych w czasie rzeczywistym, co znacznie poprawiłoby ich wydajność.

Kompatybilność z poprzednią wersją SQL Server. Jeżeli aplikacja musi wspierać poprzednie wersje SQL Server to nie można stosować omawianej w tym artykule funkcjonalności.

Wykorzystanie bibliotek .NET Framework oraz środowiska programistycznego Visual Studio. W roku 2005 wydano zarówno SQL Server jak i Visual Studio .NET. Przed aplikacjami bazodanowymi otwarły się zupełnie nowe możliwości wynikające z zastosowania bibliotek .NET Framework. Dostęp do funkcjonalności w nich zawartych jest możliwy bez konieczności posiadania wysokich uprawnień na poziomie kodu.

Zastępowanie rozszerzonych procedur składowanych (XPs). Poprzednio dostęp do zewnętrznych zasobów odbywał się przez wywołanie rozszerzonych procedur składowanych lub procedur z rodziny sp_OA*. Te metody niosą ze sobą olbrzymie ryzyko dla stabilności serwera w związku z uruchamianiem kodu niezarządzanego. Rekomenduje się administratorom baz danych wykorzystanie integracji CLR i SQL Server głównie ze względu na aspekty bezpieczeństwa:

Powyższe rozważania nie są prawdziwe, jeżeli assembly zostanie zarejestrowane w trybie UNSAFE, ponieważ wtedy jest możliwość wywołania zewnętrznego, niezarządzanego kodu.

Zredukowanie ruchu w sieci. Niektóre algorytmy wymagają przesłania dużej ilości danych dla wygenerowania odpowiedniego wyniku. Przesyłanie danych pomiędzy serwerami powoduje zwiększenie przepływu informacji w sieci. Umieszczenie algorytmów wewnątrz bazy danych nie tylko ogranicza ten ruch ale może również poprawić wydajność zauważalną w aplikacji klienckiej. Przykładem takiego algorytmu może być konieczność wykonania specjalistycznych operacji statystycznych, które wymagają otrzymania całego zbioru danych przed rozpoczęciem obliczeń.

Pisanie funkcji ogólnego przeznaczenia. Funkcja ogólnego przeznaczenia jest zdefiniowana następująco:

Przykładem takiej funkcji może być funkcja wyznaczająca odległość pomiędzy dwoma punktami otrzymanymi w wyniku monitorowania ruchu pojazdów. Wewnątrz takiej funkcji przeprowadzane są operacje trygonometryczne – sumy oraz iloczyny. Funkcja jako wynik mogłaby zwracać odległość w jednostce zgodnej z parametrem wejściowym – kilometrach, metrach, milach, jardach itp.

Definiowanie własnych skalarnych typów (UDT). Większość typów danych może być mapowana do modelu relacyjnego, jednak jest wiele przykładów, które nadają się do rozważenia jako nowe typy:

Definiowanie własnego agregatu (UDA). W części systemów wykonanie agregacji typu SUM, AVG, MIN, MAX itd. jest niewystarczające. Wraz z możliwością integracji CLR z SQL Server użytkownik ma możliwość definiowania własnego agregatu, który może być użyty w klauzuli GROUP BY. Przykładem takiego agregatu może być obliczanie transformaty Fouriera lub średniej odległości punktu od określonego miejsca.

Pisanie wydajnych funkcji użytkownika zwracających tablice.

Integracja SQL Server 2005 i CLR z punktu widzenia administratora baz danych, cz. I