Jak rozwiązać typy oczekiwania RESOURCE_SEMAPHORE i RESOURCE_SEMAPHORE_QUERY_COMPILE

Próbujemy znaleźć podstawową przyczynę powolnego uruchamiania zapytań serwera SQL, które uderzają / pobierają dane z jednej z bazy danych o wielkości 300 GB, hostowanej na serwerze o niższej konfiguracji:

Windows Server 2003 R2, SP2, Enterprise Edition, 16 GB RAM, 12 CPU 32-bitowy

SQL Server 2005, SP4, Enterprise Edition, 32-bitowy.

Poinformowaliśmy już biznes o aktualizacji do wersji 64-bitowej, co potrwa ponad miesiąc.

Ale w przypadku bieżącego problemu staramy się zebrać dane, jeśli uda nam się rozwiązać problem presji pamięci lub w końcu dojść do wniosku, aby zwiększyć pamięć RAM.

Działanie zakończone: Ponowne indeksowanie i aktualizacja statystyk są właściwe dla tego DB.

Jak pokazano poniżej, zauważyliśmy typ oczekiwania semafora od 5 ostatnich dni, działający w godzinach ładowania:

Kilka informacji po poniższych zapytaniach: rozmiar bufora = 137272

SELECT SUM(virtual_memory_committed_kb)
FROM sys.dm_os_memory_clerks
WHERE type='MEMORYCLERK_SQLBUFFERPOOL'

i pamięć semaforów = 644024 na poniższe zapytanie

 SELECT SUM(total_memory_kb)
FROM sys.dm_exec_query_resource_semaphores

Poniżej znajduje się więcej informacji zebranych z dm_exec_query_resource_semaphoresi sys.dm_exec_query_memory_grantsdmv

Z powyższych informacji wynika, że ​​problemem jest semafor zasobów SP_Blitz.

Czy pamięć „target_memory_kb” przypisana do identyfikatora semafora zasobu jest zbyt niska w porównaniu do 16 GB dostępnej pamięci RAM.

Uwaga * na analizę w ciągu 8 godzin pracy „target_memory_kb” jest zawsze poniżej 1 GB, w porównaniu do 16 GB dostępnych?

proszę wskazać, jaki może być tutaj problem i jak go rozwiązać

Dzięki

O Boże, mam tu złe wieści.

W 32-bitowym systemie operacyjnym SQL Server używa tylko pierwszych 4 GB pamięci do takich celów, jak obszar roboczy zapytania. (I walczy także z systemem operacyjnym o te 4 GB - wszelkie inne uruchomione aplikacje również będą konkurować o tę pamięć.)

4 GB może wydawać się dużo, ale stosunkowo łatwo jest napisać zapytanie, które wymaga kilku GB pamięci do uruchomienia. Gdy wystarczająca ilość zapytań wymaga wystarczającej ilości pamięci, SQL Server generuje RESOURCE_SEMAPHORE, ponieważ zapytania nie mogą uzyskać wystarczającej ilości pamięci do uruchomienia. RESOURCE_SEMAPHORE_QUERY_COMPILE oznacza, że ​​nie mogą nawet uzyskać wystarczającej ilości pamięci, aby skompilować plan wykonania - i tak, to całkiem źle.

Jak to naprawić?

• Przełącz się na 64-bitowy system operacyjny (system operacyjny, którego używasz, i tak nie jest już obsługiwany)
• Przełącz się na 64-bitową wersję SQL Server
• Zmniejsz wymagania dotyczące pamięci na serwerze (nie uruchamiaj żadnych innych aplikacji na tym urządzeniu, a jest to szczególnie ważne w przypadku urządzeń 32-bitowych, ponieważ mamy do dyspozycji tylko 4 GB pamięci)
• Użyj więcej pamięci z przełącznikami AWE / PAE - z wyjątkiem tego, że nie działa, ponieważ RESOURCE_SEMAPHORE czeka, ponieważ SQL Server może używać tylko tych pierwszych 4 GB dla obszaru roboczego zapytania
• Dostosuj zapytania i indeksy, aby potrzebowały mniej pamięci

Waham się nawet powiedzieć to ostatnie, ponieważ problem 32-bitowy jest tak zły i bardzo trudny w starszych wersjach SQL Server. Jeśli korzystasz z bieżącej, możesz przejrzeć pamięć podręczną planu i sortować zapytania według przyznania pamięci, znaleźć największych odbiorców dotacji i dostroić je. Jednak nie ma takiej opcji w tym starym antyku.