Rozwiązywanie problemów z brakiem pamięci lub małą ilością pamięci w SQL Server

Artykuł
03/20/2024

Symptomy

SQL Server używa złożonej architektury pamięci, która odpowiada złożonemu i bogatemu zestawowi funkcji. Ze względu na różne potrzeby w zakresie pamięci może istnieć wiele źródeł zużycia pamięci i nacisku pamięci, co ostatecznie powoduje brak warunków pamięci.

Występują typowe błędy wskazujące małą ilość pamięci w SQL Server. Przykłady błędów obejmują:

701: Nie można przydzielić wystarczającej ilości pamięci do uruchomienia zapytania.
802: Nie można pobrać pamięci w celu przydzielenia stron w puli buforów (danych lub stron indeksu).
1204: Nie można przydzielić pamięci do blokad.
6322: Nie można przydzielić pamięci do analizatora XML.
6513: Nie można zainicjować środowiska CLR z powodu nacisku pamięci.
6533: Usługa AppDomain została wyładowana z powodu braku pamięci.
8318: Nie można załadować liczników wydajności SQL z powodu niewystarczającej ilości pamięci.
8356 lub 8359: Nie można uruchomić śledzenia ETW lub SQL z powodu małej ilości pamięci.
8556: Nie można załadować msdtc z powodu niewystarczającej ilości pamięci.
8645: Nie można wykonać zapytania z powodu braku pamięci na potrzeby przyznawania pamięci (sortowanie i skróty) Aby uzyskać więcej informacji, zobacz Jak rozwiązać problem z błędem SQL Server 8645.
8902: Nie można przydzielić pamięci podczas wykonywania DBCC.
9695 lub 9696: Nie można przydzielić pamięci dla operacji usługi Service Broker.
17131 lub 17132: Niepowodzenie uruchamiania serwera z powodu niewystarczającej ilości pamięci.
17890: Nie można przydzielić pamięci z powodu stronicowania pamięci SQL przez system operacyjny.
22986 lub 22987: Błędy przechwytywania danych zmiany spowodowane niewystarczającą ilością pamięci.
25601: Aparat Xevent nie ma pamięci.
26053: Nie można zainicjować interfejsów sieciowych SQL z powodu niewystarczającej ilości pamięci.
30085, 30086, 30094: Operacje pełnotekstowe SQL nie powiodły się z powodu niewystarczającej ilości pamięci.

Przyczyna

Wiele czynników może powodować niewystarczającą ilość pamięci. Takie czynniki obejmują ustawienia systemu operacyjnego, dostępność pamięci fizycznej, składniki korzystające z pamięci wewnątrz SQL Server i limity pamięci dla bieżącego obciążenia. W większości przypadków zapytanie, które kończy się niepowodzeniem z powodu błędu braku pamięci, nie jest przyczyną tego błędu. Ogólnie rzecz biorąc, przyczyny można pogrupować na trzy kategorie:

Przyczyna 1: Obciążenie pamięcią zewnętrzną lub systemu operacyjnego

Nacisk zewnętrzny odnosi się do wysokiego wykorzystania pamięci pochodzącego ze składnika spoza procesu, który prowadzi do niewystarczającej ilości pamięci dla SQL Server. Musisz dowiedzieć się, czy inne aplikacje w systemie zużywają pamięć i przyczyniają się do niskiej dostępności pamięci. SQL Server jest jedną z niewielu aplikacji zaprojektowanych w celu reagowania na obciążenie pamięcią systemu operacyjnego przez ograniczenie użycia pamięci. Oznacza to, że jeśli aplikacja lub sterownik żąda pamięci, system operacyjny wysyła sygnał do wszystkich aplikacji w celu zwolnienia pamięci, a SQL Server będzie reagować przez zmniejszenie użycia własnej pamięci. Kilka innych aplikacji odpowiada, ponieważ nie są one przeznaczone do nasłuchiwania tego powiadomienia. W związku z tym, jeśli SQL Server zacznie ograniczać użycie pamięci, jej pula pamięci zostanie zmniejszona i niezależnie od tego, które składniki potrzebują pamięci, może nie zostać pobrana. W związku z tym zaczniesz otrzymywać błędy 701 lub inne związane z pamięcią. Aby uzyskać więcej informacji na temat dynamicznego przydzielania i zwalniania pamięci przez program SQL, zobacz SQL Server Architektura pamięci. Aby uzyskać bardziej szczegółową diagnostykę i rozwiązania problemu, zobacz Zewnętrzne wykorzystanie pamięci w tym artykule.

Istnieją trzy szerokie kategorie problemów, które mogą powodować obciążenie pamięci systemu operacyjnego:

Problemy związane z aplikacją: jedna lub wiele aplikacji razem wyczerpuje dostępną pamięć fizyczną. System operacyjny odpowie na nowe żądania aplikacji dotyczące zasobów, próbując zwolnić trochę pamięci. Typowym podejściem jest znalezienie aplikacji, które wyczerpują pamięć, i podjęcie niezbędnych kroków w celu zrównoważenia pamięci między nimi bez prowadzenia do wyczerpania pamięci RAM.
Problemy ze sterownikami urządzeń: sterowniki urządzeń mogą powodować stronicowanie zestawu roboczego wszystkich procesów, jeśli sterownik nieprawidłowo wywołuje funkcję alokacji pamięci.
Problemy z produktami systemu operacyjnego.

Aby uzyskać szczegółowe objaśnienie tych czynności i rozwiązywania problemów, zobacz MSSQLSERVER_17890.

Przyczyna 2: Wewnętrzne ciśnienie pamięci, które nie pochodzi z SQL Server

Wewnętrzne wykorzystanie pamięci odnosi się do niskiej dostępności pamięci spowodowanej czynnikami wewnątrz procesu SQL Server. Niektóre składniki, które mogą działać wewnątrz procesu SQL Server, są "zewnętrzne" dla aparatu SQL Server. Przykłady obejmują dostawców OLE DB (DLL), takich jak serwery połączone, procedury lub funkcje SQLCLR, procedury rozszerzone (XPs) i automatyzacja OLE (sp_OA*). Inne obejmują programy antywirusowe lub inne programy zabezpieczające, które wstrzykują biblioteki DLL wewnątrz procesu do celów monitorowania. Problem lub nieprawidłowy projekt w dowolnym z tych składników może prowadzić do dużego zużycia pamięci. Rozważmy na przykład połączony serwer buforujący 20 milionów wierszy danych ze źródła zewnętrznego do pamięci SQL Server. Jeśli chodzi o SQL Server, żaden urzędnik zajmujący się pamięcią nie zgłosi wysokiego użycia pamięci, ale ilość pamięci zużywanej w procesie SQL Server będzie wysoka. Ten wzrost pamięci z połączonej biblioteki DLL serwera, na przykład, spowodowałoby SQL Server rozpocząć cięcia użycia pamięci (patrz powyżej) i spowoduje utworzenie niskiej ilości pamięci dla składników wewnątrz SQL Server, powodując błędy pamięci. Aby uzyskać bardziej szczegółową diagnostykę i rozwiązania problemu, zobacz Wewnętrzne wykorzystanie pamięci, które nie pochodzi z SQL Server.

Uwaga

Kilka bibliotek DLL firmy Microsoft używanych w przestrzeni procesu SQL Server (na przykład MSOLEDBSQL, SQL Native Client) umożliwia interfejs z infrastrukturą pamięci SQL Server na potrzeby raportowania i alokacji. Możesz uruchomić polecenie select * from sys.dm_os_memory_clerks where type='MEMORYCLERK_HOST' , aby uzyskać ich listę i śledzić użycie pamięci dla niektórych alokacji.

Przyczyna 3. Wewnętrzne ciśnienie pamięci pochodzące z SQL Server składników

Wewnętrzne wykorzystanie pamięci pochodzące ze składników wewnątrz aparatu SQL Server może również prowadzić do błędów braku pamięci. Istnieją setki składników śledzonych za pośrednictwem urzędników pamięci, które przydzielają pamięć w SQL Server. Aby rozwiązać ten problem, musisz określić, którzy urzędnicy zajmujący się pamięcią są odpowiedzialni za największe alokacje pamięci. Jeśli na przykład okaże się, że OBJECTSTORE_LOCK_MANAGER urzędnik ds. pamięci pokazuje dużą alokację pamięci, musisz zrozumieć, dlaczego Menedżer blokad zużywa tyle pamięci. Może się okazać, że istnieją zapytania, które uzyskują wiele blokad. Te zapytania można zoptymalizować przy użyciu indeksów, skracając wszystkie transakcje, które przechowują blokady przez długi czas, lub sprawdzając, czy eskalacja blokady jest wyłączona. Każdy urzędnik pamięci lub składnik ma unikatowy sposób uzyskiwania dostępu do pamięci i korzystania z niej. Aby uzyskać więcej informacji, zobacz typy urzędników pamięci i ich opisy. Aby uzyskać bardziej szczegółową diagnostykę i rozwiązania problemu, zobacz Wewnętrzne użycie pamięci przez aparat SQL Server.

Wizualna reprezentacja typów nacisku pamięci

Na poniższym wykresie przedstawiono typy nacisku, które mogą prowadzić do braku pamięci w SQL Server:

Zrzut ekranu przedstawiający typy nacisku pamięci.

Narzędzia diagnostyczne do zbierania danych rozwiązywania problemów

Do zbierania danych dotyczących rozwiązywania problemów można użyć następujących narzędzi diagnostycznych:

monitor wydajności

Skonfiguruj i zbierz następujące liczniki za pomocą monitor wydajności:

Memory:Available MBytes
Process:Working Set
Proces:Bajty prywatne
SQL Server:Menedżer pamięci: (wszystkie liczniki)
SQL Server:Buffer Manager: (wszystkie liczniki)

DMV lub DBCC MEMORYSTATUS

Za pomocą sys.dm_os_memory_clerks lub DBCC MEMORYSTATUS można obserwować ogólne użycie pamięci wewnątrz SQL Server.

Raport standardowego użycia pamięci w programie SSMS

Wyświetlanie użycia pamięci w SQL Server Management Studio:

Uruchom SQL Server Management Studio i połącz się z serwerem.
W Eksplorator obiektów kliknij prawym przyciskiem myszy nazwę wystąpienia SQL Server.
W menu kontekstowym wybierz pozycję Raporty>zużycie pamięci w raportach>standardowych.

PSSDiag lub SQL LogScout

Alternatywnym, zautomatyzowanym sposobem przechwytywania tych punktów danych jest użycie narzędzi, takich jak PSSDiag lub SQL LogScout.

Jeśli używasz narzędzia PSSDiag, skonfiguruj go do przechwytywania modułu zbierającego Narzędzia perfmon i modułu zbierającego błędy pamięci Custom Diagnostics\SQL Memory .
Jeśli używasz narzędzia SQL LogScout, skonfiguruj go do przechwytywania scenariusza pamięci .

W poniższych sekcjach opisano bardziej szczegółowe kroki dla każdego scenariusza (zewnętrzne lub wewnętrzne ciśnienie pamięci).

Metodologia rozwiązywania problemów

Jeśli czasami pojawia się błąd braku pamięci lub przez krótki czas, może wystąpić krótkotrwały problem z pamięcią, który sam się rozwiąże. W takich przypadkach może nie być konieczne podjęcie działań. Jeśli jednak błąd występuje wiele razy w przypadku wielu połączeń i utrzymuje się przez okresy sekund lub dłużej, postępuj zgodnie z diagnostyką i rozwiązaniami w poniższych sekcjach, aby dalej rozwiązywać problemy z błędami pamięci.

Ciśnienie pamięci zewnętrznej

Aby zdiagnozować małą ilość pamięci w systemie poza procesem SQL Server, użyj następujących metod:

Zbieraj liczniki monitor wydajności. Sprawdź, czy aplikacje lub usługi inne niż SQL Server zużywają pamięć na tym serwerze, sprawdzając następujące liczniki:

Memory:Available MBytes
Process:Working Set
Proces:Bajty prywatne

Oto przykład kolekcji dzienników narzędzia Perfmon przy użyciu programu PowerShell:

clear
$serverName = $env:COMPUTERNAME
$Counters = @(
   ("\\$serverName" +"\Memory\Available MBytes"),
   ("\\$serverName" +"\Process(*)\Working Set"),
   ("\\$serverName" +"\Process(*)\Private Bytes")
)

Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 1 | ForEach-Object  {
$_.CounterSamples | ForEach-Object   {
   [pscustomobject]@{
      TimeStamp = $_.TimeStamp
      Path = $_.Path
      Value = ([Math]::Round($_.CookedValue, 3)) }
}
}

Przejrzyj dziennik zdarzeń systemowych i poszukaj błędów związanych z pamięcią (na przykład małej ilości pamięci wirtualnej).
Przejrzyj dziennik zdarzeń aplikacji pod kątem problemów z pamięcią związanych z aplikacją.

Oto przykład skryptu programu PowerShell do wykonywania zapytań w dziennikach zdarzeń systemu i aplikacji dla słowa kluczowego "pamięć". Możesz użyć innych ciągów, takich jak "zasób" do wyszukiwania:
```
Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
Get-EventLog Application -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
```
Rozwiąz wszelkie problemy z kodem lub konfiguracją dla mniej krytycznych aplikacji lub usług, aby zmniejszyć ich użycie pamięci.
Jeśli aplikacje oprócz SQL Server zużywają zasoby, spróbuj zatrzymać lub zmienić ich kolejną wersję lub rozważ uruchomienie ich na osobnym serwerze. Te kroki spomiężą obciążenie pamięci zewnętrznej.

Wewnętrzne ciśnienie pamięci, które nie pochodzi z SQL Server

Aby zdiagnozować wewnętrzne wykorzystanie pamięci spowodowane przez moduły (BIBLIOTEKI DLL) wewnątrz SQL Server, użyj następujących metod:

Jeśli SQL Server nie używa zablokowanych stron w pamięci (interfejs API AWE), większość jego pamięci znajduje odzwierciedlenie w liczniku Process:Private Bytes (SQLServrwystąpienie) w monitor wydajności. Ogólne użycie pamięci pochodzące z aparatu SQL Server znajduje odzwierciedlenie w liczniku SQL Server:Memory Manager: Total Server Memory (KB). Jeśli znajdziesz istotną różnicę między wartością Process:Private Bytes i SQL Server:Memory Manager: Total Server Memory (KB), ta różnica prawdopodobnie pochodzi z biblioteki DLL (serwer połączony, XP, SQLCLR itd.). Jeśli na przykład liczba bajtów prywatnych wynosi 300 GB, a łączna ilość pamięci serwera wynosi 250 GB, około 50 GB ogólnej pamięci w procesie pochodzi spoza aparatu SQL Server.
Jeśli SQL Server używa zablokowanych stron w pamięci (interfejs API AWE), zidentyfikowanie problemu jest trudniejsze, ponieważ monitor wydajności nie oferuje liczników AWE śledzących użycie pamięci dla poszczególnych procesów. Ogólne użycie pamięci w aparacie SQL Server znajduje odzwierciedlenie w liczniku SQL Server:Memory Manager: Total Server Memory (KB). Typowe wartości Process:Private Bytes mogą się różnić w zakresie od 300 MB do 1–2 GB. Jeśli znajdziesz znaczące użycie elementu Process:Private Bajty poza tym typowym użyciem, różnica prawdopodobnie pochodzi z biblioteki DLL (połączonego serwera, XP, SQLCLR itd.). Jeśli na przykład licznik bajtów prywatnych wynosi 4–5 GB, a SQL Server używa zablokowanych stron w pamięci (AWE), duża część bajtów prywatnych może pochodzić spoza aparatu SQL Server. Jest to technika przybliżenia.
Użyj narzędzia Tasklist, aby zidentyfikować wszystkie biblioteki DLL ładowane wewnątrz SQL Server przestrzeni:
```
tasklist /M /FI "IMAGENAME eq sqlservr.exe"
```
Możesz również użyć następującego zapytania, aby zbadać załadowane moduły (DLL) i sprawdzić, czy istnieje coś nieoczekiwanego.
```
SELECT * FROM sys.dm_os_loaded_modules
```
Jeśli podejrzewasz, że moduł połączonego serwera powoduje znaczne zużycie pamięci, możesz skonfigurować go tak, aby zabrakło procesu, wyłączając opcję Zezwalaj na przetwarzanie . Aby uzyskać więcej informacji, zobacz Tworzenie serwerów połączonych . Nie wszystkim dostawcom połączonego serwera OLE DB może zabraknąć procesu. Aby uzyskać więcej informacji, skontaktuj się z producentem produktu.
W rzadkich przypadkach, gdy są używane obiekty automatyzacji OLE (sp_OA*) można skonfigurować obiekt do uruchamiania w procesie poza SQL Server, określając wartość kontekstu 4 (tylko lokalny (.exe) serwer OLE). Aby uzyskać więcej informacji, zobacz sp_OACreate.

Wewnętrzne użycie pamięci przez aparat SQL Server

Aby zdiagnozować wewnętrzne ciśnienie pamięci pochodzące ze składników wewnątrz aparatu SQL Server, użyj następujących metod:

Rozpocznij zbieranie liczników monitor wydajności dla SQL Server: SQL Server:Menedżer buforów i SQL Server: Menedżer pamięci.
Wielokrotnie wysyłaj zapytanie do urzędników DMV SQL Server pamięci, aby zobaczyć, gdzie występuje największe zużycie pamięci wewnątrz aparatu:
```
SELECT pages_kb, type, name, virtual_memory_committed_kb, awe_allocated_kb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC
```
Alternatywnie możesz obserwować bardziej szczegółowe DBCC MEMORYSTATUS dane wyjściowe i sposób ich zmiany po wyświetleniu tych komunikatów o błędach.
```
DBCC MEMORYSTATUS
```
Jeśli zidentyfikowasz wyraźnego sprawcę wśród urzędników pamięci, skup się na uwzględnieniu specyfiki zużycia pamięci dla tego składnika. Oto kilka przykładów:
- Jeśli urzędnik MEMORYCLERK_SQLQERESERVATIONS pamięci zużywa pamięć, zidentyfikuj zapytania korzystające z ogromnych dotacji na pamięć i zoptymalizuj je za pośrednictwem indeksów, zapisz je ponownie (na przykład usuń ORDER by,) lub zastosuj wskazówki dotyczące zapytań dotyczących udzielania pamięci (zobacz wskazówki dotyczące min_grant_percent i max_grant_percent ). Można również utworzyć pulę zarządcy zasobów w celu kontrolowania użycia pamięci udzielania pamięci. Aby uzyskać szczegółowe informacje na temat przyznawania pamięci, zobacz Rozwiązywanie problemów z niską wydajnością lub małą ilością pamięci spowodowanych przez przyznawanie pamięci w SQL Server.
- Jeśli duża liczba planów zapytań ad hoc zostanie zapisana w pamięci podręcznej CACHESTORE_SQLCP , urzędnik pamięci użyje dużej ilości pamięci. Zidentyfikuj zapytania nieparametryzowane, których planów zapytań nie można użyć ponownie, i sparametryzuj je, konwertując je na procedury składowane, używając sp_executesqlpolecenia lub przy użyciu FORCED parametryzacji. Jeśli włączono flagę śledzenia 174, możesz ją wyłączyć, aby sprawdzić, czy to rozwiąże problem.
- Jeśli magazyn CACHESTORE_OBJCP pamięci podręcznej planu obiektów zużywa zbyt dużo pamięci, określ, które procedury składowane, funkcje lub wyzwalacze używają dużych ilości pamięci i ewentualnie przeprojektuj aplikację. Często może to być spowodowane dużą ilością baz danych lub schematów z setkami procedur w każdym z nich.
- Jeśli urzędnik OBJECTSTORE_LOCK_MANAGER pamięci pokazuje duże alokacje pamięci, zidentyfikuj zapytania, które stosują wiele blokad, i zoptymalizuj je przy użyciu indeksów. Skróć transakcje, które powodują, że blokady nie są zwalniane przez długi czas w niektórych poziomach izolacji lub sprawdź, czy eskalacja blokady jest wyłączona.
- Jeśli zauważysz bardzo duże TokenAndPermUserStore (select type, name, pages_kb from sys.dm_os_memory_clerks where name = 'TokenAndPermUserStore'), możesz użyć flagi śledzenia 4618 , aby ograniczyć rozmiar pamięci podręcznej.
- Jeśli zauważysz problemy z pamięcią związane z In-Memory OLTP pochodzące od urzędnika ds. pamięci, możesz zapoznać się z MEMORYCLERK_XTP artykułem Monitorowanie i rozwiązywanie problemów z użyciem pamięci dla In-Memory metadanych OLTP i metadanych bazy danych tempdb zoptymalizowanych pod kątem pamięci (HkTempDB) z powodu błędów pamięci.

Szybka ulga, która może udostępnić pamięć

Następujące akcje mogą zwolnić trochę pamięci i udostępnić ją SQL Server:

Zmienianie ustawień konfiguracji pamięci

Sprawdź następujące parametry konfiguracji pamięci SQL Server i rozważ zwiększenie maksymalnej ilości pamięci serwera, jeśli to możliwe:

maksymalna ilość pamięci serwera
minimalna pamięć serwera

Uwaga

Jeśli zauważysz nietypowe ustawienia, popraw je w razie potrzeby i uwzględnij zwiększone wymagania dotyczące pamięci. Ustawienia domyślne są wyświetlane w opcjach konfiguracji pamięci serwera.

Jeśli nie skonfigurowano maksymalnej ilości pamięci serwera, zwłaszcza w przypadku zablokowanych stron w pamięci, rozważ ustawienie jej na określoną wartość, aby zezwolić na ilość pamięci dla systemu operacyjnego. Zobacz opcję Konfiguracja serwera zablokowanych stron w pamięci .

Zmienianie lub przenoszenie obciążenia poza system

Zbadaj obciążenie zapytań: liczbę równoczesnych sesji, obecnie wykonujących zapytania i sprawdź, czy istnieją mniej krytyczne aplikacje, które mogą zostać tymczasowo zatrzymane lub przeniesione do innego SQL Server.

W przypadku obciążeń tylko do odczytu rozważ przeniesienie ich do repliki pomocniczej tylko do odczytu w środowisku zawsze włączonym. Aby uzyskać więcej informacji, zobacz Odciążanie obciążenia tylko do odczytu do repliki pomocniczej zawsze włączonej grupy dostępności i Konfigurowanie dostępu tylko do odczytu do repliki pomocniczej zawsze włączonej grupy dostępności.

Zapewnianie odpowiedniej konfiguracji pamięci dla maszyn wirtualnych

Jeśli używasz SQL Server na maszynie wirtualnej, upewnij się, że pamięć maszyny wirtualnej nie jest nadmiernie zastępowana. Aby zapoznać się z pomysłami dotyczącymi konfigurowania pamięci dla maszyn wirtualnych, zobacz Virtualization — Overcommitting memory and how to detect it within the VM and Troubleshooting ESX/ESXi virtual machine performance issues (Overcommitment) (Wirtualizacja — nadmierne przydzielanie pamięci) oraz Rozwiązywanie problemów z wydajnością maszyn wirtualnych ESX/ESXi.

Zwolnij pamięć wewnątrz SQL Server

Możesz uruchomić co najmniej jedno z następujących poleceń DBCC, aby zwolnić kilka SQL Server pamięci podręcznych:

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

Uruchom ponownie usługę SQL Server

W niektórych przypadkach, jeśli musisz radzić sobie z krytycznym wyczerpaniem pamięci, a SQL Server nie może przetwarzać zapytań, możesz rozważyć ponowne uruchomienie usługi.

Rozważ użycie funkcji Resource Governor w konkretnych scenariuszach

Jeśli używasz funkcji Resource Governor, zalecamy sprawdzenie ustawień puli zasobów i grupy obciążeń, aby sprawdzić, czy nie ograniczają one zbyt drastycznie pamięci.

Dodawanie większej ilości pamięci RAM na serwerze fizycznym lub wirtualnym

Jeśli problem będzie się powtarzać, należy dokładniej zbadać i ewentualnie zwiększyć zasoby serwera (RAM).