Jasne i ciemne strony baz danych

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

Archive for Lipiec 2010

SQL Server – Zabijanie sesji "wiszących" w statusie KILLED/ROLLBACK

Posted by C3PO w dniu 28 lipca 2010


Zdarza się, że z poziomu SQL Servera wykonujemy operacje w systemie operacyjnym Windows i w pewnym momencie zabijamy poleceniem KILL z poziomu SQL Servera sesję, która owe operacje prowadzi. Nierzadko prowadzi to do sytuacji, w której zabita sesja na długo (a czasem w nieskończoność) pozostaje na liście sesji w master.dbo.sysprocesses (cmd = ‚KILLED/ROLLBACK’) lub w widoku dynamicznym sys.dm_exec_requests (command = ‚KILLED/ROLLBACK’).

Przykład:

Wykonujemy zapytania (analogiczne zapytania do DMV i sysprocesses) i oglądamy „wiszącą” sesję o @@SPID = 52:

SELECT 
  r.session_id,
  r.command,
  s.host_process_id
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
ON r.session_id = s.session_id
WHERE r.command = 'KILLED/ROLLBACK';
GO
SELECT
  spid,
  cmd,
  hostprocess
FROM master.dbo.sysprocesses
WHERE cmd = 'KILLED/ROLLBACK';
GO

Wynik:

session_id command          host_name host_process_id
---------- ---------------- --------- ---------------
52         KILLED/ROLLBACK  CLIENT1   3720

spid   cmd              hostname hostprocess
------ ---------------- -------- -----------
52     KILLED/ROLLBACK  CLIENT1  3720

Jak sobie poradzić z taką „wiszącą” sesją? Nie bez przyczyny w zapytaniach wybrałem kolumny host_process_id i hostprocess. Obie pokazują ID procesu (tu – 3720) na maszynie CLIENT1. Aby sesja zniknęła i przestała zatruwać nam życie, należy na wskazanej maszynie zabić proces o wskazanym ID. Jeżeli maszyna (host) to serwer hostujący instancję SQL Server, na której „wisi” sesja, można spróbować użyć procedury rozszerzonej xp_cmdshell (o ile jest włączona i o ile z poziomu SQL Servera da się na tej maszynie zabić proces w systemie Windows – kwestia uprawnień):

EXEC master.dbo.xp_cmdshell 'taskkill /PID 3720';

Bardziej jednak prawdopodobne, że będzie potrzebna interwencja administratora Windows (domeny?). W każdym razie, przynajmniej wiadomo, co trzeba zrobić, by uniknąć operacji, która biednemu DBA wydaje się często jedynym ratunkiem – restartu usługi SQL Servera.

Reklamy

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

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

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

SQL Server – Narzucamy konwencję nazewniczą obiektów

Posted by C3PO w dniu 27 lipca 2010


Dobrą praktyką stosowaną w wielu firmach jest narzucenie programistom i administratorom pracującym z bazami danych konwencji nazewniczych dotyczących nazw obiektów w bazach danych. Jedną z częściej stosowanych konwencji jest wyróżnianie obiektów według ich typów przez stosowanie w nazwach odpowiednich prefiksów. I tak, widoki często nazywamy z prefiksem „v”, a dla funkcji niektórzy stosują prefiks „ufn”. Jaka by nie była konwencja, trzeba pomyśleć, jak narzucić ją zapominalskim.

W SQL Server 2008 do tego zadania świetnie nadają się wyzwalacze DDL (DDL triggers). Przykładowy wyzwalacz narzucający pewną, dość ograniczoną, konwencję nazewniczą, może wyglądać tak:

IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'tr_DDL_NamingTemplateProcedural')
  DROP TRIGGER tr_DDL_NamingTemplateProcedural ON DATABASE;
GO
CREATE TRIGGER tr_DDL_NamingTemplateProcedural
ON DATABASE
FOR
  CREATE_PROCEDURE,
  CREATE_VIEW,
  CREATE_FUNCTION,
  CREATE_TRIGGER
AS
BEGIN
  DECLARE
    -- szablony nazw
    @ProcedureTemplate sysname = 'usp[_]%',
    @FunctionTemplate sysname = 'ufn[_]%',
    @TriggerTemplate sysname = 'tr[_]%',
    @ViewTemplate sysname = 'v[_]%',
    --
    @NamingTemplate sysname,
    @EventData xml,
    @ObjectType sysname,
    @ObjectName sysname,
    @Message nvarchar(1000);
  SET
    @EventData = EVENTDATA();
  SELECT
    @ObjectName =
      @EventData.value('(/EVENT_INSTANCE/ObjectName/text())[1]', 'sysname'),
    @ObjectType =
      @EventData.value('(/EVENT_INSTANCE/ObjectType/text())[1]', 'sysname');
  SET
    @NamingTemplate =
      CASE @ObjectType
        WHEN 'PROCEDURE' THEN @ProcedureTemplate
        WHEN 'FUNCTION' THEN @FunctionTemplate
        WHEN 'TRIGGER' THEN @TriggerTemplate
        WHEN 'VIEW' THEN @ViewTemplate
        ELSE '%'
      END;
  SET
    @Message = 'Nazwa obiektu niezgodna z narzuconą konwencją nazewniczą w bazie (' +
                @ObjectType + ' LIKE ''' + REPLACE(@NamingTemplate, '%', '%%') + ''')';

  IF @ObjectName NOT LIKE @NamingTemplate BEGIN
      RAISERROR(@Message, 16, 1);
      ROLLBACK;
  END;
END;
GO

Wyzwalacz ten narzuca w bieżącej bazie danych następującą konwencję: nazwy procedur muszą zaczynać się od prefiksu usp_, nazwy funkcji – od prefiksu ufn_, nazwy wyzwalaczy – od prefiksu tr_, zaś nazwy widoków – od prefiksu v_. Wyzwalacz jest uruchamiany w przypadku próby stworzenia któregokolwiek z wymienionych obiektów w bazie danych, wydobywa z pomocą funkcji EVENTDATA i metody value (metoda typu danych XML) niezbędne informacje  – nazwę obiektu oraz jego typ, sprawdza, czy nazwa jest zgodna z konwencją, a jeśli napotka niezgodność, wycofuje transakcję tworząca obiekt zwracając przy tym stosowny komunikat.

I tak, próba utworzenia przykładowej procedury:

CREATE PROC sp_SampleProc AS SELECT 1;

zakończy się zwróceniem następującego komunikatu błędu:

Msg 50000, Level 16, State 1, Procedure tr_DDL_NamingTemplateProcedural, Line 39
Nazwa obiektu niezgodna z narzuconą konwencją nazewniczą w bazie
(PROCEDURE LIKE 'usp[_]%')
Msg 3609, Level 16, State 2, Procedure sp_SampleProc, Line 1
The transaction ended in the trigger. The batch has been aborted.

Ktoś mógłby powiedzieć, że taki efekt można osiągnąć stosując mechanizm Policy-Based Management. Prawda. Ale wyzwalacz DDL ma kilka zalet w porównaniu ze wspomnianym mechanizmem:

  • wyzwalacza DDL można użyć już w SQL Server 2005 (wymaga odrobiny zmiany kodu przy deklaracjach zmiennych),
  • w przypadku wyzwalacza DDL mamy pełną kontrolę nad tym, co i jak jest sprawdzane oraz jakie zwracamy komunikaty (sami piszemy kod), jesteśmy też w stanie zaimplementować bardziej skomplikowaną logikę,
  • w przypadku Policy-Based Management trzeba dobrze poznać szablony (facets), bo niektóre z nich nie działają w trybie On Change – Prevent (spróbujcie założyć polisę na nazwę widoku z użyciem szablonu View, to zrozumiecie, o co chodzi).

I jeszcze mała uwaga. W SQL Server istnieje niestety możliwość zmiany nazwy obiektu przy użyciu procedury sp_rename. Dokonywanie takich zmian jest bardzo złym pomysłem. Jednak na SQL Server 2008 / 2008 R2 administrator może przed takimi pomysłami zabezpieczyć się kolejnym wyzwalaczem DDL:

IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'tr_DDL_DenyRename')
  DROP TRIGGER tr_DDL_DenyRename ON ALL SERVER;
GO
CREATE TRIGGER tr_DDL_DenyRename
ON ALL SERVER
FOR RENAME
AS
BEGIN
  RAISERROR('Zmiany nazw obiektów są zabronione.', 16, 1);
  ROLLBACK;
END;
GO

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

SQL Server – Zależności między obiektami w bazach danych

Posted by C3PO w dniu 25 lipca 2010


Śledzenie zależności między obiektami w bazach danych w SQL Server to ostatnio częsty temat na forum portalu WSS.pl (np. patrz tu lub tu). Udało mi się w ramach tych wątków popełnić jedno drobne niedomówienie sprawnie wychwycone i sprostowane przez Marka Adamczuka (w końcu SQL Server MVP, więc nic dziwnego, że prawdziwą prawdę znał / zbadał i opisał:-)). Dlatego postanowiłem napisać notkę ku pamięci swojej i czytających mojego bloga poświęconą właśnie wspomnianym zależnościom między obiektami – nazwijmy je w skrócie „zależnościami”.

SQL Server 2000

Do badania zależności na SQL Server 2000 wykorzystajmy taki skrypt:

USE tempdb;
GO
-- sprzątamy
IF OBJECT_ID('dbo.p1', 'P') IS NOT NULL
  DROP PROC dbo.p1;
IF OBJECT_ID('dbo.p2', 'P') IS NOT NULL
  DROP PROC dbo.p2; 
IF OBJECT_ID('dbo.v1', 'V') IS NOT NULL
  DROP VIEW dbo.v1;
IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL
  DROP TABLE dbo.t1;
GO
-- tworzymy obiekty
CREATE TABLE dbo.t1 (c1 int NOT NULL, c2 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
CREATE PROC dbo.p1
AS
SELECT * FROM dbo.v1;
EXEC dbo.p2;
GO
-- sprawdzamy zależności (1)
SELECT
  OBJECT_NAME(id) AS obj,
  OBJECT_NAME(depid) AS depobj,
  *
FROM dbo.sysdepends
WHERE id IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO
-- zmieniamy widok
ALTER VIEW dbo.v1
AS
SELECT c1 FROM dbo.t1
GO
ALTER PROC dbo.p2
AS
-- zmiana
GO
-- sprawdzamy zależności (2)
SELECT
  OBJECT_NAME(id) AS obj,
  OBJECT_NAME(depid) AS depobj,
  *
FROM dbo.sysdepends
WHERE id IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO
-- sprzątamy
IF OBJECT_ID('dbo.p1', 'P') IS NOT NULL
  DROP PROC dbo.p1;
IF OBJECT_ID('dbo.p2', 'P') IS NOT NULL
  DROP PROC dbo.p2; 
IF OBJECT_ID('dbo.v1', 'V') IS NOT NULL
  DROP VIEW dbo.v1;
IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL
  DROP TABLE dbo.t1;
GO
-- tworzymy obiekty
CREATE TABLE dbo.t1 (c1 int NOT NULL, c2 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
CREATE PROC dbo.p1
AS
SELECT * FROM dbo.v1;
EXEC dbo.p2;
GO
-- usuwamy widok, procedurę i tabelę
DROP VIEW dbo.v1;
DROP PROC dbo.p2; 
DROP TABLE dbo.t1;
GO
CREATE TABLE dbo.t1 (c1 int NOT NULL, c2 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
-- sprawdzamy zależności (3)
SELECT
  OBJECT_NAME(id) AS obj,
  OBJECT_NAME(depid) AS depobj,
  *
FROM dbo.sysdepends
WHERE id IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO

Komentarz:

  • tworzymy tabelę t1,
  • tworzymy widok v1 używający tabeli t1,
  • tworzymy „pustą” procedurę p2,
  • tworzymy procedurę p1 używającą widoku v1 i procedury p2,
  • zależności między obiektami badamy używając tabeli systemowej dbo.sysdepends,
  • badamy wpływ operacji ALTER wykonanych na widoku v1 i procedurze p2 na zależności,
  • badamy wpływ operacji DROP i CREATE wykonanych na widoku v1, procedurze p2 i tabeli t1 na zależności.

Obserwacje:

  • ALTER na widoku v1 powoduje „zgubienie” zależności między widokiem v1 a procedurą p1 (choć procedura nadal używa widoku),
  • ALTER na procedurze p2 powoduje „zgubienie” zależności między procedurą p2 a procedurą p1 (choć p1 nadal używa p2),
  • DROP i CREATE wykonane na widoku v1 także powodują „zgubienie” zależności między widokiem v1 a procedurą p1 (choć procedura nadal używa widoku),
  • DROP i CREATE wykonane na widoku p2 także powodują „zgubienie” zależności między procedurą p2 a procedurą p1 (choć p1 nadal używa p2).

SQL Server 2005

Do badania zależności na SQL Server 2005 wykorzystajmy taki skrypt:

USE tempdb;
GO
-- sprzątamy
IF OBJECT_ID('dbo.p1', 'P') IS NOT NULL
  DROP PROC dbo.p1;
IF OBJECT_ID('dbo.p2', 'P') IS NOT NULL
  DROP PROC dbo.p2; 
IF OBJECT_ID('dbo.v1', 'V') IS NOT NULL
  DROP VIEW dbo.v1;
IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL
  DROP TABLE dbo.t1;
GO
-- tworzymy obiekty
CREATE TABLE dbo.t1 (c1 int NOT NULL, c2 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
CREATE PROC dbo.p1
AS
SELECT * FROM dbo.v1;
EXEC dbo.p2;
GO
-- sprawdzamy zależności (1)
SELECT
  OBJECT_NAME([object_id]) AS obj,
  OBJECT_NAME(referenced_major_id) AS refobj,
  *
FROM sys.sql_dependencies
WHERE [object_id] IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO
-- zmieniamy widok i procedurę
ALTER VIEW dbo.v1
AS
SELECT c1 FROM dbo.t1
GO
ALTER PROC dbo.p2
AS
-- zmiana
GO
-- sprawdzamy zależności (2)
SELECT
  OBJECT_NAME([object_id]) AS obj,
  OBJECT_NAME(referenced_major_id) AS refobj,
  *
FROM sys.sql_dependencies
WHERE [object_id] IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO
-- sprzątamy
IF OBJECT_ID('dbo.p1', 'P') IS NOT NULL
  DROP PROC dbo.p1;
IF OBJECT_ID('dbo.p2', 'P') IS NOT NULL
  DROP PROC dbo.p2; 
IF OBJECT_ID('dbo.v1', 'V') IS NOT NULL
  DROP VIEW dbo.v1;
IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL
  DROP TABLE dbo.t1;
GO
-- tworzymy obiekty
CREATE TABLE dbo.t1 (c1 int NOT NULL, c2 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
CREATE PROC dbo.p1
AS
SELECT * FROM dbo.v1;
EXEC dbo.p2;
GO
-- usuwamy widok i tabelę
DROP VIEW dbo.v1;
DROP PROC dbo.p2;
DROP TABLE dbo.t1;
GO
CREATE TABLE dbo.t1 (c1 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
-- sprawdzamy zależności (3)
SELECT
  OBJECT_NAME([object_id]) AS obj,
  OBJECT_NAME(referenced_major_id) AS refobj,
  *
FROM sys.sql_dependencies
WHERE [object_id] IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO

Komentarz:

  • tworzymy tabelę t1,
  • tworzymy widok v1 używający tabeli t1,
  • tworzymy „pustą” procedurę p2,
  • tworzymy procedurę p1 używającą widoku v1 i procedury p2,
  • zależności między obiektami badamy używając widoku systemowego sys.sql_dependencies,
  • badamy wpływ operacji ALTER wykonanych na widoku v1 i procedurze p2 na zależności,
  • badamy wpływ operacji DROP i CREATE wykonanych na widoku v1, procedurze p2 i tabeli t1 na zależności.

Obserwacje:

  • ALTER na widoku v1 powoduje „zgubienie” zależności między widokiem v1 a procedurą p1,
  • ALTER na procedurze p2 NIE powoduje „zgubienia” zależności między procedurą p2 a procedurą p1, 
  • DROP i CREATE powodują „zgubienie” zależności między widokiem v1 a procedurą p1 (choć procedura nadal używa widoku).

SQL Server 2008 / 2008 R2

Do badania zależności na SQL Server 2008 / 2008 R2 wykorzystajmy taki skrypt:

USE tempdb;
GO
-- sprzątamy
IF OBJECT_ID('dbo.p1', 'P') IS NOT NULL
  DROP PROC dbo.p1;
IF OBJECT_ID('dbo.p2', 'P') IS NOT NULL
  DROP PROC dbo.p2; 
IF OBJECT_ID('dbo.v1', 'V') IS NOT NULL
  DROP VIEW dbo.v1;
IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL
  DROP TABLE dbo.t1;
GO
-- tworzymy obiekty
CREATE TABLE dbo.t1 (c1 int NOT NULL, c2 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
CREATE PROC dbo.p1
AS
SELECT * FROM dbo.v1;
EXEC dbo.p2;
GO
-- sprawdzamy zależności (1)
SELECT
  OBJECT_NAME([referencing_id]) AS referencing_object,
  *
FROM sys.sql_expression_dependencies
WHERE [referencing_id] IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO
-- zmieniamy widok i procedurę
ALTER VIEW dbo.v1
AS
SELECT c1 FROM dbo.t1
GO
ALTER PROC dbo.p2
AS
-- zmiana
GO
-- sprawdzamy zależności (2)
SELECT
  OBJECT_NAME([referencing_id]) AS referencing_object,
  *
FROM sys.sql_expression_dependencies
WHERE [referencing_id] IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO
-- sprzątamy
IF OBJECT_ID('dbo.p1', 'P') IS NOT NULL
  DROP PROC dbo.p1;
IF OBJECT_ID('dbo.p2', 'P') IS NOT NULL
  DROP PROC dbo.p2; 
IF OBJECT_ID('dbo.v1', 'V') IS NOT NULL
  DROP VIEW dbo.v1;
IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL
  DROP TABLE dbo.t1;
GO
-- tworzymy obiekty
CREATE TABLE dbo.t1 (c1 int NOT NULL, c2 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
CREATE PROC dbo.p1
AS
SELECT * FROM dbo.v1;
EXEC dbo.p2;
GO
-- usuwamy widok i tabelę
DROP VIEW dbo.v1;
DROP PROC dbo.p2;
DROP TABLE dbo.t1;
GO
CREATE TABLE dbo.t1 (c1 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
-- sprawdzamy zależności (3)
SELECT
  OBJECT_NAME([referencing_id]) AS referencing_object,
  *
FROM sys.sql_expression_dependencies
WHERE [referencing_id] IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO

Komentarz:

  • tworzymy tabelę t1,
  • tworzymy widok v1 używający tabeli t1,
  • tworzymy „pustą” procedurę p2,
  • tworzymy procedurę p1 używającą widoku v1 i procedury p2,
  • zależności między obiektami badamy używając widoku systemowego sys.sql_expression_dependencies,
  • badamy wpływ operacji ALTER wykonanych na widoku v1 i procedurze p2 na zależności,
  • badamy wpływ operacji DROP i CREATE wykonanych na widoku v1, procedurze p2 i tabeli t1 na zależności.

Obserwacje:

  • żadna operacja nie spowodowała „zgubienia” zależności między obiektami,
  • w przeciwieństwie do tabeli dbo.sysdepends z SQL Server 2000 i widoku sys.sql_dependencies z SQL Server 2005, widok sys.sql_expression_dependencies nie pokazuje zależności per kolumna, a jedynie zwraca informację „obiekt wykorzystuje obiekt”,
  • do wyświetlania zależności z dokładnością do kolumny w SQL Server 2008 / 2008 R2 wykorzystujemy dwie dynamiczne funkcje: sys.dm_sql_referenced_entities (obiekty wykorzystywane przez wskazany obiekt) i sys.dm_sql_referencing_entities (obiekty odwołujące się do wskazanego obiektu).

Podsumowanie

  • Na SQL Server 2000 zależności między obiektami są gubione „przy byle okazji”.
  • Na SQL Server 2005 ALTER na widoku spowodował „zgubienie” zależności między owym widokiem, a procedurą, która z widoku korzystała. Wygląda na to, że problematyczne jest śledzenie zależności na poziomie szczegółowości sięgającym kolumn (w sys.sql_dependencies dla każdej kolumny, od której zależny jest obiekt, trzymany jest jeden wiersz).
  • SQL Server 2008 (i 2008 R2) potrafi poradzić sobie z trzymaniem zależności między obiektami nawet, gdy obiekty używane przez inne obiekty zostaną usunięte i odtworzone na nowo pod nowym OBJECT_ID. Problem został rozwiązany w sposób bardzo prosty – widok sys.sql_expression_dependencies przechowuje OBJECT_ID tylko dla obiektu odwołującego się do innego obiektu, a dla obiektu „używanego” trzymana jest pełna nazwa (czteroczłonowa! z dokładnością nawet do nazwy serwera, jeżeli zależność jest między obiektami z dwóch instancji SQL Servera).
  • Do pełni szczęścia brakuje w zasadzie tylko przechowywania metadanych uwzględniających zależności, w skład których wchodzą obiekty systemowe. Cóż, może w SQL Server 2011 Microsoft wymyśli koło na nowo (w SQL Server 2000 namiastkę takiej funkcjonalności mieliśmy)… :-)

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

SQL Server 2008 – Szybkie tworzenie polis

Posted by C3PO w dniu 22 lipca 2010


Ostatnio zdarzyło mi się parę razy użyć mechanizmu Policy-Based Management dostępnego w SQL Server 2008 (i 2008 R2). Zacząłem eksperymentować i szukać wszelkich opcji, które umożliwiłyby w miarę szybkie i sprawne tworzenie polis takiemu leniwemu DBA, jak ja :-)

Jedną z takich opcji jest opcja dostępna w menu kontekstowym okna Object Explorer w Management Studio. Klikamy na przykład prawym przyciskiem myszy na nazwie instancji SQL Servera i wybieramy opcję Facets.

Facets

Pojawia się okno właściwości obiektu (w tym przypadku serwera) w kontekście wybranego szablonu (pole Facet). A na samym dole okna… Tada! Przycisk Export Current State as Policy :-)

Export as policy

Klikamy, zapisujemy polisę do pliku XML albo od razu na instancję SQL Server, otwieramy świeżutko utworzoną polisę, edytujemy. Czysta przyjemność! A wszystko w oparciu o przygotowany wzorzec serwera, bazy, obiektu w bazie danych (można sprawdzić, że pozycja Facets pojawia się w menu kontekstowym po kliknięciu w zasadzie na cokolwiek w oknie Object Explorer).

Nie powiem, zabawka w sam raz dla takich leniuszków, jak ja. Łatwiej zrobić polisę opartą o nieco bardziej rozbudowany warunek i usuwać zbędne właściwości z warunku, niż tworzyć wszystko rękami w pocie czoła ;-)

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

SQL Server – Ile czego w buforze danych?

Posted by C3PO w dniu 19 lipca 2010


Czy pracując z SQL Server 2005/2008/2008 R2 można dowiedzieć się, która baza danych zajmuje najwięcej pamięci w buforze danych (buffer cache)? Spróbujmy użyć do tego celu widoku dynamicznego sys.dm_os_buffer_descriptors, który przechowuje jeden rekord dla każdej 8-kilobajtowej strony przechowywanej w buforze danych.

SELECT
  CASE
    WHEN database_id = 32767 THEN 'mssqlsystemresource'
    ELSE DB_NAME(database_id)
  END AS [Database],
  CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [In buffer cache (MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY 2 DESC;

Wynik:

Database                     In buffer cache (MB)
---------------------------- --------------------
AdventureWorks2008R2         19.59
mssqlsystemresource          13.13
master                       3.30
msdb                         2.97
ReportServer$SQL2008R2       2.81
ReportServer$SQL2008R2TempDB 2.12
AdventureWorks               1.37
tempdb                       1.20
AdventureWorksDW2008R2       1.03
AdventureWorksDW             0.95
AdventureWorksLT             0.94
AdventureWorksLT2008R2       0.94
model                        0.50
...

Tu ciekawostka – pojawia nam się baza niewidoczna w widoku sys.databases – mssqlsystemresource (nazwałem ją tak od nazwy pliku danych), czyli baza systemowa, w której Microsoft ukrył przed użytkownikami (użyszkodnikami?) tabele systemowe.

Idąc dalej, czy można dla wybranej bazy sprawdzić, które obiekty zajmują w tym buforze najwięcej miejsca? Czemu nie? Wystarczy dołożyć kilka złączeń (żeby dowiedzieć się, jakie obiekty rezydują w buforze dołączamy widoki systemowe sys.allocation_units i – w chytry sposób – sys.partitions).

USE AdventureWorks2008R2;
GO
SELECT
  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' +
  QUOTENAME(OBJECT_NAME(p.object_id)) AS Object,
  CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [In buffer cache (MB)]
FROM sys.dm_os_buffer_descriptors AS d
INNER JOIN sys.allocation_units AS u
ON d.allocation_unit_id = u.allocation_unit_id
INNER JOIN sys.partitions AS p
ON (u.type IN (1,3) AND u.container_id = p.hobt_id)
OR (u.type = 2 AND u.container_id = p.partition_id)
WHERE d.database_id = DB_ID()
GROUP BY QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' +
  QUOTENAME(OBJECT_NAME(p.object_id))
ORDER BY 2 DESC;

Wynik:

Object                             In buffer cache (MB)
---------------------------------- --------------------
[sys].[sysobjvalues]               12.70
[Sales].[SalesOrderDetail]         9.68
[Sales].[SalesOrderHeader]         5.35
[sys].[sysrowsets]                 4.25
[sys].[syscolpars]                 3.55
[sys].[sysschobjs]                 2.29
[sys].[sysrscols]                  1.95
[sys].[sysallocunits]              1.32
[sys].[sysmultiobjrefs]            1.15
[sys].[sysftstops]                 0.95
[sys].[sysiscols]                  0.93
[sys].[syssingleobjrefs]           0.92
...

A teraz zadanie domowe – zmodyfikuj powyższe zapytanie, by zwracało ilość megabajtów zajętą przez każdy indeks (sprawdź, które indeksy zajmują w buforze najwięcej miejsca). To nie może być trudne :-)

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 – Wielki Mit: porządek wierszy w klastrowanej tabeli

Posted by C3PO w dniu 13 lipca 2010


Dzisiaj zaskoczył mnie wątek na forum portalu WSS.pl, w którym okazało się, że kilku uczestników dyskusji żyło w przeświadczeniu, że zapytanie do tabeli, w której jest założony indeks klastowany, w przypadku braku jawnego sortowania (klauzuli ORDER BY) zwróci rekordy w kolejności podyktowanej kluczem indeksu klastrowanego. O ile można wymyśleć wyrafinowane kontrprzykłady tej tezy (we wspomnianym wątku warto przeczytać ze zrozumieniem wypowiedź Marcina Szeligi), o tyle Marek Adamczuk podał w trakcie dyskusji przykład, który jest banalny do odtworzenia i o wiele szybciej otwiera oczy osobom, które szerzą wspomniany mit :-)

Weźmy taką tabelę:

IF OBJECT_ID('dbo.SortTest') IS NOT NULL
  DROP TABLE dbo.SortTest;
CREATE TABLE dbo.SortTest (
  id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
  col1 int NOT NULL,
  col2 int NOT NULL
);
GO

Wstawmy do niej trochę rekordów:

INSERT INTO dbo.SortTest (col1, col2)
SELECT number, number + 1
FROM master.dbo.spt_values
WHERE type = 'P' AND number <= 2047
ORDER BY number DESC;
GO

Ważne, by zauważyć, że wartości kolumny col2 maleją wraz ze wzrostem wartości w kolumnie id, która jest kolumną klucza głównego oraz indeksu klastrowanego (przy czym należy pamiętać, że indeks klastrowany a klucz główny to zupełnie dwie różne bajki i to, że akurat w tym przykładzie się pokrywają, to przypadek szczególny).

Weźmy zapytanie:

SELECT col2, id FROM dbo.SortTest;

Zapytanie być może zwróci nam wynik posortowany po kolumnie id (mamy fart! często wcale tak nie będzie, ale do tego dojdziemy za moment).

Teraz dodajmy indeks nieklastrowany na kolumnie col2:

CREATE INDEX IX_SortTest_col2 ON dbo.SortTest (col2);

I ponownie wykonajmy nasz SELECT (wcześniej włączmy pokazywanie graficznego planu wykonania):

SELECT col2, id FROM dbo.SortTest;

Niespodzianka? Wiersze posortowane względem kolumny col2?! :-) Plan wykonania nie pozostawia złudzeń – mamy skan indeksu IX_SortTest_col2 (dość oczywisty wybór optymalizatora – IX_SortTest_col2 to teraz najmniejszy indeks pokrywający zapytanie, czyli zawierający wszystkie kolumny, o które pytamy). I teraz dalej pytanie – czy to gwarantuje, że od teraz nasz SELECT będzie zawsze zwracał wiersze posortowane względem kolumny col2? Niekoniecznie! Zakonotujmy i uznajmy za prawdę objawioną – jeżeli nie używamy w zapytaniu klauzuli ORDER BY, nie możemy oczekiwać żadnego porządku wierszy.

To samo dotyczy sortowania wierszy na siłę w definicjach widoków i funkcji tabelarycznych. Zapomnijmy o takiej opcji! Co prawda (póki co!) działają chwyty typu TOP (baaaaardzo_dużaaaa_liczbaaaa) + ORDER BY, ale jutro mogą przestać działać. Ja sam nie chciałbym używać w swoich bazach danych kodu, o którym nie wiem, czy nie jest wrażliwy na instalację nowego Service Packa do SQL Servera :-) Wystrzegać się zatem takich tricków i sortować po bożemu:

SELECT * FROM Widok ORDER BY KolumnaWidoku;
SELECT * FROM Funkcja ORDER BY KolumnaFunkcji;

Over.

Posted in MS SQL Server | Otagowane: , , , | 1 Comment »

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 »