SQL Server Architecture (wyjaśnione)

MS SQL Server to architektura klient-serwer. Proces MS SQL Server rozpoczyna się od wysłania żądania przez aplikację kliencką. SQL Server akceptuje, przetwarza i odpowiada na żądanie, przesyłając przetworzone dane. Omówmy szczegółowo całą architekturę przedstawioną poniżej:

Jak pokazano na poniższym diagramie, w programie SQL Server występują trzy główne komponenty Archistruktura:

1. Warstwa protokołu
2. Silnik relacyjny
3. Silnik pamięci masowej

Warstwa protokołu – SNI

Warstwa protokołu SQL Server, znana również jako interfejs sieciowy serwera (SNI), obsługuje trzy typy architektury klient-serwer. Każdy protokół obsługuje inny scenariusz sieciowy. Zrozumienie tych protokołów jest niezbędne przed zgłębieniem wewnętrznego przetwarzania zapytań.

Pamięć współdzielona

Rozważmy poranną rozmowę. Tom i jego mama są w tym samym logicznym miejscu, w swoim domu. Tom prosi o kawę, a mama podaje ją bezpośrednio. Podobnie, SQL Server udostępnia protokół pamięci współdzielonej, gdy klient i serwer działają na tej samej maszynie. Oba systemy komunikują się za pośrednictwem pamięci współdzielonej bez obciążenia sieciowego.

Analogia: Tom jest mapowany na Klienta, Mama jest mapowana na Serwer SQL, Dom jest mapowany na Maszynę, a komunikacja werbalna jest mapowana na protokół Pamięci Współdzielonej.

Notatki dotyczące konfiguracji: In Studio zarządzania SQLOpcja „Nazwa serwera” dla połączenia lokalnego może być „.”, „localhost”, „127.0.0.1” lub „Komputer\Instancja”.

TCP / IP

Wyobraź sobie, że Tomek chce kawy ze sklepu oddalonego o 10 km. Tomek jest w domu, a kawiarnia znajduje się na ruchliwym targu. Komunikują się za pośrednictwem sieci komórkowej. Podobnie, SQL Server zapewnia… Protokół TCP / IP gdy klient i serwer SQL Server znajdują się na oddzielnych maszynach połączonych siecią.

Analogia: Tom jest mapowany na Klienta, kawiarnia jest mapowana na SQL Server, dom i sklep są mapowane na zdalne lokalizacje, a sieć komórkowa jest mapowana na protokół TCP/IP.

Notatki dotyczące konfiguracji: W SQL Management Studio opcja „Nazwa serwera” dla połączenia TCP/IP musi być „Komputer\Wystąpienie serwera”. Domyślnie serwer SQL Server używa portu 1433 dla połączeń TCP/IP.

Nazwane rury

Na koniec Tomek chce zieloną herbatę od swojej sąsiadki Sierry. Znajdują się w tej samej lokalizacji fizycznej, są sąsiadami i komunikują się za pośrednictwem sieci wewnętrznej. Podobnie, SQL Server udostępnia protokół potoku nazwanego, gdy klient i serwer są połączone za pośrednictwem sieci lokalnej (LAN).

Analogia: Tom jest mapowany na Klienta, Sierra jest mapowana na Serwer SQL, sąsiedzi są mapowani na sieć LAN, a sieć wewnątrzsieciowa jest mapowana na protokół Nazwanego Potoku.

Notatki dotyczące konfiguracji: Nazwane potoki są domyślnie wyłączone i należy je włączyć za pomocą Menedżera konfiguracji SQL.

Co to jest TDS?

Teraz, gdy wyjaśniliśmy już trzy typy architektury klient-serwer, przyjrzyjmy się bliżej TDS:

• TDS oznacza tabelaryczny strumień danych.
• Wszystkie trzy protokoły wykorzystują pakiety TDS.
• TDS jest kapsułkowany w pakietach sieciowych, umożliwiając przesyłanie danych z komputera klienckiego do serwera.
• TDS został pierwotnie opracowany przez firmę Sybase i obecnie jest własnością Microsoft.

W poniższej tabeli porównano trzy protokoły połączeń SQL Server:

Cecha	Pamięć współdzielona	TCP / IP	Nazwane rury
Zakres sieci	Ta sama maszyna	Zdalny (WAN/Internet)	Tylko sieć LAN
Port domyślny	N / A	1433	445
Wydajność	Najszybszy (bez obciążenia sieci)	Dobry (zoptymalizowany dla sieci WAN)	Dobry (zoptymalizowany pod kątem sieci LAN)
Włączone domyślnie	Tak	Tak	Nie
Najlepszy przypadek użycia	Rozwój lokalny i testowanie	Zdalny dostęp do produkcji	Zaufane środowiska LAN

Ponieważ warstwa protokołu obsługuje komunikację sieciową, kolejnym krokiem w architekturze SQL Server jest samo przetwarzanie zapytania. To właśnie tutaj przejmuje kontrolę silnik relacyjny.

Silnik relacyjny

Silnik relacyjny jest również znany jako procesor zapytań. Zawiera komponenty SQL Server, które określają, co zapytanie ma wykonać i jak najefektywniej je wykonać. Odpowiada za wykonywanie zapytań użytkownika poprzez żądanie danych z silnika pamięci masowej i przetwarzanie zwróconych wyników.

Jak pokazano na schemacie architektonicznym, silnik relacyjny składa się z trzech głównych komponentów:

Parser CMD

Dane otrzymane z warstwy protokołu są przekazywane do silnika relacyjnego. Parser CMD jest pierwszym komponentem odbierającym dane zapytania. Jego głównym zadaniem jest sprawdzenie zapytania pod kątem błędów składniowych i semantycznych, a następnie wygenerowanie drzewa zapytań.

Kontrola składni: Jak każdy inny język programowania, SQL Server ma predefiniowany zestaw słów kluczowych i reguł gramatycznych. SELECT, INSERT, UPDATE i wiele innych należą do predefiniowanej listy słów kluczowych. Parser CMD weryfikuje zgodność danych wejściowych z tymi regułami. Jeśli dane wejściowe użytkownika odbiegają od oczekiwanej składni, parser zwraca błąd.

Przykład: Wyobraź sobie Rosjanina wchodzącego do japońskiej restauracji i składającego zamówienie po rosyjsku. Kelner rozumie tylko japoński i nie może przetworzyć zamówienia. Podobnie, jeśli użytkownik wpisze „SELECR” zamiast „SELECT”, parser CMD zwróci błąd, ponieważ nie rozpoznaje słowa kluczowego.

Kontrola semantyczna: Wykonuje to Normalizator. Sprawdza on, czy nazwy kolumn, tabel i inne obiekty będące przedmiotem zapytania rzeczywiście istnieją w schemacie. Jeśli tak, Normalizator wiąże je z zapytaniem. Ten proces jest również znany jako wiązanie. Gdy zapytania użytkownika zawierają WIDOK, Normalizator zastępuje go wewnętrznie zapisaną definicją widoku.

Przykład: Bieganie SELECT * from USER_ID spowodowałoby, że parser zgłosiłby błąd podczas sprawdzania semantyki, jeśli tabela USER_ID nie istnieje w bazie danych.

Utwórz drzewo zapytań: Ten krok generuje różne drzewa wykonania, reprezentujące różne sposoby uruchomienia zapytania. Wszystkie drzewa generują ten sam pożądany wynik.

Optimizer

Optymalizator tworzy plan wykonania zapytania użytkownika. Plan ten określa sposób wykonania zapytania. Nie wszystkie zapytania są optymalizowane. Optymalizacja dotyczy poleceń DML (Data Modification Language), takich jak SELECT, INSERT, DELETE i UPDATE. Polecenia DDL, takie jak CREATE i ALTER, nie są optymalizowane, lecz kompilowane do postaci wewnętrznej.

Koszt zapytania jest obliczany na podstawie takich czynników, jak obciążenie procesora, zużycie pamięci oraz zapotrzebowanie na dane wejścia/wyjścia. Rolą Optymalizatora jest znalezienie najtańszego i najbardziej opłacalnego planu wykonania, a niekoniecznie najlepszego.

Przykład: Wyobraź sobie, że chcesz otworzyć konto bankowe online. Założenie konta w jednym banku zajmuje maksymalnie 2 dni. Masz również listę 20 innych banków, które mogą, ale nie muszą, zająć mniej czasu. Przeszukanie wszystkich 20 banków może nie znaleźć szybszej opcji, a samo wyszukiwanie jest czasochłonne. Lepiej byłoby wybrać pierwszy bank. Podobnie, SQL Optimizer wykorzystuje wyczerpujące i heurystyczne algorytmy, aby zminimalizować czas wykonywania zapytań.

Optymalizator przeszukuje w trzech fazach:

Faza 0: Poszukiwanie trywialnego planu

To etap przedoptymalizacji. W przypadku niektórych zapytań istnieje tylko jeden praktyczny plan, znany jako plan trywialny. Nie ma potrzeby dalszego wyszukiwania, ponieważ każde dodatkowe wyszukiwanie spowodowałoby znalezienie tego samego planu wykonania, co wiązałoby się z dodatkowymi kosztami.

Faza 1: Wyszukiwanie planów przetwarzania transakcji

Obejmuje to wyszukiwanie zarówno planów prostych, jak i złożonych. Wyszukiwanie według planu prostego wykorzystuje analizę statystyczną danych kolumnowych i indeksowych, zazwyczaj ograniczoną do jednego indeksu na tabelę. Jeśli nie zostanie znaleziony żaden plan prosty, przeprowadzane jest bardziej złożone wyszukiwanie obejmujące wiele indeksów na tabelę.

Faza 2: Przetwarzanie równoległe i optymalizacja

Jeśli poprzednie strategie nie przyniosą odpowiedniego planu, Optymalizator poszukuje możliwości przetwarzania równoległego w oparciu o możliwości obliczeniowe maszyny. Jeśli przetwarzanie równoległe nie jest możliwe, rozpoczyna się końcowa faza optymalizacji, która wykorzystuje wszystkie pozostałe opcje w celu znalezienia najlepszego możliwego planu wykonania.

Wykonawca zapytania

Wykonawca zapytania wywołuje metodę dostępu (Access Method) w silniku pamięci masowej. Dostarcza plan wykonania zawierający logikę pobierania danych wymaganą do wykonania. Po otrzymaniu danych z silnika pamięci masowej, wynik jest publikowany na warstwie protokołu i wysyłany do użytkownika końcowego.

Po tym, jak silnik relacyjny określi sposób wykonania zapytania, silnik pamięci masowej obsługuje fizyczne operacje na danych. Ta warstwa zarządza sposobem przechowywania, buforowania i pobierania danych z dysku.

Silnik pamięci masowej

Moduł Storage Engine odpowiada za przechowywanie danych w systemie pamięci masowej, takim jak dysk lub sieć SAN, i ich pobieranie w razie potrzeby. Przed omówieniem komponentów modułu Storage Engine ważne jest zrozumienie, jak dane są fizycznie przechowywane.

Pliki danych i zakresy

Pliki danych fizycznie przechowują dane w formie stron danych, z których każda ma rozmiar 8 KB. Jest to najmniejsza jednostka pamięci masowej w SQL ServerStrony danych są logicznie pogrupowane w zakresy. Żaden obiekt nie jest bezpośrednio przypisany do pojedynczej strony; konserwacja odbywa się za pośrednictwem zakresów. Każda strona ma nagłówek strony (96 bajtów) zawierający metadane, takie jak typ strony, numer strony, zajęte miejsce, wolne miejsce oraz wskaźniki do następnych i poprzednich stron.

Typy plików

Plik główny: Każda baza danych zawiera jeden plik główny. Przechowuje on wszystkie ważne dane związane z tabelami, widokami, wyzwalaczami i innymi obiektami. Rozszerzenie pliku to zazwyczaj .mdf, ale może być dowolne.

Plik drugorzędny: Baza danych może, ale nie musi, zawierać wiele plików dodatkowych. Są one opcjonalne i zawierają dane specyficzne dla użytkownika. Rozszerzenie pliku to zazwyczaj .ndf, ale może być dowolne.

Plik dziennika: Znane również jako Write-Ahead Logs. Rozszerzenie .ldf. Pliki dziennika służą do zarządzania transakcjami, odzyskiwania po niechcianych wystąpieniach oraz wycofywania niezatwierdzonych transakcji.

System Storage Engine składa się z trzech głównych komponentów. Każdy z nich odgrywa określoną rolę w zarządzaniu dostępem do danych i ich integralnością.

Metoda dostępu

Metoda dostępu działa jako interfejs pomiędzy wykonawcą zapytania a Buffer Menedżer lub dzienniki transakcji. Nie wykonuje on samego wykonania, ale określa typ zapytania:

• Jeśli zapytanie jest Instrukcja SELECT (DML), jest przekazywany do Buffer Menedżer do dalszego przetwarzania.
• Jeśli zapytanie jest Instrukcja inna niż SELECT (DDL i DML), jest przekazywany do Menedżera Transakcji. Obejmuje to głównie polecenia UPDATE, INSERT i DELETE.

Buffer Menedżer

Buffer Menedżer zarządza podstawowymi funkcjami pamięci podręcznej planów, analizą danych i obsługą brudnych stron.

Planuj pamięć podręczną

Istniejący plan zapytania: Buffer Menedżer sprawdza, czy plan wykonania znajduje się w pamięci podręcznej planów. Jeśli tak, buforowany plan zapytania i powiązana z nim pamięć podręczna danych są używane bezpośrednio.

Pierwszy plan buforowania: Jeśli plan wykonania pierwszego zapytania jest złożony, jest on zapisywany w pamięci podręcznej planów. Zapewnia to szybszą dostępność przy kolejnym odebraniu tego samego zapytania przez serwer SQL Server.

Analiza danych: Buffer Pamięć podręczna i przechowywanie danych

Buffer Menedżer zapewnia dostęp do wymaganych danych. W zależności od tego, czy dane znajdują się w pamięci podręcznej, możliwe są dwa podejścia:

Buffer Pamięć podręczna – analiza miękka

Buffer Menedżer wyszukuje dane w Buffer Pamięć podręczna. Jeśli dane są obecne, Query Executor wykorzystuje je bezpośrednio. Poprawia to wydajność, ponieważ pobieranie danych z pamięci podręcznej wymaga mniejszej liczby operacji wejścia/wyjścia w porównaniu z pobieraniem z pamięci dyskowej.

Przechowywanie danych – twarda analiza składniowa

Jeżeli dane nie są obecne w Buffer Pamięć podręczna – wymagane dane są przeszukiwane w pamięci masowej na dysku. Dane są następnie przechowywane w pamięci podręcznej do wykorzystania w przyszłości.

Menedżer transakcji

Menedżer transakcji jest wywoływany, gdy metoda dostępu ustali, że zapytanie nie jest instrukcją SELECT. Zapewnia spójność i trwałość danych poprzez kilka podkomponentów:

Menedżer dziennika

Menedżer dziennika przechowuje track wszystkich aktualizacji wykonanych w systemie za pośrednictwem logów przechowywanych w dziennikach transakcji. Każdy wpis w dzienniku zawiera numer sekwencyjny dziennika wraz z identyfikatorem transakcji i rekordem modyfikacji danych. Ten mechanizm tracks zatwierdzonych i wycofanych transakcji.

Menedżer zamków

Podczas transakcji powiązane dane w pamięci masowej przechodzą w stan zablokowania. Menedżer Blokady obsługuje ten proces, zapewniając spójność i izolację danych. Właściwości te są również znane jako ACID (AtomIlność, Konsystencja, Izolacja, Trwałość).

Proces wykonania

Proces realizacji przebiega następująco:

1. Menedżer dziennika rozpoczyna rejestrowanie, a Menedżer blokad blokuje skojarzone dane.
2. Kopia danych jest przechowywana w Buffer Pamięć podręczna
3. Kopia danych do aktualizacji jest przechowywana w dzienniku Bufferi wszystkie zdarzenia aktualizują dane w Data Buffer.
4. Strony przechowujące zmodyfikowane dane są znane jako Brudne strony.

Rejestrowanie punktów kontrolnych i zapisów z wyprzedzeniem

Proces punktu kontrolnego jest uruchamiany mniej więcej raz na minutę i oznacza wszystkie brudne strony do zapisu na dysku. Jednak strona jest najpierw przesyłana na stronę danych pliku dziennika z Buffer Log. Mechanizm ten znany jest jako Write-Ahead Logging. Brudne strony pozostają w pamięci podręcznej nawet po zapisaniu na dysku.

leniwy Writer

Gdy SQL Server wykryje duże obciążenie i pamięć buforowa będzie potrzebna do nowych transakcji, zwalnia brudne strony z pamięci podręcznej. Writer działa w oparciu o algorytm LRU (Least Recently Used) w celu czyszczenia stron z puli buforów i umieszczania ich na dysku.

Jak program SQL Server przetwarza zapytanie od początku do końca

Zrozumienie każdej warstwy z osobna jest cenne, ale zobaczenie, jak one ze sobą współdziałają, rozjaśnia cały obraz. Gdy aplikacja kliencka wysyła zapytanie SQL, następuje następująca sekwencja:

Warstwa protokołu odbiera żądanie za pośrednictwem pamięci współdzielonej, protokołu TCP/IP lub nazwanych potoków i pakuje je w pakiet TDS. Silnik relacyjny Następnie przejmuje kontrolę: parser CMD sprawdza składnię i semantykę, optymalizator generuje najtańszy plan wykonania, a wykonawca zapytania rozpoczyna pobieranie danych.

Wykonawca zapytania wywołuje Silnik pamięci masowej Metoda dostępu, która kieruje zapytania SELECT do Buffer Zapytania menedżera i modyfikacji do menedżera transakcji. Buffer Menedżer sprawdza pamięć podręczną planu i Buffer Najpierw buforowanie (analiza miękka). Jeśli dane nie są buforowane, wykonywany jest odczyt z dysku (analiza twarda). W przypadku operacji zapisu Menedżer transakcji koordynuje Menedżera dziennika, Menedżera blokad i proces punktu kontrolnego, aby zapewnić zgodność z ACID.

Gdy moduł pamięci masowej zwróci żądane dane, moduł relacyjny formatuje zestaw wyników, a warstwa protokołu przekazuje go z powrotem do aplikacji klienckiej za pośrednictwem tego samego protokołu TDS.

Jak wybrać właściwy protokół dla połączeń z serwerem SQL

Wybór właściwego protokołu zależy od fizycznej relacji pomiędzy klientem i serwerem, a także od wymagań wydajnościowych.

Użyj pamięci współdzielonej gdy aplikacja kliencka działa na tej samej maszynie co SQL Server. Jest to najszybsza opcja, ponieważ eliminuje wszelkie obciążenie sieci. Idealnie nadaje się do lokalnego tworzenia oprogramowania, testowania i wdrożeń na pojedynczych maszynach.

Użyj TCP/IP gdy klient i serwer znajdują się na różnych maszynach połączonych przez sieć WAN lub internet. Jest to najczęściej używany protokół w środowiskach produkcyjnych. Serwer SQL Server domyślnie nasłuchuje na porcie 1433, a protokół ten obsługuje szyfrowane połączenia przez TLS.

Użyj nazwanych potoków Gdy klient i serwer znajdują się w tej samej zaufanej sieci LAN, a wydajność w sieciach wewnętrznych jest priorytetem, funkcja potoków nazwanych jest domyślnie wyłączona i należy ją włączyć za pomocą Menedżera konfiguracji programu SQL Server. Jest ona mniej powszechna we współczesnych wdrożeniach, ale nadal przydatna w starszych aplikacjach intranetowych.

FAQ