Dlaczego wymagane są okresowe restarty, aby moja instancja działała dobrze?

Mamy produkcyjny serwer DB na SQL 2005. Wszystko działa normalnie przez chwilę, ale po kilku tygodniach zauważamy znaczny spadek wydajności. Tylko ponowne uruchomienie programu SQL Server przywraca normalną wydajność.

Trochę tła:

• Uruchamianie ponad 1200 baz danych (głównie jeden dzierżawca, część wielu dzierżawców). Zanim ktokolwiek wygłosi wykład na temat przejścia tylko do wielu dzierżawców, istnieją ważne powody, aby utrzymać tę strukturę ......
• Pamięć RAM wynosi 16 GB. Po ponownym uruchomieniu SQL Server nie wraca do użycia 15 GB.
• Aktywne połączenia DB to około 80 połączeń - co naszym zdaniem jest dość zdrowe, biorąc pod uwagę, że istnieje jedna pula połączeń na serwer WWW na proces - więc nie mamy problemu z wyciekiem połączenia.

Próbowaliśmy kilku rzeczy w godzinach poza szczytem: - Uruchom DBCC DROPCLEANBUFFERS (z PUNKTEM KONTROLNYM), aby wyczyścić pamięć podręczną danych. Nie ma to żadnego wpływu, ani nie usuwa zużycia pamięci RAM). - Uruchom FREEPROCCACHE i FREESYSTEMCACHE, aby wyczyścić plany zapytań i przechowywaną pamięć podręczną proc. Bez efektu.

Oczywiście ponowne uruchomienie programu SQL Server nie jest idealne w aktywnym środowisku produkcyjnym. Coś nam brakuje. Ktoś jeszcze przez to przechodzi?

AKTUALIZACJA: 28 kwietnia 2012 r. Nadal walczę z tym problemem. Obniżyłem pamięć SQL Server do 10 GB, aby wykluczyć wszelkie spory z systemem operacyjnym. Zbliżam się do zawężenia go, ale potrzebuję pomocy od następnego kroku.

Oto, co znalazłem, po ponownym uruchomieniu programu SQL Server, plik strony waha się między 12,3 GB a 12,5 GB. Tak pozostanie na wiele dni. Łączna liczba wątków serwera zawiesi się między 850 a 930 - również stabilna i spójna przez wiele dni (serwer sqlser stale ma od 55 do 85 z nich w zależności od ruchu).

Potem jest „wydarzenie”. Nie mam pojęcia, co to za wydarzenie, nie widzę tego w dziennikach i nie widzę niczego spójnego w dniu tygodnia lub o godzinie, w której się ono zdarza, ale cały przypadkowy plik strony przeskakuje do 14.1 lub 14.2 GB, a wątki skaczą między 1750 a 1785.

Sprawdzając perfom, kiedy to się dzieje, ponad 900 tych wątków to serwer sqlserver. Więc idę do sp_who2, aby zobaczyć, skąd pochodzą te wątki ... a tam jest tylko używane około 80 połączeń db.

Więc ... czy ktoś ma jakieś pomysły, jak zlokalizować resztę tych 900 wątków na serwerze SQL i co robią?

AKTUALIZACJA: 01 czerwca 2012 Nadal walczę z tym problemem. Dla każdego, kto to czyta, problem z przeskakiwaniem wątków został rozwiązany. Było to spowodowane automatycznym oprogramowaniem do tworzenia kopii zapasowych ComVault. Tworzył wątek próbujący wykonać kopię zapasową baz danych, których już tam nie było (utrzymywał listę wcześniejszych baz danych), a nie tylko tworzyć kopie zapasowe bieżących baz danych.

Ale - problem nadal występuje i musimy restartować co tydzień, dać lub zająć kilka dni. Praca z zespołem Rackspace, aby sprawdzić, czy mogą rzucić jakieś światło.

Mówisz, że wszystko jest w porządku, a po kilku tygodniach wydajność spada. (Zwykle ludzie twierdzą, że wydajność spada szybko, w określonych momentach lub w pozornie przypadkowych odstępach czasu. Może to oznaczać złą wydajność wejścia / wyjścia lub zablokować burze lub zapytania wymagające dużej mocy obliczeniowej działające w dziwnych czasach, lub ciężką zaplanowaną pracę lub brak indeksowanie lub złe statystyki powodujące zapytania procesora lub odczyty dysku itp.). Tygodnie są niezwykłe.

Moja hipoteza jest taka, że ​​inna aplikacja na twoim serwerze przecieka pamięć. Widziałem to z oprogramowaniem antywirusowym (ulubionym złym oprogramowaniem serwera każdego DBA) i oprogramowaniem do monitorowania innych firm. Z biegiem czasu sprawdzałbym dwukrotnie użycie pamięci SQL Server i pobierałbym całe użycie pamięci przez wszystkie inne aplikacje na pudełku. Jeśli masz ustawione twarde limity wykorzystania pamięci przez SQL Server i masz ustawione, aby nie zezwalać na stronicowanie, mogą to być inne aplikacje, które są stronicowane i pochłaniają pojemność I / O.

Nie jest trudno szukać. Jeśli nie przechowujesz jeszcze danych na serwerze, uruchomiłbym Perfmon i pobierał próbki co 30 lub 60 minut. Po kilku dniach może pojawić się zwiększenie wykorzystania pamięci w innych aplikacjach.

Czy w dzienniku SQL Server występują komunikaty o błędach informujące, że „znaczące części serwera SQL zostały stronicowane”? To też byłaby duża wskazówka.

Chciałbym pogratulować, że można uruchomić 1200 baz danych na jednym wystąpieniu serwera SQL z zaledwie 16 GB pamięci RAM i mieć problemy tego rodzaju po kilku tygodniach bezproblemowego działania. Fajna historia do opowiedzenia w lokalnym rozdziale PASS.

Teraz do rozwiązania problemu: Twoja pamięć RAM ma 16 GB zarówno dla SQL, jak i OS. Zakładam, że twoje maksymalne ustawienie pamięci wynosi 15 GB lub maks. Może to powodować, że pula buforów zużywa całą pamięć i dusi system operacyjny. Mówisz, że czyszczenie puli buforów i pamięci podręcznych nie wykazuje żadnych różnic, a ponadto twoje PLE przekracza 300. Świadczy to o szyjkach butelek pamięci. Jak wygląda procesor i operacje wejścia / wyjścia na serwerze (specyfikacje / statystyki)?

Uruchom select * from sys.dm_exec_request where session_id>50 and session_id<>@@spidi jakie są wyświetlane zawartości zasobów (wait_type, wait_time, last_wait_type, wait_resource).

1200 baz danych, system operacyjny i ewentualnie inne rzeczy? Tak, myślę, że sam serwer będzie potrzebował więcej niż 1 GB pamięci RAM do działania, zwłaszcza biorąc pod uwagę, że jeśli ustawisz 15 GB jako ustawienie maksymalnej pamięci SQL Server, nadal potrzebuje dodatkowej pamięci poza tym 15 GB na wątki.

Zwiększyłem SQL Servera do 14 GB, aby dać serwerowi trochę więcej oddechu.

Również przykład podany w „Professional SQL Server 2008 Internals and Rozwiązywanie problemów” dla ilości pamięci w systemie SQL Server 2008 x64 z narzędziem do tworzenia kopii zapasowych trzeciej części z 16 GB pamięci RAM:

• 2 GB dla Windows
• 1 GB na wątki robocze
• 1 GB na MPA itp.
• 1 GB na program do tworzenia kopii zapasowych
• 11 GB dla SQL Server

W książce pokazuje, jak określić maksymalną liczbę wątków, jaką możesz mieć, i jak obliczyć, ile pamięci zajmą. Uruchom to (zmień typ serwera, aby pasował do serwera), aby dowiedzieć się, ile pamięci będą potrzebne twoje wątki.

declare @servertype int

set @servertype=1
/*
1: x86 (32-bit)
2: x64 (64-bit)
3: IA64

*/

select max_workers_count *
    (
        case @servertype when 1 then .5
            when 2 then 2
            when 3 then 4
            else .5
        end
    )
from sys.dm_os_sys_info

Jeśli pamięć bazy danych jest równomiernie rozłożona na wszystkie bazy danych, masz tylko 12,8 MB dla każdej bazy danych (15 * 1024) /1200=12,8. Potrzebujesz więcej pamięci.

Musisz sprawdzić, dlaczego wydajność spada. Czy widzisz blokowanie, blokowanie itp.? Jak wyglądają statystyki oczekiwania?

Komendy DBCC usuwają tylko bufory pamięci, nie zwalniają pamięci z powrotem do systemu operacyjnego.

Czy wiesz, że SQL Server faktycznie zajmuje pamięć? Sugerowałbym, aby rozważyć skonfigurowanie sesji Perfmon lub rozpocząć zbieranie informacji DMV po ponownym uruchomieniu, aby dowiedzieć się, co robi SQL Server i nad czym pracuje. Weź również pod uwagę, jeśli użytkownicy wykonują więcej pracy niż zwykle w czasie zbierania (np. Przetwarzanie na koniec miesiąca itp.). Czy używasz SSRS, SSIS lub SSAS na tym samym serwerze?

W systemie jest 1200 baz danych, jaki jest największy rozmiar bazy danych, którą masz?