Zużycie pamięci przez SQL Server 2012 poza pulą buforów

Mam instancję programu SQL Server 2012 SP2 Enterprise Edition, która zużywa około 20 GB pamięci więcej niż maksimum. limit pamięci. Wystąpienie jest ograniczone do 65 GB, ale pamięć fizyczna używana z poniższego zapytania pokazuje 86 GB

SELECT (physical_memory_in_use_kb/1024)/1024 AS [PhysicalMemInUseGB]
FROM sys.dm_os_process_memory;
GO

Serwer jest fizyczny z 2 węzłami NUMA. Czy istnieje sposób, aby dowiedzieć się, co zajmuje pamięć poza pulą buforów (zakładam, że tak się dzieje)?

Oto wynik DBCC MEMORYSTATUS: -

A oto ustawiony limit pamięci: -

Z góry dziękuję.

AKTUALIZACJA: - Uruchomiłem zapytanie zasugerowane przez Aarona

SELECT TOP (20) * FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC

Oto wynik: -

SUM strony pages_kb wynosi ~ 60 GB

AKTUALIZACJA 2: - Pełna wersja DBCC MEMORYSTATUS znajduje się tutaj: - http://pastebin.com/nGn6kXEc

AKTUALIZACJA 3: - Dane wyjściowe skryptów Shanky w pliku Excel tutaj: - http://jmp.sh/LKRlH4K

AKTUALIZACJA 4: - Zrzut ekranu wyników: -

SELECT (physical_memory_in_use_kb/1024)/1024 AS [PhysicalMemInUseGB]
FROM sys.dm_os_process_memory;
GO

To wydaje się wskazywać, że SQL Server używa więcej niż 65 GB zestawu.

Maksymalna pamięć serwera kontroluje pulę buforów i wszystkie alokacje wielkości strony, ale nadal nie kontroluje takich rzeczy, jak bezpośrednie alokacje Windows (serwery połączone, sp_OA, XP), pamięć wymagana dla stosów wątków / wątków itp .

Prawdopodobnie możesz spodziewać się, że będzie wyższy na NUMA (chociaż nie jestem pewien, czy 20 GB jest normalne); chodzi o to, że nie można oczekiwać, że maksymalna pamięć serwera w pełni kontroluje pamięć używaną przez wystąpienie programu SQL Server. Jeśli chcesz, aby cała instancja (nie tylko pula buforów, plany pamięci podręcznej i CLR) używała nie więcej niż 64 GB, powinieneś ustawić maksymalną pamięć serwera na coś niższego.

Kilka potencjalnych pomysłów na śledzenie tego (normalizuję wszystko do MB):

• liczniki wydajności

  Sprawdź, czy coś wyskoczy tutaj jako zbyt duże:

  SELECT counter_name, instance_name, mb = cntr_value/1024.0
    FROM sys.dm_os_performance_counters 
    WHERE (counter_name = N'Cursor memory usage' and instance_name <> N'_Total')
    OR (instance_name = N'' AND counter_name IN 
         (N'Connection Memory (KB)', N'Granted Workspace Memory (KB)', 
          N'Lock Memory (KB)', N'Optimizer Memory (KB)', N'Stolen Server Memory (KB)', 
          N'Log Pool Memory (KB)', N'Free Memory (KB)')
    ) ORDER BY mb DESC;

• 20 najlepszych urzędników

  Już to zrobiłeś, ale dla kompletności:

  SELECT TOP (21) [type] = COALESCE([type],'Total'), 
    mb = SUM(pages_kb/1024.0)
  FROM sys.dm_os_memory_clerks
  GROUP BY GROUPING SETS((type),())
  ORDER BY mb DESC;

• rozmiar stosu nici

  Najpierw upewnij się, że jest to zero, a nie jakaś niestandardowa liczba (jeśli nie jest to 0, dowiedz się dlaczego i napraw ją):

  SELECT value_in_use
    FROM sys.configurations 
    WHERE name = N'max worker threads';

  Ale możesz również zobaczyć, ile pamięci zajmują stosy wątków, używając:

  SELECT stack_size_in_bytes/1024.0/1024 
    FROM sys.dm_os_sys_info;

• Załadowano moduły innych firm

  SELECT base_address, description, name
    FROM sys.dm_os_loaded_modules 
    WHERE company NOT LIKE N'Microsoft%';
  
  -- you can probably trace down memory usage using the base_address

• DMV związane z pamięcią

  Możesz także zauważyć coś niezwykłego, patrząc na te DMV:

  SELECT * FROM sys.dm_os_sys_memory;
  SELECT * FROM sys.dm_os_memory_nodes WHERE memory_node_id <> 64;

Ten artykuł został napisany przed SQL Server 2012, więc niektóre nazwy kolumn i obliczenia mogą wymagać dostosowania, ale mogą też dać inne możliwości:

• Rozwiązywanie problemów z pamięcią programu SQL Server

Dobre tło w innym artykule na tej stronie:

• Pamięć programu SQL Server 2012

Kilka dobrych informacji o rodzajach rzeczy korzystających z pamięci poza max server memory(ale brak dobrych danych o tym, jak zebrać faktyczne użycie):

• Konsumenci MTL

Mam poniżej definicji od Boba Dorra o tym, jaka pamięć Max serwera kontroluje w SQL Server 2012. Możesz również przeczytać Books Online, aby uzyskać więcej informacji

Maksymalna pamięć serwera kontroluje alokację pamięci SQL Server, w tym pulę buforów, pamięć kompilacji, wszystkie pamięci podręczne, przydzielanie pamięci qe, pamięć menedżera blokady i pamięć CLR (w zasadzie dowolny „urzędnik” znaleziony w dm_os_memory_clerks). Pamięć na stosy wątków, sterty pamięci, dostawców połączonych serwerów innych niż SQL Server lub dowolna pamięć przydzielona przez bibliotekę DLL „inną niż SQL Server” nie jest kontrolowana przez maksymalną pamięć serwera.

Przydzielona pamięć dla stosu wątków, DLL innej firmy, Połączony dostawca serwerów, który jest inny niż Microsoft (jak MySQL.PostgreSQL itp.) Lub dowolna biblioteka DLL załadowana w przestrzeni adresowej SQL Server, która nie jest SQL Server, jest przydzielana poza maksymalną pamięcią serwera. Operacja tworzenia kopii zapasowych IIRC w programie SQL Server 2012 nadal ma przydzieloną pamięć poza pulą buforów.

Czy używasz połączonego serwera do wysyłania zapytań do innych RDBMS? Wszelkie inne oprogramowanie zainstalowane na tym samym komputerze z systemem Windows. Czy możesz opublikować w jakiejś wspólnej lokalizacji wynik następujących zapytań

select type,
sum(pages_kb)/1024 as [Memory utilized in MB],
sum(awe_allocated_kb)/1024 as [Memory allocated though Windows API]
 from sys.dm_os_memory_clerks
 group by type
 order by [Memory utilized in MB] desc
 Go
-------

 select (virtual_address_space_committed_kb/1024) as virtual_address_space_committed_MB,
 (locked_page_allocations_kb/1024) locked_page_allocations_MB,
 (pages_kb/1024) [memory allocated MB]
  from sys.dm_os_memory_nodes
  Go
-------
SELECT SUM (pages_in_bytes)/1024 as 'KB Used', type 
FROM sys.dm_os_memory_objects
GROUP BY type 
ORDER BY 'KB Used' DESC;
GO
--------
select name,
type,
sum(pages_kb)/1024 as [Mem MB],
sum(entries_count) as [Total Entry count] from sys.dm_os_memory_cache_counters
group by
type, name
order by [Mem MB] desc
Go
-----
select * from sys.dm_os_loaded_modules where company <> 'Microsoft Corporation'
go

Czy możesz również przesłać pełne DBCC MMEMORYSTATUSdane wyjściowe do udostępnionej lokalizacji i opublikować link tutaj. Pomogłoby to zrozumieć, który składnik zajmuje pamięć

Edycja: Zgodnie z danymi wyjściowymi memorystatus dbcc widzę 2 węzły NUMA, a pamięć wykorzystywana przez każdy węzeł wynosi około

Node 1 : VM Committed 33554380

Node 2: VM Committed  33554420

Total is approx 64 G. 

Znowu, jeśli widzisz Memory Manager w memorystatus wyprowadza go

Memory Manager                           KB
---------------------------------------- -----------
VM Reserved                              260726964
VM Committed                             **67108820**

Zatwierdzona maszyna wirtualna jest faktycznie pamięcią wirtualną zatwierdzoną przez program SQL Server, a ponieważ pamięć ta została zatwierdzona, tak się stało physical memory backing it. To znowu sprawia, że ​​myślę, że SQL Server używa 65 G ustawionego w maksymalnej pamięci serwera

To jest maksymalna pamięć serwera. Więc pamięć jest dobrze rozdzielona między oba węzły. Możesz również dodać dane wyjściowe poniżej zapytania jutro, aby sprawdzić. Dodaj zrzut ekranu

SELECT (physical_memory_in_use_kb/1024)/1024 AS [PhysicalMemInUseGB]
FROM sys.dm_os_process_memory;
GO