MS SQL Server - Architektura
Podzieliliśmy architekturę SQL Server na następujące części, aby ułatwić zrozumienie -
- Ogólna architektura
- Architektura pamięci
- Architektura plików danych
- Architektura plików dziennika
Architektura ogólna
Client - Gdzie zainicjowano żądanie.
Query - Zapytanie SQL, które jest językiem wysokiego poziomu.
Logical Units - Słowa kluczowe, wyrażenia i operatory itp.
N/W Packets - Kod związany z siecią.
Protocols - W SQL Server mamy 4 protokoły.
Pamięć współdzielona (do połączeń lokalnych i rozwiązywania problemów).
Nazwane potoki (dla połączeń w sieci LAN).
TCP / IP (dla połączeń, które są w łączności WAN).
VIA-Virtual Interface Adapter (wymaga specjalnego sprzętu do skonfigurowania przez dostawcę, a także wycofany z wersji SQL 2012).
Server - Gdzie zainstalowano usługi SQL i znajdują się bazy danych.
Relational Engine- To tutaj odbędzie się prawdziwa egzekucja. Zawiera parser zapytań, optymalizator zapytań i moduł wykonawczy zapytań.
Query Parser (Command Parser) and Compiler (Translator) - Spowoduje to sprawdzenie składni zapytania i przekonwertowanie zapytania na język maszynowy.
Query Optimizer - Przygotuje plan wykonania jako dane wyjściowe, przyjmując zapytania, statystyki i drzewo Algebrizera jako dane wejściowe.
Execution Plan - To jest jak mapa drogowa, która zawiera kolejność wszystkich kroków do wykonania w ramach wykonywania zapytania.
Query Executor - Tutaj zapytanie zostanie wykonane krok po kroku z pomocą planu wykonania, a także skontaktujemy się z silnikiem pamięci masowej.
Storage Engine - Odpowiada za przechowywanie i odzyskiwanie danych w systemie pamięci masowej (dysk, SAN itp.), Manipulowanie danymi, blokowanie i zarządzanie transakcjami.
SQL OS- Znajduje się między komputerem głównym (systemem operacyjnym Windows) a serwerem SQL. Wszystkie czynności wykonywane na silniku bazy danych są obsługiwane przez system operacyjny SQL. SQL OS zapewnia różne usługi systemu operacyjnego, takie jak zarządzanie pamięcią z pulą buforów, bufor dziennika i wykrywanie zakleszczeń przy użyciu struktury blokowania i blokowania.
Checkpoint Process- Punkt kontrolny to wewnętrzny proces, który zapisuje wszystkie brudne strony (zmodyfikowane strony) z pamięci podręcznej bufora na dysk fizyczny. Oprócz tego zapisuje również rekordy dziennika z bufora dziennika do pliku fizycznego. Zapisywanie brudnych stron z pamięci podręcznej bufora do pliku danych jest również nazywane utwardzaniem brudnych stron.
Jest to proces dedykowany, uruchamiany automatycznie przez SQL Server w określonych odstępach czasu. SQL Server uruchamia proces punktu kontrolnego indywidualnie dla każdej bazy danych. Checkpoint pomaga skrócić czas przywracania programu SQL Server w przypadku nieoczekiwanego zamknięcia lub awarii systemu \ Awaria.
Punkty kontrolne w SQL Server
W SQL Server 2012 istnieją cztery typy checkpoints -
Automatic - Jest to najczęstszy punkt kontrolny, który działa jako proces w tle, aby upewnić się, że baza danych SQL Server może zostać odzyskana w czasie określonym w opcji Okres odzyskiwania - opcja konfiguracji serwera.
Indirect- Jest to nowość w programie SQL Server 2012. Działa również w tle, ale w celu spełnienia określonego przez użytkownika docelowego czasu odzyskiwania dla określonej bazy danych, w której skonfigurowano tę opcję. Po wybraniu Target_Recovery_Time dla danej bazy danych, spowoduje to zastąpienie interwału odzyskiwania określonego dla serwera i uniknięcie automatycznego punktu kontrolnego w takiej bazie danych.
Manual- Ta instrukcja działa jak każda inna instrukcja T-SQL, po wydaniu polecenia punktu kontrolnego będzie działać do końca. Ręczny punkt kontrolny działa tylko dla bieżącej bazy danych. Możesz również określić Checkpoint_Duration, który jest opcjonalny - ten czas trwania określa czas, w którym chcesz zakończyć punkt kontrolny.
Internal- Jako użytkownik nie możesz kontrolować wewnętrznego punktu kontrolnego. Wydane na określone operacje, takie jak
Shutdown inicjuje operację punktu kontrolnego na wszystkich bazach danych, z wyjątkiem sytuacji, gdy shutdown nie jest czysty (shutdown with nowait).
Jeśli model odzyskiwania zostanie zmieniony z Full \ Bulk-logged na Simple.
Podczas wykonywania kopii zapasowej bazy danych.
Jeśli Twoja baza danych jest w prostym modelu odzyskiwania, proces punktu kontrolnego jest wykonywany automatycznie, gdy dziennik zapełni się w 70% lub w oparciu o opcję serwera - okres odzyskiwania.
Polecenie Alter database w celu dodania lub usunięcia pliku data \ log również inicjuje punkt kontrolny.
Punkt kontrolny ma również miejsce, gdy model odzyskiwania bazy danych jest rejestrowany zbiorczo i wykonywana jest minimalnie zarejestrowana operacja.
Tworzenie migawki bazy danych.
Lazy Writer Process- Leniwy program zapisujący będzie wypychał brudne strony na dysk z zupełnie innego powodu, ponieważ musi zwolnić pamięć w puli buforów. Dzieje się tak, gdy serwer SQL znajduje się pod presją pamięci. O ile mi wiadomo, jest to kontrolowane przez wewnętrzny proces i nie ma na to żadnego ustawienia.
Serwer SQL stale monitoruje użycie pamięci, aby ocenić rywalizację o zasoby (lub dostępność); jego zadaniem jest upewnienie się, że przez cały czas dostępna jest pewna ilość wolnego miejsca. W ramach tego procesu, gdy zauważy jakąkolwiek rywalizację o zasoby, wyzwala program Lazy Writer, aby zwolnić niektóre strony w pamięci, zapisując brudne strony na dysk. Wykorzystuje algorytm LRU, który decyduje, które strony mają zostać zrzucone na dysk.
Jeśli program Lazy Writer jest zawsze aktywny, może to wskazywać na wąskie gardło pamięci.
Architektura pamięci
Oto niektóre z najistotniejszych cech architektury pamięci.
Jednym z głównych celów projektowych całego oprogramowania bazodanowego jest zminimalizowanie operacji we / wy dysku, ponieważ odczyty i zapisy na dysku należą do operacji wymagających największej ilości zasobów.
Pamięć w systemie Windows można wywołać za pomocą wirtualnej przestrzeni adresowej, współdzielonej przez tryb jądra (tryb systemu operacyjnego) i tryb użytkownika (aplikacja taka jak SQL Server).
„Przestrzeń adresowa użytkownika” programu SQL Server jest podzielona na dwa regiony: MemToLeave i Pula buforów.
Rozmiar MemToLeave (MTL) i puli buforów (BPool) jest określany przez SQL Server podczas uruchamiania.
Buffer managementjest kluczowym elementem w osiąganiu wysokiej wydajności we / wy. Komponent zarządzania buforami składa się z dwóch mechanizmów: menedżera buforów do uzyskiwania dostępu do stron bazy danych i ich aktualizowania oraz puli buforów do zmniejszania operacji we / wy pliku bazy danych.
Pula buforów jest dalej podzielona na wiele sekcji. Najważniejsze z nich to pamięć podręczna bufora (nazywana również pamięcią podręczną danych) i pamięć podręczna procedur.Buffer cacheprzechowuje strony danych w pamięci, dzięki czemu często używane dane można pobrać z pamięci podręcznej. Alternatywą byłoby odczytywanie stron danych z dysku. Odczytywanie stron danych z pamięci podręcznej optymalizuje wydajność, minimalizując liczbę wymaganych operacji we / wy, które są z natury wolniejsze niż pobieranie danych z pamięci.
Procedure cacheutrzymuje procedury składowane i plany wykonywania zapytań, aby zminimalizować liczbę generowanych planów zapytań. Możesz uzyskać informacje o rozmiarze i aktywności w pamięci podręcznej procedur za pomocą instrukcji DBCC PROCCACHE.
Inne części puli buforów obejmują -
System level data structures - Przechowuje dane poziomu instancji SQL Server dotyczące baz danych i blokad.
Log cache - Zarezerwowane do czytania i zapisywania stron dziennika transakcji.
Connection context- Każde połączenie z instancją ma niewielki obszar pamięci do zapisywania aktualnego stanu połączenia. Informacje te obejmują procedury składowane i parametry funkcji zdefiniowanych przez użytkownika, pozycje kursorów i nie tylko.
Stack space - Windows przydziela miejsce na stosie dla każdego wątku uruchomionego przez SQL Server.
Architektura plików danych
Architektura pliku danych składa się z następujących składników -
Grupy plików
Pliki baz danych można grupować w grupy plików do celów alokacji i administrowania. Żaden plik nie może być członkiem więcej niż jednej grupy plików. Pliki dziennika nigdy nie są częścią grupy plików. Przestrzeń dziennika jest zarządzana oddzielnie od przestrzeni danych.
Istnieją dwa typy grup plików w SQL Server, podstawowe i zdefiniowane przez użytkownika. Podstawowa grupa plików zawiera podstawowy plik danych i wszystkie inne pliki, które nie zostały specjalnie przypisane do innej grupy plików. Wszystkie strony tabel systemowych są przydzielone w podstawowej grupie plików. Grupy plików zdefiniowane przez użytkownika to dowolne grupy plików określone za pomocą słowa kluczowego file group w instrukcji tworzenia bazy danych lub zmiany bazy danych.
Jedna grupa plików w każdej bazie danych działa jako domyślna grupa plików. Gdy SQL Server przydziela stronę do tabeli lub indeksu, dla którego nie określono grupy plików podczas ich tworzenia, strony są przydzielane z domyślnej grupy plików. Aby przełączyć domyślną grupę plików z jednej grupy plików na inną, powinna mieć stałą rolę db_owner.
Domyślnie podstawową grupą plików jest domyślna grupa plików. Użytkownik powinien mieć stałą rolę db_owner w bazie danych, aby samodzielnie tworzyć kopie zapasowe plików i grup plików.
Akta
Bazy danych mają trzy typy plików - podstawowy plik danych, dodatkowy plik danych i plik dziennika. Podstawowy plik danych jest punktem początkowym bazy danych i wskazuje na inne pliki w bazie danych.
Każda baza danych ma jeden podstawowy plik danych. Możemy podać dowolne rozszerzenie dla podstawowego pliku danych, ale zalecanym rozszerzeniem jest.mdf. Dodatkowy plik danych to plik inny niż podstawowy plik danych w tej bazie danych. Niektóre bazy danych mogą mieć wiele pomocniczych plików danych. Niektóre bazy danych mogą nie mieć ani jednego dodatkowego pliku danych. Zalecane rozszerzenie dla dodatkowego pliku danych to.ndf.
Pliki dziennika zawierają wszystkie informacje dziennika używane do odzyskiwania bazy danych. Baza danych musi mieć co najmniej jeden plik dziennika. Możemy mieć wiele plików dziennika dla jednej bazy danych. Zalecane rozszerzenie pliku dziennika to.ldf.
Lokalizacja wszystkich plików w bazie danych jest rejestrowana zarówno w bazie danych master, jak i w pliku podstawowym bazy danych. W większości przypadków aparat bazy danych korzysta z lokalizacji pliku z bazy danych master.
Pliki mają dwie nazwy - logiczną i fizyczną. Nazwa logiczna jest używana w odniesieniu do pliku we wszystkich instrukcjach T-SQL. Nazwa fizyczna to nazwa_pliku_OS, musi być zgodna z zasadami systemu operacyjnego. Pliki danych i dziennika można umieszczać w systemach plików FAT lub NTFS, ale nie można ich umieszczać w systemach plików skompresowanych. W jednej bazie danych może znajdować się do 32767 plików.
Zakresy
Zakresy to podstawowa jednostka, w której miejsce jest przydzielane tabelom i indeksom. Zasięg to 8 sąsiadujących ze sobą stron lub 64 KB. SQL Server ma dwa typy zakresów - jednolite i mieszane. Jednolite zakresy składają się tylko z jednego obiektu. Zakresy mieszane są współdzielone przez maksymalnie osiem obiektów.
Strony
Jest to podstawowa jednostka przechowywania danych w MS SQL Server. Rozmiar strony to 8 KB. Początkiem każdej strony jest 96-bajtowy nagłówek używany do przechowywania informacji systemowych, takich jak typ strony, ilość wolnego miejsca na stronie i identyfikator obiektu będącego właścicielem strony. W SQL Server istnieje 9 typów stron danych.
Data - Wiersze danych zawierające wszystkie dane z wyjątkiem danych tekstowych, tekstowych i graficznych.
Index - Wpisy indeksu.
Tex\Image - Dane tekstowe, graficzne i ntext.
GAM - Informacje o przydzielonych zakresach.
SGAM - Informacje o przydzielonych zakresach na poziomie systemu.
Page Free Space (PFS) - Informacje o wolnym miejscu dostępnym na stronach.
Index Allocation Map (IAM) - Informacje o zakresach używanych przez tabelę lub indeks.
Bulk Changed Map (BCM) - Informacje o zakresach zmodyfikowanych przez operacje zbiorcze od ostatniej kopii zapasowej instrukcji dziennika.
Differential Changed Map (DCM) - Informacje o zakresach, które zmieniły się od czasu ostatniej instrukcji kopii zapasowej bazy danych.
Architektura pliku dziennika
Dziennik transakcji programu SQL Server działa logicznie tak, jakby dziennik transakcji był ciągiem rekordów dziennika. Każdy rekord dziennika jest identyfikowany przez numer sekwencji dziennika (LSN). Każdy rekord dziennika zawiera identyfikator transakcji, do której należy.
Rekordy dziennika modyfikacji danych rejestrują albo wykonaną operację logiczną, albo rejestrują obrazy przed i po zmodyfikowanych danych. Obraz przed jest kopią danych przed wykonaniem operacji; obraz wtórny jest kopią danych po wykonaniu operacji.
Kroki umożliwiające odzyskanie operacji zależą od typu rekordu dziennika -
- Logiczna operacja zarejestrowana.
- Aby wycofać operację logiczną do przodu, operacja jest wykonywana ponownie.
- Aby wycofać operację logiczną, wykonywana jest odwrotna operacja logiczna.
- Rejestracja obrazu przed i po.
- Aby przewinąć operację do przodu, stosowany jest obraz po.
- Aby cofnąć operację, stosowany jest obraz sprzed.
W dzienniku transakcji rejestrowane są różne typy operacji. Te operacje obejmują -
Początek i koniec każdej transakcji.
Każda modyfikacja danych (wstawianie, aktualizowanie lub usuwanie). Obejmuje to zmiany wprowadzone przez procedury składowane w systemie lub instrukcje języka definicji danych (DDL) w dowolnej tabeli, w tym tabelach systemowych.
Każdy zakres i alokacja stron lub usunięcie alokacji.
Tworzenie lub usuwanie tabeli lub indeksu.
Operacje wycofywania zmian są również rejestrowane. Każda transakcja rezerwuje miejsce w dzienniku transakcji, aby upewnić się, że istnieje wystarczająca ilość miejsca w dzienniku, aby obsłużyć wycofywanie, które jest spowodowane jawną instrukcją wycofania lub napotkaniem błędu. To zarezerwowane miejsce jest zwalniane po zakończeniu transakcji.
Sekcja pliku dziennika z pierwszego rekordu dziennika, która musi istnieć, aby pomyślnie przywrócić całą bazę danych do ostatniego zapisanego rekordu dziennika, nazywana jest aktywną częścią dziennika lub dziennikiem aktywnym. To jest sekcja dziennika wymagana do pełnego odzyskania bazy danych. Żadna część aktywnego dziennika nie może zostać obcięta. LSN tego pierwszego rekordu dziennika jest nazywany minimalnym LSN odzyskiwania (Min LSN).
Aparat bazy danych programu SQL Server dzieli każdy fizyczny plik dziennika wewnętrznie na kilka wirtualnych plików dziennika. Pliki dziennika wirtualnego nie mają stałego rozmiaru i nie ma ustalonej liczby wirtualnych plików dziennika dla fizycznego pliku dziennika.
Aparat baz danych dynamicznie wybiera rozmiar wirtualnych plików dziennika podczas tworzenia lub rozszerzania plików dziennika. Aparat baz danych próbuje zachować niewielką liczbę plików wirtualnych. Administratorzy nie mogą konfigurować ani ustawiać rozmiaru ani liczby wirtualnych plików dziennika. Jedyny przypadek, w którym wirtualne pliki dziennika wpływają na wydajność systemu, to sytuacja, gdy fizyczne pliki dziennika są zdefiniowane przez mały rozmiar i wartości growth_increment.
Wartość rozmiaru to początkowy rozmiar pliku dziennika, a wartość growth_increment to ilość miejsca dodawana do pliku za każdym razem, gdy wymagane jest nowe miejsce. Jeśli pliki dziennika osiągną duży rozmiar z powodu wielu małych przyrostów, będą miały wiele wirtualnych plików dziennika. Może to spowolnić uruchamianie bazy danych, a także rejestrować operacje tworzenia kopii zapasowych i przywracania.
Zalecamy przypisanie plikom dziennika wartości rozmiaru zbliżonej do wymaganego rozmiaru końcowego, a także stosunkowo dużej wartości growth_increment. SQL Server używa dziennika zapisu z wyprzedzeniem (WAL), który gwarantuje, że żadne modyfikacje danych nie zostaną zapisane na dysku przed zapisaniem skojarzonego rekordu dziennika na dysk. To zachowuje właściwości ACID transakcji.