Zwiększona pamięć RAM, gorsza wydajność

Ustawiać:

Windows Server 2008 R2
SQL Server 2008 R2 z dodatkiem SP1
240 GB pamięci RAM
TempDB to pliki danych 8 x 16 GB bez automatycznego wzrostu (łącznie 128 GB)
Serwer fizyczny / autonomiczny

Ten serwer służy do przetwarzania ETL. Właśnie zainstalowaliśmy więcej pamięci RAM na tym serwerze, co daje w sumie 240 GB pamięci RAM. Usługi SQL Server to jedyne działające rzeczy.

Pamięć pokazuje się dobrze w BIOS, OpenManage i Windows.

Jeśli skonfiguruję program SQL Server do korzystania z pamięci min./maks. 70/100 GB, nie będziemy mieć problemów. Jednak gdy zwiększę to do 120/150 GB, po uruchomieniu jednego z naszych procesów ETL pojawia się następujący błąd:

Nie można przydzielić miejsca dla obiektu „<tymczasowy obiekt systemowy: 422234507706368>” w bazie danych „tempdb”, ponieważ grupa plików „PODSTAWOWA” jest pełna. Utwórz miejsce na dysku, usuwając niepotrzebne pliki, upuszczając obiekty w grupie plików, dodając dodatkowe pliki do grupy plików lub ustawiając autogrowth dla istniejących plików w grupie plików. (Msg 1105, stan 2, procedura nieznana, wiersz 1)

Nigdy nie napotkaliśmy tego problemu przed zmianą konfiguracji pamięci. Po ponownej konfiguracji z powrotem do pierwotnej pojemności 70/100 GB nie pojawia się ten błąd.

Rzeczy, których próbowałem:

Ustaw pliki danych TempDB na automatyczne powiększanie. Powoduje to po prostu automatyczne powiększanie plików do momentu osiągnięcia pojemności dyskowej, a następnie awarii.
Dodaj więcej plików danych TempDB. Taki sam błąd, jak pokazano.
Zwiększ rozmiar TempDB do 8 x 32 GB (łącznie 256 GB)

Nie wiem, co może być przyczyną tego problemu.

sql-server sql-server-2008-r2 memory

— Derek Kromm
źródło

Czy twoja pamięć jest zrównoważona między węzłami NUMA? A co z twoimi procesorami? Czy dziennik SQL Server pokazuje, ile procesorów jest używanych podczas uruchamiania?

— Aaron Bertrand

Czego używasz do procesów ETL? SSIS czy podobne narzędzie? Jeśli jest to narzędzie poza SQL Server, czy uruchamiasz je na tym samym serwerze, co Twoja instancja SQL Server?

— Mike Fal

To dobry punkt @ Mike, jeśli proces ETL nie jest w stanie pobrać wystarczającej ilości pamięci, aby wykonać swoje zadanie, ponieważ SQL Server zużywa zbyt dużo, może być konieczne przesunięcie pracy do tempdb.

— Aaron Bertrand

Oto dobry start do monitorowania użycia tempdb: msdn.microsoft.com/en-us/library/ms176029(v=SQL.105).aspx . To powinno dać ci wyobrażenie o tym, co się dzieje.

— Thomas Stringer

Czy zrobiłeś jakąkolwiek analizę tego, co faktycznie działa, gdy TempDB się rozwija? Prosty sp_who2 / sp_whoisactive? Wydaje mi się, że masz jakieś długoterminowe transakcje, którymi można lepiej zarządzać, ale trudno powiedzieć. Osobiście nie przywiązałbym się do zmiany pamięci, ale najpierw spójrz na kod i sprawdź, czy działa poprawnie.

— Mike Fal

Dziękujemy wszystkim za pomoc.

Po przejrzeniu niektórych planów wykonania okazuje się, że istnieje JOIN, który jest przetwarzany inaczej w zależności od ilości dostępnej pamięci RAM. Przy mniejszej ilości pamięci RAM ocenia to za pomocą skrótu; z większą ilością pamięci RAM wykorzystuje serię połączeń scalających.

Zasadniczo sprowadza się to do źle napisanego T-SQL, który obecnie refaktoryzuję.

— Derek Kromm
źródło

Jest to całkowicie sprzeczne z intuicją, ponieważ sprzężenie mieszające wymaga przyznania pamięci, podczas gdy scalanie nie. Czy istnieje dodatkowa operacja sortowania, która obsługuje łączenie scalające?

— Martin Smith,

To nie jest odpowiedź na pytanie, tylko kod, którego nie chciałem publikować w komentarzu. Aby zobaczyć saldo harmonogramów i pamięci między węzłami NUMA (a także sprawdzić, czy jakieś węzły nie są widoczne online):

SELECT 
  parent_node_id, 
  [status],
  AVG(current_tasks_count) AS avg_tasks_count, 
  AVG(load_factor) AS avg_load_factor,
  scheduler_count = COUNT(*)
FROM sys.dm_os_schedulers
GROUP BY parent_node_id, [status];

SELECT 
  memory_node_id, 
  name, 
  SUM(single_pages_kb + multi_pages_kb) AS memory_kb
FROM sys.dm_os_memory_clerks
GROUP BY memory_node_id, name;

(W SQL Server 2012 ostatnia SUMpowinna być, SUM(pages_kb)ponieważ nie ma już oddzielnych alokatorów jedno- i wielostronicowych).

— Aaron Bertrand
źródło