Przewodnik po architekturze zarządzania pamięcią

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Menedżer pamięci wirtualnej systemu Windows

Zatwierdzone regiony przestrzeni adresowej są mapowane na dostępną pamięć fizyczną przez Menedżera pamięci wirtualnej systemu Windows (VMM).

Aby uzyskać więcej informacji na temat ilości pamięci fizycznej obsługiwanej przez różne systemy operacyjne, zobacz dokumentację systemu Windows dotyczącą limitów pamięci dla wydań systemu Windows.

Systemy pamięci wirtualnej umożliwiają nadmierne zaangażowanie pamięci fizycznej, dzięki czemu stosunek pamięci wirtualnej do fizycznej może przekroczyć 1:1. W związku z tym większe programy mogą być uruchamiane na komputerach z różnymi konfiguracjami pamięci fizycznej. Jednak użycie znacznie większej ilości pamięci wirtualnej niż połączone średnie zestawy robocze wszystkich procesów może spowodować niską wydajność.

Architektura pamięci programu SQL Server

Program SQL Server dynamicznie uzyskuje i zwalnia pamięć zgodnie z potrzebami. Zazwyczaj administrator nie musi określać, ile pamięci należy przydzielić do programu SQL Server, chociaż opcja nadal istnieje i jest wymagana w niektórych środowiskach.

Jednym z głównych celów projektowych wszystkich oprogramowania bazy danych jest zminimalizowanie operacji we/wy dysku, ponieważ operacje odczytu i zapisu dysku należą do najbardziej intensywnie korzystających z zasobów operacji. Program SQL Server tworzy pulę w pamięci w celu przechowywania stron odczytywanych z bazy danych. Większość kodu w programie SQL Server jest przeznaczona do zminimalizowania liczby fizycznych odczytów i zapisów między dyskiem a pulą. Program SQL Server próbuje osiągnąć równowagę między dwoma celami:

• Zapobiegać, aby pula buforów nie rosła do takich rozmiarów, że cały system zabraknie pamięci.
• Zminimalizuj fizyczne operacje we/wy do plików bazy danych poprzez maksymalne zwiększenie rozmiaru puli buforowej.

W mocno załadowanym systemie niektóre duże zapytania, które wymagają dużej ilości pamięci do uruchomienia, nie mogą uzyskać minimalnej ilości żądanej pamięci i otrzymać błąd przekroczenia limitu czasu podczas oczekiwania na zasoby pamięci. Aby rozwiązać ten problem, zwiększ opcję oczekiwania zapytania. W przypadku zapytania równoległego rozważ zmniejszenie maksymalnego stopnia równoległości Opcji.

W mocno obciążonym systemie pod presją pamięci, zapytania ze sprzężeniami scalanymi, sortowaniem i mapą bitową w planie zapytania mogą usuwać mapę bitową, jeśli nie otrzymują minimalnej wymaganej pamięci dla mapy bitowej. Może to wpływać na wydajność zapytań, a jeśli proces sortowania nie mieści się w pamięci, może to zwiększyć użycie tabel roboczych w bazie danych tempdb, co powoduje wzrost tempdb. Aby rozwiązać ten problem, dodaj pamięć fizyczną lub dostosuj zapytania, aby użyć innego i szybszego planu zapytań.

Tradycyjna pamięć (wirtualna)

Wszystkie wersje programu SQL Server obsługują tradycyjną pamięć na 64-bitowej platformie. Proces SQL Server może uzyskać dostęp do wirtualnej przestrzeni adresowej do maksimum określonego przez system operacyjny w architekturze x64 (SQL Server Standard Edition obsługuje maksymalnie 128 GB). W przypadku architektury IA64 limit wynosił 7 TB (IA64 nie jest obsługiwany w programie SQL Server 2012 (11.x) i nowszych wersjach). Aby uzyskać więcej informacji , zobacz Limity pamięci dla systemu Windows .

Adres pamięci rozszerzeń systemu Windows (AWE)

Korzystając z rozszerzeń okien adresowych (AWE) i uprawnień Blokuj strony w pamięci (LPIM) wymagane przez AWE, można zachować większość pamięci procesu programu SQL Server zablokowanej w fizycznej pamięci RAM w warunkach małej ilości pamięci wirtualnej. Dzieje się tak zarówno przy alokacjach AWE w 32-bitowym, jak i 64-bitowym środowisku. Blokowanie pamięci występuje, ponieważ pamięć AWE nie przechodzi przez Menedżera pamięci wirtualnej w systemie Windows, który kontroluje stronicowanie pamięci. Interfejs API alokacji pamięci AWE wymaga przywileju Blokuj strony w pamięci (SeLockMemoryPrivilege); zobacz uwagi dotyczące AllocateUserPhysicalPages. W związku z tym główną zaletą korzystania z interfejsu API AWE jest utrzymanie większości pamięci jako rezydentnej w pamięci RAM, jeśli w systemie występuje presja na pamięć. Aby uzyskać informacje o umożliwieniu programowi SQL Server korzystania z AWE, zobacz Włącz opcję blokady stron w pamięci (Windows).

W przypadku udzielenia LPIM zdecydowanie zalecamy ustawienie maksymalnej pamięci serwera (MB) na określoną wartość, a nie pozostawienie wartości domyślnej 2147 483 647 megabajtów (MB). Aby uzyskać więcej informacji, zobacz Konfiguracja pamięci serwera: ustawianie opcji ręcznie i Blokowanie stron w pamięci (LPIM).

Jeśli protokół LPIM nie jest włączony, program SQL Server przełącza się na użycie konwencjonalnej pamięci i w przypadkach wyczerpania pamięci systemu operacyjnego oraz błąd [MSSQLSERVER_17890] (errors-events/mssqlserver-17890-database-engine-error.md) może zostać zgłoszony w dzienniku błędów. Błąd przypomina następujący przykład:

A significant part of SQL Server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

Zmiany zarządzania pamięcią począwszy od programu SQL Server 2012

W starszych wersjach programu SQL Server alokacja pamięci została wykonana przy użyciu pięciu różnych mechanizmów:

• Single-Page alokator (SPA), w tym tylko alokacje pamięci, które były mniejsze lub równe 8 KB w procesie SQL Server. Maksymalna ilość pamięci serwera (MB) i minimalna pamięć serwera (MB) opcje konfiguracji określają limity pamięci fizycznej używanej przez SPA. Pula buforów jednocześnie pełniła rolę mechanizmu SPA i była największym odbiorcą alokacji jednostronicowych.
• Wielostronicowy alokator (MPA) dla alokacji pamięci, które żądają więcej niż 8 KB.
• ClR Allocator, w tym sterty CLR SQL i jego globalne alokacje, które są tworzone podczas inicjowania CLR.
• Alokacje pamięci dla stosów wątków w procesie programu SQL Server.
• Bezpośrednie alokacje systemu Windows (DWA) dla żądań alokacji pamięci wysyłanych bezpośrednio do systemu Windows. Obejmuje to wykorzystanie sterty systemu Windows i bezpośrednie alokacje wirtualne wykonywane przez moduły ładowane do procesu SQL Server. Przykłady takich żądań alokacji pamięci obejmują alokacje z rozszerzonych procedur składowanych w formie bibliotek DLL, obiektów tworzonych przy użyciu procedur automatyzacji (wywołania sp_OA) oraz alokacje przez dostawców serwerów połączonych.

Począwszy od programu SQL Server 2012 (11.x), alokacje Single-Page, alokacje wielostronicowe oraz alokacje CLR są konsolidowane w ramach alokatora stron o dowolnym rozmiarze i uwzględniane w limitach pamięci kontrolowanych za pomocą opcji konfiguracyjnych maksymalna pamięć serwera (MB) i minimalna pamięć serwera (MB). Ta zmiana zapewniała dokładniejszą możliwość określania rozmiaru dla wszystkich wymagań dotyczących pamięci przechodzących przez menedżera pamięci programu SQL Server.

Poniższa tabela wskazuje, czy określony typ alokacji pamięci jest kontrolowany przez opcje konfiguracji maksymalnej pamięci serwera (MB) i minimalnej pamięci serwera (MB):

Program SQL Server może zatwierdzać pamięć za pośrednictwem ustawienia maksymalnej pamięci serwera

Począwszy od programu SQL Server 2012 (11.x), program SQL Server może przydzielić więcej pamięci niż wartość określona w ustawieniu maksymalnej pamięci serwera (MB). To zachowanie może wystąpić, gdy wartość Łączna pamięć serwera (KB) osiągnęła już ustawienie Pamięć serwera docelowego (KB) określone przez maksymalną pamięć serwera (MB). Jeśli za mało ciągłej, wolnej pamięci, aby zaspokoić zapotrzebowanie na żądania pamięci wielostronicowej (ponad 8 KB) z powodu fragmentacji pamięci, program SQL Server może wykonać nadmierne przydzielanie zamiast odrzucenia żądania pamięci.

Gdy tylko ta alokacja zostanie wykonana, zadanie w tle Monitor zasobów zacznie sygnalizować wszystkim odbiorcom pamięci, aby zwolnić przydzieloną pamięć, i próbuje obniżyć wartość Łączna pamięć serwera (KB) poniżej specyfikacji pamięci serwera docelowego (KB). W związku z tym użycie pamięci programu SQL Server może na krótko przekroczyć ustawienie maksymalnej pamięci serwera (MB). W takiej sytuacji odczyt licznika wydajności całkowitej pamięci serwera (KB) przekracza ustawienia maksymalnej pamięci serwera (MB) i pamięci serwera docelowego (KB).

To zachowanie jest zwykle obserwowane podczas następujących operacji:

• Duże zapytania indeksu magazynu kolumn
• Tryb wsadowy dla dużych zapytań rowstore
• Kompilacje indeksów kolumnowych (tworzenia i przebudowywania), które używają dużych ilości pamięci do wykonywania operacji haszowania i sortowania
• Operacje tworzenia kopii zapasowych, które wymagają dużych pamięci
• Operacje śledzenia, które muszą przechowywać duże parametry wejściowe
• Żądania udzielenia dużej ilości pamięci

Jeśli obserwujesz to zachowanie często, rozważ użycie flagi śledzenia 8121 w programie SQL Server 2019 (15.x), aby umożliwić monitorowi zasobów szybsze czyszczenie. Począwszy od programu SQL Server 2022 (16.x), ta funkcja jest domyślnie włączona, a flaga śledzenia nie działa.

Zmiany w memory_to_reserve począwszy od SQL Server 2012

W starszych wersjach programu SQL Server menedżer pamięci w SQL Server przeznaczył część wirtualnej przestrzeni adresowej procesu (VAS) do użycia przez alokator wielostronicowy (MPA), alokator CLR, alokacje pamięci dla stosów wątków w procesie programu SQL Server i bezpośrednie alokacje Windows (DWA). Ta część wirtualnej przestrzeni adresowej jest również nazywana regionem "Mem-To-Leave" lub "pulą niebuforową".

Wirtualna przestrzeń adresowa zarezerwowana dla tych alokacji jest określana przez opcję konfiguracji memory_to_reserve. Wartość domyślna używana przez program SQL Server to 256 MB.

Ponieważ alokator strony o dowolnym rozmiarze obsługuje również alokacje większe niż 8 KB, wartość memory_to_reserve nie zawiera alokacji wielostronicowych. Z wyjątkiem tej zmiany wszystkie inne elementy pozostają takie same w przypadku tej opcji konfiguracji.

W poniższej tabeli przedstawiono, czy określony typ alokacji pamięci należy do regionu memory_to_reserve wirtualnej przestrzeni adresowej dla procesu programu SQL Server:

Dynamiczne zarządzanie pamięcią

Domyślne zachowanie zarządzania pamięcią aparatu bazy danych SQL Server polega na uzyskaniu tyle pamięci, ile jest potrzebne, bez powodowania niedoboru pamięci w systemie. Silnik bazy danych SQL Server wykonuje to przy użyciu interfejsów API powiadamiania o pamięci w systemie Microsoft Windows.

W przypadku dynamicznego używania pamięci program SQL Server okresowo wysyła zapytanie do systemu w celu określenia ilości wolnej pamięci. Utrzymywanie tej wolnej pamięci zapobiega stronicowaniu systemu operacyjnego. Jeśli mniej pamięci jest dostępnej, program SQL Server zwalnia pamięć na rzecz systemu operacyjnego. Jeśli jest więcej wolnej pamięci, program SQL Server może przydzielić więcej pamięci. Program SQL Server dodaje pamięć tylko wtedy, gdy jego obciążenie wymaga więcej pamięci; serwer w stanie spoczynku nie zwiększa rozmiaru swojej wirtualnej przestrzeni adresowej. Jeśli zauważysz, że Menedżer zadań i Monitor wydajności pokazują stały spadek dostępnej pamięci, gdy program SQL Server korzysta z dynamicznego zarządzania pamięcią, jest to zachowanie domyślne i nie powinno być postrzegane jako przeciek pamięci.

Opcje konfiguracji pamięci serwera sterują alokacją pamięci programu SQL Server, pamięcią kompilacji, wszystkimi pamięciami podręcznymi (w tym pulą buforów), przydziałem pamięci na wykonywanie zapytań, pamięcią menedżera blokad oraz pamięcią CLR¹ (zasadniczo każdego memory clerk znalezionego w sys.dm_os_memory_clerks).

¹ pamięć CLR jest zarządzana w ramach alokacji max_server_memory począwszy od SQL Server 2012 (11.x).

Następujące zapytanie zwraca informacje o aktualnie przydzielonej pamięci:

SELECT physical_memory_in_use_kb / 1024 AS sql_physical_memory_in_use_MB,
       large_page_allocations_kb / 1024 AS sql_large_page_allocations_MB,
       locked_page_allocations_kb / 1024 AS sql_locked_page_allocations_MB,
       virtual_address_space_reserved_kb / 1024 AS sql_VAS_reserved_MB,
       virtual_address_space_committed_kb / 1024 AS sql_VAS_committed_MB,
       virtual_address_space_available_kb / 1024 AS sql_VAS_available_MB,
       page_fault_count AS sql_page_fault_count,
       memory_utilization_percentage AS sql_memory_utilization_percentage,
       process_physical_memory_low AS sql_process_physical_memory_low,
       process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

Rozmiary stosu

Pamięć dla stosów wątków ¹, CLR ², plików procedur rozszerzonych .dll, dostawcy OLE DB przywołani przez zapytania rozproszone, obiekty automatyzacji przywołane w instrukcjach Transact-SQL, i wszelka pamięć przydzielona przez bibliotekę DLL programu innego niż SQL Server, nie są kontrolowane przez maksymalną pamięć serwera (MB).

¹ Zapoznaj się z artykułem dotyczącym konfigurowania maksymalnej liczby wątków roboczych (opcja konfiguracji serwera), aby uzyskać informacje na temat obliczonych domyślnych wątków roboczych dla danej liczby powiązanych procesorów CPU w bieżącym hoście. Rozmiary stosu programu SQL Server są następujące:

² pamięć CLR jest zarządzana dzięki alokacjom max_server_memory, począwszy od wersji SQL Server 2012 (11.x).

System SQL Server używa interfejsu API powiadomień pamięci QueryMemoryResourceNotification aby określić, kiedy menedżer pamięci może przydzielić lub zwolnić pamięć.

Po uruchomieniu programu SQL Server oblicza rozmiar wirtualnej przestrzeni adresowej puli na podstawie kilku parametrów, takich jak ilość fizycznej pamięci w systemie, liczba wątków serwera i różne parametry uruchamiania. Program SQL Server rezerwuje obliczoną ilość wirtualnej przestrzeni adresowej procesu dla puli buforowej, ale zatwierdza tylko wymaganą ilość pamięci fizycznej dla bieżącego obciążenia.

Następnie wystąpienie będzie nadal pobierać pamięć zgodnie z potrzebami w celu obsługi obciążenia. W miarę łączenia się i uruchamiania zapytań przez użytkowników program SQL Server uzyskuje więcej pamięci fizycznej na żądanie. Wystąpienie programu SQL Server nadal uzyskuje pamięć fizyczną, dopóki nie osiągnie maksymalnej wartości docelowej alokacji pamięci serwera (MB) lub system operacyjny wskazuje, że nie ma już nadmiaru wolnej pamięci; zwalnia pamięć, gdy jest to więcej niż minimalna ilość pamięci serwera, a system operacyjny wskazuje, że brakuje wolnej pamięci.

Ponieważ inne aplikacje są uruchamiane na komputerze z uruchomionym wystąpieniem programu SQL Server, zużywają pamięć i ilość wolnej pamięci fizycznej spada poniżej docelowej ilości dla SQL Server. Wystąpienie programu SQL Server dostosowuje zużycie pamięci. Jeśli inna aplikacja zostanie zatrzymana, a ilość pamięci stanie się dostępna, wystąpienie programu SQL Server zwiększa rozmiar alokacji pamięci. Program SQL Server może zwalniać i pozyskiwać zasoby pamięci o wielkości kilku megabajtów każdą sekundę, co pozwala na szybkie dostosowanie się do zmian w alokacji pamięci.

Efekty minimalnej i maksymalnej pamięci serwera

Minimalna pamięć serwera i maksymalna pamięć serwera jako opcje konfiguracji określają górne i dolne limity ilości pamięci używanej przez pulę buforową i inne pamięci podręczne silnika bazy danych. Pula buforowa nie uzyskuje natychmiast ilości pamięci określonej w parametrze minimalnej pamięci serwera. Pula buforów rozpoczyna się tylko od pamięci niezbędnej do inicjalizacji. Wraz ze wzrostem obciążenia aparatu bazy danych programu SQL Server zwiększa się ilość pamięci wymaganej do obsługi obciążenia. Bufor nie zwalnia żadnej z przydzielonej pamięci, dopóki nie osiągnie ilości określonej w minimalnej pamięci serwera. Po osiągnięciu minimalnej pamięci serwera pula buforowa używa standardowego algorytmu do uzyskiwania i zwalniania pamięci zgodnie z potrzebami. Jedyną różnicą jest to, że pula bufora nigdy nie spada poniżej alokacji pamięci określonej w minimalnej pamięci serwera (MB) i nigdy nie uzyskuje więcej pamięci niż poziom określony w maksymalnej pamięci serwera (MB).

Ilość pamięci uzyskanej przez silnik bazy danych programu SQL Server jest całkowicie zależna od obciążenia instancji. Wystąpienie programu SQL Server, które nie przetwarza wielu żądań, może nigdy nie osiągnąć wartości określonej przez minimalną pamięć serwera.

Jeśli ta sama wartość jest określona zarówno dla minimalnej pamięci serwera, jak i maksymalnej pamięci serwera (MB), to gdy pamięć przydzielona do aparatu bazy danych programu SQL Server osiągnie tę wartość, aparat bazy danych programu SQL Server zatrzymuje się dynamicznie zwalniając i uzyskując pamięć dla puli.

Jeśli wystąpienie programu SQL Server jest uruchomione na komputerze, na którym inne aplikacje są często zatrzymywane lub uruchamiane, alokacja i dealokacja pamięci przez wystąpienie programu SQL Server może spowolnić czas uruchamiania innych aplikacji. Ponadto jeśli program SQL Server jest jedną z kilku aplikacji serwerowych działających na jednym komputerze, administratorzy systemu powinni kontrolować ilość pamięci przydzielonej do programu SQL Server. W takich przypadkach można użyć opcji minimalnej pamięci serwera i maksymalnej pamięci serwera (MB), aby kontrolować ilość pamięci programu SQL Server. Minimalna pamięć serwera i maksymalne opcje pamięci serwera są określone w megabajtach. Aby uzyskać więcej informacji, w tym zalecenia dotyczące ustawiania tych konfiguracji pamięci, zobacz Opcje konfiguracji pamięci serwera.

Pamięć używana przez specyfikacje obiektów programu SQL Server

Na poniższej liście opisano przybliżoną ilość pamięci używanej przez różne obiekty w programie SQL Server. Wymienione kwoty są szacowane i mogą się różnić w zależności od środowiska i sposobu tworzenia obiektów:

• Blokada (utrzymywana przez menedżera blokady): 64 bajty + 32 bajty na właściciela
• Połączenie użytkownika: około (3 * network_packet_size + 94 KB)

Rozmiar pakietu sieciowego to rozmiar pakietów strumienia danych tabelarycznych (TDS), które są używane do komunikacji między aplikacjami a aparatem bazy danych. Domyślny rozmiar pakietu to 4 KB i jest kontrolowany przez opcję konfiguracji rozmiaru pakietów sieciowych.

Po włączeniu wielu aktywnych zestawów wyników (MARS) połączenie użytkownika szacuje się na około (3 + 3 * num_logical_connections) * network_packet_size + 94 KB.

Efekty minimalnej ilości pamięci na zapytanie

Minimalna ilość pamięci na konfigurację zapytania określa minimalną ilość pamięci (w kilobajtach), która zostanie przydzielona do wykonania zapytania. Jest to również nazywane minimalnym przydziałem pamięci. Wszystkie zapytania muszą czekać, aż będzie można zabezpieczyć minimalną żądaną pamięć, przed rozpoczęciem wykonywania lub do momentu przekroczenia wartości określonej w opcji konfiguracji serwera oczekiwania zapytania. Typ oczekiwania, który jest skumulowany w tym scenariuszu, to RESOURCE_SEMAPHORE.

Rozważania dotyczące przydzielania pamięci

W przypadku wykonywania trybu wiersza nie można przekroczyć przydziału pamięci początkowej w żadnym warunku. Jeśli do wykonania operacji skrótu lub sortowania potrzebna jest większa ilość pamięci niż początkowa, operacje przenoszą się na dysk. Operacja mieszania, która wycieka, jest obsługiwana przez plik Workfile w tempdb, podczas gdy operacja sortowania, która wycieka, jest obsługiwana przez tabelę Worktable.

Wyciek występujący podczas operacji sortowania jest znany jako Klasa zdarzeń ostrzeżeń dotyczących sortowania. Ostrzeżenia sortowania wskazują, że operacje sortowania nie mieszczą się w pamięci. Nie obejmuje to operacji sortowania związanych z tworzeniem indeksów, tylko operacje sortowania wykonywane w zapytaniu (takie jak klauzula ORDER BY użyta w instrukcji SELECT).

Wyciek, który występuje podczas operacji skrótu, jest znany jako klasa zdarzeń ostrzegawczych dotyczących skrótu. Występują one, gdy podczas operacji tworzenia skrótu wystąpiła rekursja skrótu lub zaprzestanie tworzenia skrótów (ratowanie skrótu).

• Rekursja skrótu występuje, gdy dane wejściowe kompilacji nie mieszczą się w dostępnej pamięci, co powoduje podzielenie danych wejściowych na wiele partycji, które są przetwarzane oddzielnie. Jeśli którakolwiek z tych partycji nadal nie pasuje do dostępnej pamięci, jest podzielona na części, które są również przetwarzane oddzielnie. Ten proces dzielenia będzie kontynuowany do momentu, aż każda partycja zmieści się w dostępnej pamięci lub do momentu osiągnięcia maksymalnego poziomu rekursji.
• Awaryjne zakończenie operacji skrótu występuje, gdy operacja tworzenia skrótów osiągnie maksymalny poziom rekursji i przełącza się na alternatywne rozwiązanie do przetwarzania pozostałych danych partycjonowanych. Te zdarzenia mogą spowodować zmniejszenie wydajności serwera.

** W przypadku wykonania w trybie wsadowym początkowe przyznanie pamięci może się dynamicznie zwiększyć do określonego wewnętrznego progu domyślnego. Ten mechanizm dynamicznego przydziału pamięci został zaprojektowany tak, aby umożliwić wykonywanie operacji skrótu lub sortowania w pamięci działających w trybie wsadowym. Jeśli te operacje nadal nie mieszczą się w pamięci, operacje są przenoszone na dysk.

Aby uzyskać więcej informacji na temat trybów wykonywania, zobacz Przewodnik po architekturze przetwarzania zapytań.

Zarządzanie buforem

Podstawowym celem bazy danych programu SQL Server jest przechowywanie i pobieranie danych, dlatego intensywne operacje we/wy dysku są główną cechą aparatu bazy danych. Ponieważ operacje we/wy dysku mogą zużywać wiele zasobów i trwać stosunkowo długo, program SQL Server koncentruje się na dokonaniu wysokiej wydajności operacji we/wy. Zarządzanie buforami jest kluczowym składnikiem w celu osiągnięcia tej wydajności. Składnik zarządzania buforem składa się z dwóch mechanizmów: menedżera w celu uzyskiwania dostępu do i aktualizowania stron bazy danych oraz pamięci podręcznej buforu (nazywanej również pulą) w celu zmniejszenia operacji we/wy pliku bazy danych.

Aby uzyskać szczegółowe wyjaśnienie operacji we/wy dysku w programie SQL Server, zobacz Podstawy we/wy programu SQL Server.

Jak działa zarządzanie buforami

Bufor to strona o rozmiarze 8 KB w pamięci, taka sama jak strona danych lub indeksu. W związku z tym pamięć podręczna buforu jest podzielona na strony o rozmiarze 8 KB. Menedżer zarządza funkcjami odczytu danych lub stron indeksu z plików dysku bazy danych do pamięci podręcznej buforu i zapisywania zmodyfikowanych stron z powrotem na dysku. Strona pozostaje w pamięci podręcznej buforu, dopóki menedżer buforu nie potrzebuje obszaru buforu, aby odczytać więcej danych. Dane są zapisywane z powrotem na dysku tylko wtedy, gdy są modyfikowane. Dane w pamięci podręcznej buforu można modyfikować wiele razy przed zapisaniem z powrotem na dysku. Aby uzyskać więcej informacji, zobacz Odczytywanie stron i Zapisywanie stron.

Po uruchomieniu programu SQL Server oblicza rozmiar wirtualnej przestrzeni adresowej pamięci podręcznej buforu na podstawie kilku parametrów, takich jak ilość pamięci fizycznej w systemie, skonfigurowana liczba maksymalnych wątków serwera i różne parametry uruchamiania. Program SQL Server rezerwuje tę obliczoną ilość wirtualnej przestrzeni adresowej procesu (nazywanej obiektem docelowym pamięci) dla pamięci podręcznej buforu, ale uzyskuje (zatwierdza) tylko wymaganą ilość pamięci fizycznej dla bieżącego obciążenia. Możesz wykonać zapytanie dotyczące kolumn committed_target_kb i committed_kb w widoku katalogu sys.dm_os_sys_info, aby zwrócić liczbę stron zarezerwowanych jako pamięci docelowej oraz liczbę stron aktualnie zatwierdzonych w pamięci podręcznej bufora.

Interwał między uruchomieniem programu SQL Server a uzyskaniem docelowej pamięci podręcznej bufora jest nazywany rozruchem. W tym czasie żądania odczytu wypełniają bufory w miarę potrzeb. Na przykład jedno żądanie odczytu strony 8 KB wypełnia pojedynczą stronę buforu. Oznacza to, że intensyfikacja procesów zależy od liczby i rodzaju żądań klientów. Zwiększenie skali jest przyspieszane przez przekształcenie pojedynczych żądań odczytu na jedną stronę w żądania odczytu na osiem stron ułożone w sposób zgodny (tworząc jeden obszar pamięci). Dzięki temu można znacznie szybciej zakończyć pracę, szczególnie na maszynach z dużą ilością pamięci. Aby uzyskać więcej informacji na temat stron i zakresów, zobacz Podręcznik architektury stron i zakresów.

Ponieważ menedżer buforów używa większości pamięci w procesie SQL Server, współpracuje z menedżerem pamięci, aby umożliwić innym składnikom korzystanie ze swoich buforów. Menedżer bufora współdziała przede wszystkim z następującymi składnikami:

• Resource Manager do zarządzania ogólnym użyciem pamięci i, na 32-bitowych platformach, kontrolowania użycia przestrzeni adresowej.
• Menedżer baz danych i system operacyjny programu SQL Server (SQLOS) na potrzeby operacji we/wy na plikach niskiego poziomu.
• Menedżer dzienników na potrzeby rejestrowania z wyprzedzeniem.

Obsługiwane funkcje

Menedżer bufora obsługuje następujące funkcje:

• Menedżer buforu jest świadomy architektury niejednolitego dostępu do pamięci (NUMA). Strony pamięci podręcznej buforu są dystrybuowane między sprzętowe węzły NUMA, co umożliwia wątkowi dostęp do strony buforu przydzielonej w lokalnym węźle NUMA, a nie z pamięci obcej.

• Menedżer buforów obsługuje Hot Add Memory, co umożliwia użytkownikom dodawanie pamięci fizycznej bez ponownego uruchamiania serwera.

• Menedżer bufora obsługuje duże strony na platformach 64-bitowych. Rozmiar strony jest specyficzny dla wersji systemu Windows.

  Uwaga / Notatka

  Przed programem SQL Server 2012 (11.x) włączenie dużych stron w programie SQL Server wymaga flagi śledzenia 834.

• Menedżer buforów zapewnia dodatkowe diagnostyki dostępne przez dynamiczne widoki zarządzania. Za pomocą tych widoków można monitorować różne zasoby systemu operacyjnego specyficzne dla programu SQL Server. Na przykład można użyć widoku sys.dm_os_buffer_descriptors do monitorowania stron w pamięci podręcznej buforu.

Wykrywanie ciśnienia pamięci

Ciśnienie pamięci jest stanem wynikającym z niedoboru pamięci i może spowodować:

• Dodatkowe operacje I/O (takie jak bardzo aktywny wątek leniwego zapisu w tle)
• Wyższy współczynnik ponownego kompilu
• Długotrwałe zapytania (jeśli występują oczekiwania na przydział pamięci)
• Dodatkowe cykle procesora CPU

Taka sytuacja może być wyzwalana przez przyczyny zewnętrzne lub wewnętrzne. Przyczyny zewnętrzne obejmują:

• Dostępna pamięć fizyczna (RAM) jest niska. Powoduje to, że system obcina zestawy robocze procesów, które są aktualnie uruchomione, co może prowadzić do ogólnego spowolnienia. Program SQL Server może zmniejszyć cel zatwierdzenia puli buforowej i częściej rozpocząć redukowanie wewnętrznych pamięci podręcznych.
• Ogólna ilość dostępnej pamięci systemowej (w tym pliku strony systemu) jest niska. Może to spowodować awarię alokacji pamięci przez system, ponieważ nie można odstronicować obecnie przydzielonej pamięci.

Przyczyny wewnętrzne obejmują:

• Reagowanie na zewnętrzne obciążenie pamięci, gdy mechanizm bazy danych programu SQL Server ustala niższe ograniczenia użycia pamięci.
• Ustawienia pamięci zostały ręcznie obniżone przez zmniejszenie maksymalnej konfiguracji pamięci serwera .
• Zmiany w dystrybucji pamięci składników wewnętrznych pomiędzy różnymi cache.

Aparat bazy danych programu SQL Server implementuje platformę dedykowaną do wykrywania i obsługi ciśnienia pamięci w ramach dynamicznego zarządzania pamięcią. Ta struktura obejmuje zadanie w tle o nazwie Monitor zasobów. Zadanie Monitor zasobów monitoruje stan wskaźników pamięci zewnętrznej i wewnętrznej. Gdy jeden z tych wskaźników zmieni stan, oblicza odpowiednie powiadomienie i emituje je. Powiadomienia te są wewnętrznymi komunikatami z poszczególnych komponentów silnika i przechowywane w buforach pierścieniowych.

Dwa bufory pierścieniowe przechowują informacje istotne dla dynamicznego zarządzania pamięcią.

• Bufor pierścieniowy Monitora Zasobów, który śledzi jego aktywność, na przykład czy sygnalizowano presję pamięci czy nie. Ten bufor pierścieniowy zawiera informacje o stanie, zależnie od bieżącego stanu RESOURCE_MEMPHYSICAL_HIGH, RESOURCE_MEMPHYSICAL_LOW, RESOURCE_MEMPHYSICAL_STEADY lub RESOURCE_MEMVIRTUAL_LOW.
• Bufor pierścienia brokera pamięci, który zawiera rekordy powiadomień o pamięci dla każdej puli zasobów zarządcy zasobów. Gdy zostanie wykryte ciśnienie pamięci wewnętrznej, powiadomienia o małej ilości pamięci są uruchamiane dla składników, które przydzielają pamięć, w celu wywołania działań mających na celu zrównoważenie pamięci między różnymi pamięciami podręcznymi.

Brokerzy pamięci monitorują zużycie pamięci przez każdy składnik, a następnie na podstawie zebranych informacji oblicza i optymalną wartość pamięci dla każdego z tych składników. Istnieje zestaw brokerów dla każdej puli zasobów zarządcy zasobów. Te informacje są następnie przekazywane do każdego ze składników, które zwiększają lub zmniejszają swoje użycie zgodnie z wymaganiami.

Aby uzyskać więcej informacji na temat brokerów pamięci, zobacz sys.dm_os_memory_brokers.

Wykrywanie błędów

Strony bazy danych mogą używać jednego z dwóch opcjonalnych mechanizmów, które pomagają zapewnić integralność strony, od momentu zapisania na dysku do chwili ponownego odczytania: ochrona przed rozdarciem strony i ochrona za pomocą sumy kontrolnej. Mechanizmy te umożliwiają niezależną metodę weryfikowania poprawności nie tylko magazynu danych, ale także składników sprzętowych, takich jak kontrolery, sterowniki,, a nawet system operacyjny. Ochrona jest dodawana do strony tuż przed zapisaniem jej na dysku i zweryfikowana po jej odczytaniu z dysku.

Program SQL Server ponawia próbę odczytu, który kończy się niepowodzeniem z sumą kontrolną, rozdartą stroną lub innym błędem we/wy cztery razy. Jeśli odczyt zakończy się pomyślnie w dowolnej z prób ponawiania próby, komunikat zostanie zapisany w dzienniku błędów i polecenie, które wyzwoliło odczyt będzie kontynuowane. Jeśli próby ponowienia się nie powiodą, polecenie zakończy się niepowodzeniem z błędem MSSQLSERVER_824.

Rodzaj używanej ochrony stron jest atrybutem bazy danych zawierającej stronę. Ochrona sumy kontrolnej to domyślna ochrona baz danych utworzonych w programie SQL Server 2005 (9.x) i nowszych wersjach. Mechanizm ochrony strony jest określony w czasie tworzenia bazy danych i można go zmienić za pomocą polecenia ALTER DATABASE SET. Bieżące ustawienie ochrony strony można określić, wykonując zapytanie dotyczące kolumny page_verify_option w widoku katalogu sys.databases lub właściwości IsTornPageDetectionEnabled funkcji DATABASEPROPERTYEX.

Ochrona strony rozdartej

Ochrona przed rozdarciem stron, wprowadzona w programie SQL Server 2000 (8.x), jest przede wszystkim sposobem wykrywania uszkodzeń stron z powodu awarii zasilania. Na przykład nieoczekiwana awaria zasilania może pozostawić tylko część strony zapisanej na dysku. Gdy stosowana jest ochrona przed rozdarciem strony, określony 2-bitowy wzorzec sygnatury dla każdego sektora o wielkości 512 bajtów na stronie bazy danych o pojemności 8 kilobajtów (KB) jest przechowywany w nagłówku strony bazy danych, gdy strona jest zapisywana na dysk.

Gdy strona jest odczytywana z dysku, rozdarte bity przechowywane w nagłówku strony są porównywane z rzeczywistymi informacjami o sektorze strony. Wzorzec sygnatury zmienia się pomiędzy binarnym 01 i 10 przy każdym zapisie, dlatego zawsze można stwierdzić, kiedy tylko część sektorów trafiła na dysk: jeśli bit znajduje się w niewłaściwym stanie podczas późniejszego odczytu strony, oznacza to, że strona została zapisana niepoprawnie i wykryto rozdartą stronę. Wykrywanie rozdartej strony używa minimalnych zasobów; jednak nie wykrywa wszystkich błędów spowodowanych awariami sprzętu dysku. Aby uzyskać informacje na temat ustawiania wykrywania rozdartej strony, zobacz ALTER DATABASE SET Options (Transact-SQL).

Ochrona sumy kontrolnej

Ochrona sumy kontrolnej wprowadzona w programie SQL Server 2005 (9.x) zapewnia silniejsze sprawdzanie integralności danych. Suma kontrolna jest obliczana dla danych na każdej stronie, które są zapisywane i przechowywane w nagłówku strony. Za każdym razem, gdy strona z przechowywaną sumą kontrolną jest odczytywana z dysku, aparat bazy danych ponownie oblicza sumę kontrolną danych na stronie i zgłasza błąd 824, jeśli nowa suma kontrolna różni się od przechowywanej sumy kontrolnej. Ochrona sumy kontrolnej może przechwytywać więcej błędów niż ochrona przed rozdarciem strony, ponieważ wpływa na każdy bajt na stronie, jednak wykorzystuje umiarkowane zasoby.

Po włączeniu sumy kontrolnej błędy spowodowane awariami zasilania i wadliwym sprzętem lub oprogramowaniem układowym mogą być wykrywane za każdym razem, gdy menedżer buforu odczytuje stronę z dysku. Aby uzyskać informacje na temat ustawiania sumy kontrolnej, zobacz ALTER DATABASE SET Options (Transact-SQL).

Zrozumienie pamięci o niejednolitym dostępie

SQL Server jest świadomy architektury niejednolitego dostępu do pamięci (NUMA) i działa dobrze na sprzęcie NUMA bez specjalnej konfiguracji. W miarę wzrostu szybkości zegara i liczby procesorów coraz trudniej jest zmniejszyć opóźnienie pamięci wymagane do korzystania z tej dodatkowej mocy obliczeniowej. Aby obejść ten problem, dostawcy sprzętu zapewniają duże pamięci podręczne L3, ale jest to tylko ograniczone rozwiązanie. Architektura NUMA zapewnia skalowalne rozwiązanie tego problemu.

Program SQL Server jest przeznaczony do korzystania z komputerów opartych na architekturze NUMA bez konieczności wprowadzania żadnych zmian w aplikacji. Aby uzyskać więcej informacji, zobacz Soft-NUMA (SQL Server).

Partycja dynamiczna obiektów pamięci

Alokatory sterta, znane jako obiekty pamięci w programie SQL Server, umożliwiają silnikowi bazy danych przydzielanie pamięci ze sterta. Można je śledzić przy użyciu sys.dm_os_memory_objects DMV.

CMemThread jest typem obiektu pamięci bezpiecznej wątkowo, który umożliwia współbieżne alokacje pamięci z wielu wątków. W celu poprawnego śledzenia obiekty bazują na konstrukcjach synchronizacji (mutex), aby upewnić się, CMemThread że tylko jeden wątek aktualizuje krytyczne elementy informacji naraz.

Jednak użycie muteksów może prowadzić do zatorów, jeśli wiele wątków przydziela pamięć z tego samego obiektu w sposób wysoce współbieżny. W związku z tym program SQL Server ma koncepcję partycjonowanych obiektów pamięci (PMO), a każda partycja jest reprezentowana przez pojedynczy CMemThread obiekt. Partycjonowanie obiektu pamięci jest definiowane statycznie i nie można go zmienić po utworzeniu. Ponieważ wzorce alokacji pamięci różnią się znacznie w zależności od aspektów, takich jak użycie sprzętu i pamięci, nie można wymyślić idealnego wzorca partycjonowania z góry.

W większości przypadków użycie jednej partycji jest wystarczające, ale w niektórych scenariuszach może to prowadzić do rywalizacji, której można zapobiec tylko w przypadku wysoce partycjonowanego obiektu pamięci. Nie jest pożądane partycjonowanie każdego obiektu pamięci, ponieważ więcej partycji może spowodować inną nieefektywność i zwiększyć fragmentację pamięci.

Począwszy od serwera SQL Server 2014 (12.x) z dodatkiem SP2 i serwera SQL Server 2016 (13.x), aparat bazy danych może dynamicznie wykrywać konflikt dotyczący określonego obiektu CMemThread i promować obiekt do implementacji opartej na poszczególnych węzłach lub CPU. Po awansowaniu PMO, pozostaje on awansowany do momentu ponownego uruchomienia procesu SQL Server. CMemThreadKonflikt można wykryć przez obecność wysokich CMEMTHREAD oczekiwań w DMV sys.dm_os_wait_stats oraz poprzez obserwację kolumn sys.dm_os_memory_objects, contention_factor, partition_type, exclusive_allocations_count i waiting_tasks_count.

Treści powiązane

• Podstawy we/wy programu SQL Server
• opcje konfiguracji pamięci serwera
• Odczytywanie stron
• Pisanie Stron
• Soft-NUMA (SQL Server)
• Wymagania dotyczące używania tabel zoptymalizowanych pod kątem pamięci
• Rozwiązywanie problemów z brakiem pamięci lub małą ilością pamięci w programie SQL Server
• Rozwiązywanie problemów z brakiem pamięci