Monthly Archives: Listopad 2011

WINDOWS FIREWALL – pomocna dłoń przy braku połączenia :)

Dzisiejszym wpisem chciałbym was zachęcić, abyście nie wyłączali FIREWALL-a na swoich serwerach podczas diagnozowania problemów z komunikacją sieciową. Wiem, ze powyższe zdanie może brzmieć całkowicie nieżyciowo, ale WINDOWS FIREWALL może ułatwić waszą pracę przy braku łączności pomiędzy serwerem a maszyną kliencką. Powiem więcej – może on wykonać 90% pracy związanej z diagnozą problemu 🙂

Zacznijmy więc od przykładu problemowej sytuacji:

Załóżmy, że wdrażamy nową aplikację kliencką typu klient-serwer. Na serwerze zainstalowaliśmy silnik bazy danych, do którego aplikacja będzie nawiązywać połączenia. Jesteśmy w posiadaniu odpowiedniego loginu i hasła lecz  niestety nie udaje się.

Jako substytut takiej aplikacji na potrzeby przykładu posłuży mi ODBC Administrator dostępny w zakładce Narzędzia Administracyjne Panelu Sterowania.

W tego typu sytuacjach niezastąpiona jest komenda ping, która w analizowanym przypadku kończy się sukcesem:

Wiemy, że maszyny widzą się a więc co dalej?

Korci by wyłączyć FIREWALL-a na serwerze i sprawdzić czy aplikacja zadziała. Tym razem zrobimy jednak nieco inaczej – posłużymy się firewallem do zdiagnozowania naszego przykładowego problemu.

Na początek przejdźmy za pomocą menu start\narzędzia administracyjne do Windows Firewall with Advanced Security na serwerze z zainstalowaną bazą danych. Następnie kliknijmy prawym przyciskiem w część główną drzewka z lewej strony i wybierzmy opcje „Properties”. Krok ten pokazany jest na rysunku poniżej.

W tym momencie system powinien wyświetlić nam okno z ustawieniami FIREWALL-a. Następnie przejdźmy do odpowiedniego profilu ustawień (w przedstawianym przykładzie jest to profil publiczny) i kliknijmy w przycisk „Customize” z części „Logging” okna.

Zostaniemy przeniesieni do okna ustawień w którym powinniśmy zmienić opcję logowania odrzuconych pakietów z wartości domyślnej (NIE) na wartość TAK. Miejsce, w którym należy tego dokonać zaznaczyłem na czerwono na rysunku poniżej.

W tym momencie WINDOWS FIREWALL powinien zacząć logować wszystkie odrzucone pakiety. Powinniśmy spróbować ponownie połączyć się naszą aplikacja z serwerem i sprawdzić co jest przyczyną niepowodzenia. Po nieudanej próbie połączenia możemy przejść na naszym serwerze do zakładki Windows Firewall with Advanced Security i wybrać część związaną z monitorowaniem. Za jej pomocą można dostać się do logów zapory sieciowej, która będzie zawierać wyjaśnienie zaistniałego problemu.

W przedstawionym przeze mnie przykładzie problemem okazał się zamknięty port 1433, na którym nasłuchuje SQL Server. Log z którego to wyczytałem przedstawiam poniżej:

Podsumowanie

Podczas diagnozowania problemów z połączeniem sieciowym polecam użycie FIREWALL-a. Włączenie opcji logowania pozwala nam zdiagnozować nie tylko zaistniały problem (brak odpowiedniej reguły w zaporze), ale także namierzyć szczegóły potrzebne do jego naprawy. W przedstawionym przypadku możemy wyczytać z logów, że komunikacja jest nawiązywana na porcie 1433 TCP, a więc stworzenie zasady pozwalającej na ruch sieciowy nie powinno stanowić problemu. Gdybyśmy wyłączyli FIREWALL-a zamiast użyć logowania zdarzeń podczas diagnozy to dowiedzielibyśmy się, że przyczyną problemu jest zapora sieciowa, ale nadal nie wiedzielibyśmy jaką regułę musimy ustawić by aplikacja działała poprawnie.

Reklamy

Sekwencje w SQL Server 2012 – gdzie się podział zysk na IO?

W moim poprzednim poście na temat sekwencji przytoczyłem następujący fragment BOOKS ONLINE mówiący o zysku wydajnościowym przy użyciu opcji CACHE w sekwencjach:

Increases performance for applications that use sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers.

Ponieważ użycie statystyk wejścia/wyjścia nie wykazało różnic pomiędzy sekwencją z ustawioną opcją CACHE i bez używania jej,  to kończąc przytaczany post obiecałem wyjaśnić tę zagadkę. Odpowiedzią na nią mam zamiar zająć się dzisiaj 🙂

Przedstawię zatem swój pomysł 🙂

Pierwszą myślą jaka przyszła mi do głowy odnośnie zysku na operacjach dyskowych w trakcie generowania wartości przez sekwencję jest mniejsza ilość zapisanych danych do logu transakcyjnego podczas używania opcji CACHE.

Aby sprawdzić tę tezę posłużę się nieudokumentowaną funkcją fn_dblog, która pozwala na pobranie zawartości logu transakcyjnego w formie tabelarycznej. Składnia tej funkcji jest następująca:

fn_dblog(@StartLSN nvarchar(25), @StopLSN nvarchar(25))

@StartLSN – mówi od jakiej wartości LSN zacząć czytać log   
@StopLSN – na jakiej wartości LSN skończyć czytanie logu

W przypadku, gdy jako parametry StartLSN i StopLSN podamy wartości NULL, to pobrana zostanie cała zawartość logu transakcyjnego używanej bazy.

Rozpocznijmy test

Utwórzmy dwie przykładowe sekwencje – jedną z, a drugą bez opcji CACHE.

-- sekwencja z CACHE
CREATE SEQUENCE dbo.seqTestCache
AS int
START WITH 1
INCREMENT BY 1
CACHE 1000
-- sekwencja bez CACHE
CREATE SEQUENCE dbo.seqTestNoCache
AS int
START WITH 1
INCREMENT BY 1
NO CACHE

Następnie sprawdźmy jaki jest ostatni numer LSN logu transakcyjnego w naszej bazie (dla skrócenia czasu otrzymania wyniku polecam wcześniejsze zrobienie backupu logu).

SELECT MAX([Current LSN]) AS OstatniLSN
FROM fn_dblog(NULL, NULL)

Znając go możemy przejść do właściwej części testu.

Użyjmy sekwencji z opcją CACHE

Pobierzmy kolejną wartość licznika i sprawdźmy, czy LSN się nie zmienił.

SELECT (NEXT VALUE FOR dbo.seqTestCache) AS NumerSekwencji,
   ( SELECT MAX([Current LSN])
    FROM fn_dblog(NULL, NULL) )  AS OstatniLSN

Numery LSN przed i po wywołaniu sekwencji są różne, a więc miał miejsce zapis do logu. Obecnie numer LSN ma wartość „00000286:0000153c:0002”. Wykonajmy nasz kod ponownie:

SELECT (NEXT VALUE FOR dbo.seqTestCache) AS NumerSekwencji,
   ( SELECT MAX([Current LSN])
    FROM fn_dblog(NULL, NULL) )  AS OstatniLSN

LSN nadal wynosi „00000286:0000153c:0002”. Jego wartość jest taka sama jak po pierwszym wykonaniu wsadu. SQL Server użył sekwencji pobierając kolejną wartość, ale log transakcyjny pozostał nietknięty. Użyjmy naszego kodu ponownie, tym razem wykonując go 1000 razy:

SELECT (NEXT VALUE FOR dbo.seqTestCache) AS NumerSekwencji,
   ( SELECT MAX([Current LSN])
    FROM fn_dblog(NULL, NULL) )  AS OstatniLSN
GO 1000

Jak widać dopiero po zapełnieniu się CACHE-u SQL Server dokonał ponownego zapisu. Co więcej ilość wyświetlanych wierszy przez funkcję fn_dblog poczynając od LSN z jakim rozpoczęliśmy test jest niewielka.

-- ostatnie LSN przed testem = 00000286:0000153c:0001
-- każdą z części trzeba zamienić na formę dzisiętną
-- da nam to wartość 646:5436:1
SELECT COUNT(*) - 1 AS IloscNowychWpisow
FROM fn_dblog('646:5436:1', NULL)

Sprawdźmy sekwencję bez użycia CACHE-u

Na początek sprawdźmy aktualny numer LSN:

SELECT MAX([Current LSN]) AS OstatniLSN
FROM fn_dblog(NULL, NULL)

Użyjmy sekwencji bez CACHE-u:

SELECT (NEXT VALUE FOR dbo.seqTestNoCache) AS NumerSekwencji,
   ( SELECT MAX([Current LSN])
    FROM fn_dblog(NULL, NULL) )  AS OstatniLSN

Podobnie jak w przypadku sekwencji z cache wartość LSN podczas generowania pierwszego numeru zmieniła się, teraz wynosi ona „00000286:0000153f:000b”. Użyjmy jej ponownie i sprawdźmy co się stanie:

SELECT (NEXT VALUE FOR dbo.seqTestNoCache) AS NumerSekwencji,
   ( SELECT MAX([Current LSN])
    FROM fn_dblog(NULL, NULL) )  AS OstatniLSN

 

Wartość LSN uległa zmianie, a więc miał miejsce zapis do logu. Upewnijmy się, że  jest to regułą i zrównajmy liczniki obu sekwencji:

SELECT (NEXT VALUE FOR dbo.seqTestNoCache) AS NumerSekwencji,
   ( SELECT MAX([Current LSN])
    FROM fn_dblog(NULL, NULL) )  AS OstatniLSN
GO 1000

SQL Server za każdym użyciem sekwencji bez CACHE-u zapisuje do logu kolejne informacje. Informacji tych jest znacznie więcej niż podczas używania opcji CACHE. Funkcja fn_dblog zwraca w tym przypadku ponad 1000 nowych wierszy:

-- LSN rozpoczęcia testu = 00000286:0000153f:000a
-- każdą z części trzeba zamienić na formę dzisiętną
-- co da nam wartość 646:5439:10
SELECT COUNT(*) -1 AS IloscNowychWpisow
FROM fn_dblog('646:5439:10', NULL)

Podsumowanie

Użycie CACHE-u podczas generowania kolejnych numerów sekwencji powoduje zysk wydajnościowy na operacjach podsystemu dyskowego. Zysk ten występuje na pewno podczas zapisu do logu transakcyjnego. W przypadku wykorzystania omawianej opcji zapis do logu wykonywany jest jedynie podczas generowania pierwszej wartości oraz kolejnych przepełnień CACHE-u (w  testowanym przykładzie co 1000 numerów). Łączna ilość zapisywanych w ten sposób informacji jest znacznie mniejsza.

SQL Server Denali – zastosowanie sekwencji a wydajność

Wraz z ukazaniem się SQL Server 2012 platforma bazodanowa firmy Microsoft wzbogaci się o kolejną funkcjonalność, a mianowicie sekwencje.  Ponieważ sekwencje są obecne od jakiegoś czasu w takich systemach jak ORACLE czy DB2, to ich koncepcja jest powszechnie znana i nie będę jej tu przytaczał. Chciałbym także zaznaczyć, że z  uwagi na to, że mój dzisiejszy wpis nie jest wprowadzeniem do tematu, to tym z was, którzy nie mieli okazji  wypróbować sekwencji w SQL Server polecam krótki artykuł ze strony polskiego TechNet-u:

http://technet.microsoft.com/pl-pl/library/denali-ctp-1-co-nowego-dla-deweloperow-cz-3.aspx

Przejdźmy wiec  do meritum tego wpisu, a mianowicie: Czy zastosowanie sekwencji może mieć wpływ na wydajność?

Do tej pory SQL Server podczas generowania kolejnych wartości opierał się na właściwości IDENTITY. Jednym z pytań, jakie przychodzi na myśl jest to, czy jeżeli zastąpimy IDENTITY sekwencją, to wstawianie wierszy do tabeli będzie szybsze (ewentualnie wolniejsze).

Postanowiłem wykonać test 🙂

Idealnym sprawdzeniem, która funkcji SQL Servera jest szybsza powinno być nadanie wielu kolejnych numerów w jak najkrótszym czasie. Naturalnymi kandydatami do tego zadania wydały mi się operacje masowego ładowania danych. Jedną z takich operacji może być instrukcja INSERT INTO … SELECT.

Utworzyłem więc bazę testową, zrobiłem jej backup i przestawiłem w tryb BULK LOGGED. Następnie sporządziłem prostą sekwencję oraz dwie tabele testowe za pomocą poniższego kodu:

 -- utworzenie sekwencji
CREATE SEQUENCE dbo.seqTestA
AS int
START WITH 1
INCREMENT BY 1
GO
-- utworzenie tabeli z sekwencją
CREATE TABLE dbo.tblTestSeqA (
 id int PRIMARY KEY DEFAULT NEXT VALUE FOR dbo.seqTestA,
 val int
)
GO
-- utworzenie tabeli z kolumną IDENTITY
CREATE TABLE dbo.tblTestIdentA (
 id int PRIMARY KEY IDENTITY(1,1),
 val int
)
GO

Wykonałem pierwszy test – wstawienie kilkuset tysięcy rekordów do obu tabel:

-- włączenie statystyk użycia procesora
SET STATISTICS TIME ON
GO

-- wpisanie danych do tabeli z IDENTITY
INSERT dbo.tblTestIdentA(val)
SELECT m.message_id
FROM sys.messages m

-- wpisanie danych do tabeli z sekwencją
INSERT dbo.tblTestSeqA(val)
SELECT m.message_id
FROM sys.messages m

Wynik tego testu przedstawiam poniżej:

Statystyki użycia procesora (CPU time) są dla pierwszej z tabel większe.

Czyżby sekwencja była bardziej wydajna?

Powtarzałem test kilkukrotnie czyszcząc tabelę instrukcją TRUNCATE i restartując sekwencję, za każdym razem sekwencja ta stawała się coraz szybsza. Domyślając się od czego może zależeć otrzymany wynik sprawdziłem dane zwracane przez widok katalogowy pokazujący właściwości sekwencji:

SELECT name, is_cached, cache_size
FROM sys.sequences

No tak, dane w sekwencji są domyślne cache-owane, nie znamy jednak wielkości tego cache-u. Odnosząc się do BOOKS ONLINE można przeczytać następujące stwierdzenia odnośnie działania opcji CACHE:

Increases performance for applications that use sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers.

If the cache option is enabled without specifying a cache size, the Database Engine will select a size. However, users should not rely upon the selection being consistent. Microsoft might change the method of calculating the cache size without notice.

Wykonajmy więc drugi test 🙂

Utwórzmy dwie kolejne sekwencje. W pierwszej z nich ustawmy opcję CACHE z odpowiednio dużą wartością a w drugiej wyłączmy ją wpisując „NO CACHE”. Zadeklarujmy kolejne tabele i sprawdźmy wydajność.  Dodatkowo możemy włączyć statystyki wejścia/wyjścia, mając nadzieję, że dzięki temu uda nam się coś więcej zaobserwować.

Deklaracja obiektów:

-- utworzenie sekwencji z cache
CREATE SEQUENCE dbo.seqTestB_cache
AS int
START WITH 1
INCREMENT BY 1
CACHE 10000
GO
-- utworzenie sekwencji bez cache
CREATE SEQUENCE dbo.seqTestB_nocache
AS int
START WITH 1
INCREMENT BY 1
NO CACHE
GO
-- tabela z kolumną IDENTITY
CREATE TABLE dbo.tblTestIdentB (
 id int PRIMARY KEY IDENTITY(1,1),
 val int
)
GO
-- tabela z sekwencją z cache
CREATE TABLE dbo.tblTestSeqB_cache (
 id int PRIMARY KEY DEFAULT NEXT VALUE FOR dbo.seqTestB_cache,
 val int
)
GO
-- tabela z sekwencją bez cache
CREATE TABLE dbo.tblTestSeqB_nocache (
 id int PRIMARY KEY DEFAULT NEXT VALUE FOR dbo.seqTestB_nocache,
 val int
)
GO

Kod testujący:

SET STATISTICS IO ON
SET STATISTICS TIME ON

-- wpisanie danych do tabeli z IDENTITY
INSERT dbo.tblTestIdentB(val)
SELECT m.message_id
FROM sys.messages m

-- wpisanie danych do tabeli z sekwencją z cache
INSERT dbo.tblTestSeqB_cache(val)
SELECT m.message_id
FROM sys.messages m

-- wpisanie danych do tabeli z sekwencją bez cache
INSERT dbo.tblTestSeqB_nocache(val)
SELECT m.message_id
FROM sys.messages m

Wyniki:

Wpisanie danych do tabeli używającej sekwencji opcją CACHE było najszybsze. Na drugim miejscu znalazła się właściwość IDENTITY. Sekwencja bez CACHE-u znalazła się na trzecim miejscu, odstając wyraźnie pod względem zajętego czasu procesora. Co ciekawe analizując statystyki wejścia/wyjścia nie zobaczymy różnicy (przynajmniej mi takowej nie udało się znaleźć :))

Podsumowanie

Jak widać zastosowanie sekwencji może mieć wpływ na wydajność. W zależności od tego czy (i w jaki sposób) użyjemy opcji CACHE generowanie kolejnych numerów jest wolniejsze lub szybsze. Zanim jednak zaczniemy zmieniać struktury tabel zastanówmy się, czy aby zastosowanie IDENTITY z projektowego punktu widzenia nie jest bardziej odpowiednie.

Pozostaje jednak pewne pytanie:

Skąd w BOOKS ONLINE wziął się wpis o zysku w operacjach IO podczas użycia opcji CACHE, skoro statystyki wejścia wyjścia podczas przeprowadzonego testu tego nie wykazały?

Jeżeli dodatkowe testy potwierdzą tezę, która przyszła mi do głowy, to odpowiedź na tę zagadkę przedstawię w kolejnym poście 🙂

Materiały z mojej sesji na temat indeksów w SQL Server

9 listopada w ramach spotkania Trójmiejskiej Zawodowej Grupy .Net miałem możliwość przeprowadzenia sesji na temat indeksów w SQL Server.

Slajdy z tej prezentacji zamieściłem w zakładce Materiały do pobrania, tak więc jeśli najdzie was ochota by je przejrzeć – zapraszam 🙂

Listopadowe spotkanie Trójmiejskiej Zawodowej Grupy .NET

9 listopada o godzinie 18:00 w gdańskiej siedzibie firmy Betacom odbędzie się kolejne spotkanie Trójmiejskiej Zawodowej Grupy .NET.

W terminie tym przewidziane są 3 sesje tematyczne, a mianowicie:

  1. Tips & tricks dla programistów Windows Phone
  2. Strojenie baz danych: Indeksy
  3. Umbraco CMS

Na udział w spotkaniu to zachęcam tym bardziej, że druga z sesji  (Strojenie baz danych: Indeksy)  będzie prezentowana przeze mnie 🙂

O czym podczas niej będę opowiadał?

Postaram się przybliżyć tematykę związaną z tworzeniem indeksów w SQL Server. Poruszę aspekty teoretyczne, takie jak budowa i działanie indeksów  oraz zagadnienia bardziej przyziemne (np. mierzenie wydajności kodu SQL czy zasadność tworzenia indeksów w przykładowych sytuacjach).

Jeżeli macie ochotę posłuchać – zapraszam 🙂

Link do rejestracji na spotkanie: http://www.codeguru.pl/grupy/tgnet/kalendarium