No i przyszło mi się zderzyć z CLR na SQL. Jeśli chodzi o tworzenie bibliotek – no problem. Kompilujesz ładujesz, działa. Sęk w tym, że na jednym serwerze ciągle pojawiał się błąd z załadowaniem bibliotek CLR. Bład wskazywał na brak pamięci. Tymczasem na systemie z 16 GB RAM miałem 7GB RAM wolnego. Jak diagnozować ten przypadek?
CLR został udostępniony w SQL w wersji 2005. W wersji 2012 znacznie przebudowany został Memory Manager i w zawiązku z tym również zarządzanie pamięcią dostępną dla CLR.
Bardzo ogólnie do wersji 2008R2:
- otrzymaną pamięć SQL dzielił sobie na 2 obszary. Najpierw rezerwował „zapas”, tzw. Memory To Leave lub Memory To Reserve lub MTL, a następnie resztę pamięci przeznaczał na buffer pool.
- tzw. single page allocator przyznawał pamięć z zakresu buffer pool
- w obszarze MTL przechowywany był CLR, zewnętrzne biblioteki dll, database mail, obiekty COM oraz pamięć o którą prosił CLR podczas tworzenia obiektów większych niż jedna strona. Pamięć przyznawał tzw. multipage allocator
- MaxServerMemory definiował tylko buffer pool, więc normalne było, że SQL zajmował więcej pamięci niż MaxServerMemory.
- Jeśli chciało się mieć więcej pamięci poza MaxMemory należało uruchamiać SQL z przełącznikiem /g
Miało to sens. W buforze obsługiwane były żądania na pojedyncze strony, bo taka jest specyfika pracy bazy danych. Dodatkowo jeśli CLR wymagał utworzenia małego obiektu <8KB, to te obiekty również były tworzone w Bufer pool (tzw. stolen pages)
I znowu ogólne od wersji 2012:
- SQL rezygnuje z dwóch allocatorów pamięci. Jest tylko jeden, który obsługuje wszystko
- Jest ograniczony przez MaxServerMemory. Teraz SQL może zająć już na prawdę niewiele więcej pamięci niż określa to MaxMemory
- Żądania o pamięć (czy to pojedyncze strony czy o ciągły zakres kilku stron) są realizowane z pamięci buffer pool.
- Jedyny wyjątek od tej zasady, to jeśli CLR tworzy obiekty bezpośrednio w systemie Windows (żąda pamięci w VAS)
A teraz wrócę do mojego przypadku. SQL 2012. Wszelkie opisywane rozwiązania polegające na cudownym działaniu opcji /g nie działają. SQL ma MaxMemory ustawione na 12 GB, ale nie skorzystał z całej pamięci bo nadal 7 GB RAM jest wolne na serwerze. Dlaczego SQL preferuje zbyć żądania CLR komunikatem „out of memory” zamiast pobrać nową pamięć z OS?
Przyczyna była prosta. Konto serwisowe SQL nie miało przyznanego prawa „Lock pages in memory”. Połowa buffer poola była już na dysku. Kiedy CLR chciał tworzyć nowe obiekty SQL przyznawał mu strony zeswapowane, co kończyło się błędem „out of memory”. Dodanie tego prawa, stuningowanie MaxMemory i … niestety restart SQL rozwiązało problem.
Polecam lekturę:
- Jak działa przyznawanie pamięci w SQL 200R2 i wcześniejszych: http://mssqlwiki.com/sqlwiki/sql-performance/basics-of-sql-server-memory-architecture/
- Jak działa przyznawanie pamięci w SQL 2012 i nowszych: http://mssqlwiki.com/tag/sql-server-2012-memory-architecture/
- Na czym polegały główne zmiany w SQL 2012 (stąd pochodzi ilustracja powyżej): http://blogs.msdn.com/b/sqlosteam/archive/2012/07/11/memory-manager-surface-area-changes-in-sql-server-2012.aspx
- Opis widoku do diagnostyki problemów z RAM – sys.dm_os_memory_clerks : https://msdn.microsoft.com/en-us/library/ms175019(v=sql.120).aspx
- Parę słów o troubleshootingu: http://blogs.msdn.com/b/karthick_pk/archive/2012/06/15/troubleshooting-sql-server-memory.aspx
