Jak kontrolować nadmierne użycie pamięci RAM przez SQL Server?

Serwer bazy danych, którego używam, działa w 6 różnych instancjach SQL Server. Ma 48 GB pamięci RAM. Jeden z nich zużywa ponad 10 GB pamięci RAM, całkowite zużycie wynosi na razie 20 GB. Zużycie pamięci RAM stale rośnie. Kilka dni wcześniej zużywało ponad 40 GB pamięci RAM, a serwer reagował bardzo wolno. Aplikacja pokazuje awarie podczas zapisywania danych.

Ponownie uruchomiłem usługi SQL Server.

Po ponownym uruchomieniu usług zużycie spadło do 4 GB, ale teraz rośnie. Obawiam się, że w ciągu 4–5 dni powiększy się do 40 GB i spowolni serwer.

Ponowne uruchomienie usługi nie jest dobrą opcją.

Odkryłem również z różnych źródeł, że możemy ustawić maksymalny rozmiar pamięci dla SQL Server. I nie jestem pewien, czy to pomoże, czy nie. Nie mogę tego przetestować, ponieważ serwer korzysta z produkcyjnej bazy danych i byłoby to ryzykowne, gdyby usługa zatrzymała się podczas modyfikowania ustawienia w SQL Server.

Czy ktoś może pomóc w rozwiązaniu tego problemu?

sql-server

Domyślnie SQL zużywa jak najwięcej pamięci RAM, aby zmaksymalizować wydajność, jest to dobre - oznacza to, że buforuje typowe zapytania i wyniki, aby przyspieszyć działanie aplikacji. Użyje pamięci RAM tylko do limitu, przy którym może nastąpić stronicowanie raportów systemu operacyjnego. Możesz zastąpić to zachowanie. Zobacz dokumentację MS: Jak dostosować użycie pamięci za pomocą opcji konfiguracji w SQL Server Może to być również przydatne: Jak określić właściwe ustawienia konfiguracji SQL Server

To NIE jest problem - to zaprojektowane i domyślne zachowanie SQL Server !! I tak - ponowne uruchomienie procesu SQL Server to okropny pomysł ... SQL Server buforuje w pamięci tak dużo danych i stron indeksowych, jak to możliwe - optymalizuje w ten sposób ogólną wydajność systemu. To jest powód, dla którego SQL Server zawsze powinien być serwerem dedykowanym - nie robiąc nic poza SQL Server.

— marc_s

Tak, mam to. Jest tak zaprojektowany. Ale gdy serwer sql zużywa dużo pamięci, spowalnia serwer i powoduje przekroczenie limitu czasu raportów. Chcę więc zapobiec używaniu ograniczonej pamięci przez serwer SQL.

Właściwie, jeśli SQL Server zużywa tyle pamięci, ile może, powiedziałbym, że zmniejszyłoby to prawdopodobieństwo problemów z dostarczaniem raportów. To wtedy, gdy sztucznie ograniczasz SQL Server (lub umieszczasz zbyt wiele instancji w jednym polu, które konkurują o zasoby), zobaczysz problemy związane z przyznaniem pamięci i innymi problemami.

— Aaron Bertrand

Ale zastanawiam się, dlaczego nagle zaczął dobrze wyświetlać raporty po ponownym uruchomieniu usługi serwera SQL. Czy może być tak, że zbyt wielu klientów korzystało z aplikacji, przez co raporty nie były wyświetlane?

To jest z założenia. SQL Server powinien wykorzystywać całą dostępną pamięć, ponieważ przechowuje coraz więcej danych w pamięci, dzięki czemu nie musi wracać na dysk, aby uzyskać tę samą pamięć w kółko.

Jeśli chcesz ograniczyć ilość pamięci używanej przez pojedyncze wystąpienie programu SQL Server, możesz to zrobić w SQL Server Management Studio, klikając prawym przyciskiem myszy nazwę wystąpienia w Eksploratorze obiektów i wybierając właściwości. Następnie wybierz kartę pamięci i ustaw maksymalną ilość pamięci, z której SQL Server będzie mógł korzystać. Teraz nie ograniczy to wszystkich aspektów SQL Server do takiej ilości pamięci. Kontroluje to tylko pulę buforów i pamięć podręczną planu wykonania. Rzeczy takie jak CLR, pełny tekst, rzeczywista pamięć używana przez pliki exe programu SQL Server, SQL Agent, rozszerzone procedury składowane itp. Nie są kontrolowane przez to ustawienie. Jednak te inne rzeczy zwykle nie potrzebują tyle pamięci, to pula buforów i pamięć podręczna planu wykonania, które wymagają dużej ilości pamięci.

Jeśli ustawisz to ustawienie na jedną instancję, będziesz chciał ustawić ją na wszystkich instancjach, aby się nie nadepnęły.

— mrdenny
źródło