max server memory beállítás

Az elmúlt időszakban több SQL Server beállítását is meg kellett vizsgálnom. Általban a max server memory beállítás mindenhol az alapértelmezett, dinamikus értéken volt: akár a szerver összes memóriáját is elviheti az SQL Server, ami igen komoly "lassuláshoz" vezethet.

Alapvetően a max servever memory beálításnál az alábbi paramétereket kell megvizsgálni:

• mennyi memória van a szerverben,
• milyen platformon dolgozunk (64 bit, 32 bit),
• mennyi processzor van a szerverben,
• mennyi memóriát igényelnek egyéb alkalmazások, szolgáltatások.

Itt két - talán meglepő - elemre szeretném felhívni a figyelmet: a processzorok száma a worker thread-ek száma miatt érdekes, míg az egyéb alkalmazások alatt pl. a backup szoftvert értem. Az utóbbival kapcsolatban az az általános véleményem, hogy az SQL Server-en ne legyenek "egyéb" szoftverek, csak az SQL Server és maximum egy antivirus program (ez is egy érdekes kérdés, hogy kell e egyáltalán egy adatbázis szerverre). A worker thread-ek esetében figyelembe kell venni a processzorok számát és a platformot amin megyünk. Ettől függően ezek is memória felhasználók az alábbi táblázat alapján, ahol a processzorok és a platform függvényében a worker thread-ek száma látható:

32 bit esetében 1 worker thread 0,5 MB, míg 64 bit esetében 2 MB memóriát foglal el. Így pl. egy 8 processzoros 64 bites szerver esetén, csak a worker therad-ek ~1GB memóriát foglalnának el.

A fentiek ismeretében már majdnem ki tudjuk számolni, hogy mennyi is legyen/lehet a maximum memória, amit az SQL Server-nek adhatunk, de ne feledkezzünk meg arról, hogy ez csak a Buffer Pool memória foglalására vonatkozik, az ezen kivűl eső szolgáltatásokra nem. Ilyenek pl.: a multi-page allocation, sql xml vagy akár az sql clr által betöltött dll-ek. Ezeknek általában 1 GB-tól indulva kell memóriát hagyni, idővel, mérések alapján ezt lehet növelni vagy csökkeneteni.

Na ezek után már könnyebb kiszámolni, hogy mennyi is legyen ennek a beállításnak az értéke. Az alábbi script segít ennek a megállapításában (Itanium rendszerek nem támogatottak a script-ben):

 1--Itanium is not supported in this calculation!!!!
 2IF OBJECT_ID('tempdb..#memory') IS NOT NULL
 3    DROP TABLE #memory;
 4GO
 5 
 6CREATE TABLE #memory
 7(
 8    [PhysicalMemory] int,
 9    [RoomForOS] int,
10    [MemToApps] int,
11    [WorkerThreadMemory] int,
12    [CalculatedMaxServerMemoryMB] int,
13    [ConfiguredMaxServerMemoryMB] int,
14    [ActiveMaxServerMemoryMB] int
15);
16GO
17 
18--Memory allocated to other apps than SQL Server. Eg.: antivirus, backups software + 1024 MB for multi-page alocation, sqlxml, etc.
19DECLARE @MemToApps int;
20SET @MemToApps= 2048;
21--Memory allocated to the OS in MB.
22DECLARE @RoomForOS int;
23SET @RoomForOS = 2048;
24--max worker threads
25DECLARE @WT int
26SET @WT = (SELECT [max_workers_count] FROM sys.dm_os_sys_info);
27DECLARE @PhysicalMemory int
28SET @PhysicalMemory = (SELECT [physical_memory_in_bytes] / 1048576 FROM sys.dm_os_sys_info);
29 
30IF EXISTS (SELECT 1 FROM sys.configurations WHERE NAME LIKE '%64%')
31    BEGIN
32        --64 bit platform
33        INSERT INTO #memory
34        SELECT  @PhysicalMemory AS [PhysicalMemory],
35                @RoomForOS AS [RoomForOS],
36                @MemToApps AS [MemToApps],
37                CAST((@WT * 2) AS int) AS [WorkerThreadMemory],
38                CAST((@PhysicalMemory - @RoomForOS - @MemToApps - (@WT * 2)) AS int) AS [CalculatedMaxServerMemoryMB],
39                CAST([value] AS int) AS [ConfiguredMaxServerMemoryMB],
40                CAST([value_in_use] AS int) AS [ActiveMaxServerMemoryMB]
41        FROM
42            sys.configurations WHERE [name] = 'max server memory (MB)';
43             
44    END
45ELSE
46    BEGIN
47        --32 bit platform
48        INSERT INTO #memory
49        SELECT  @PhysicalMemory AS [PhysicalMemory],
50                @RoomForOS AS [RoomForOS],
51                @MemToApps AS [MemToApps],
52                CAST((@WT * 0.5) AS int) AS [WorkerThreadMemory],
53                CAST((@PhysicalMemory - @RoomForOS - @MemToApps - (@WT * 0.5)) AS int) AS [CalculatedMaxServerMemoryMB],
54                CAST([value] AS int) AS [ConfiguredMaxServerMemoryMB],
55                CAST([value_in_use] AS int) AS [ActiveMaxServerMemoryMB]
56        FROM
57            sys.configurations WHERE [name] = 'max server memory (MB)';
58    END
59 
60SELECT
61    *
62FROM
63    #memory

A paraméterek jelentése, értelmezése az alábbi:

• @MemToApps: memória MB-ban, amennyit az egyéb alkalmazásoknak - backup szoftver, antivírus, stb. akarunk adni + 1024 Mb a multi-page - allocation-hoz.
• @RoomForOS: memória MB-ban, amit az operációs rendszernek tartok fent. Általában ez 2 GB, 2048 MB. Szükség esetén növelhető, de tapasztalatom - szerint a min. 2048 MB kell.
• @WT: a maximum worker thread-ek száma, a sys.dm_os_sys_info DMV tartalmazza.
• @PhysicalMemory: a fizikai memória, a sys.dm_os_sys_info DMV tartalmazza.

Próbaként lefuttatam egy teszt szerveren, ahol az alábbi eredményt kaptam:

Az eredméy:

• PhysicalMemory: ennyi memória van a gépben, ez it 128 GB,
• RoomForOS: 2 GB memóriát hagytam az OS-nek,
• MemToApps: 2 GB memóriát hagytam az egyéb alkalmazásoknak, illetve a Buffer Pool-on kívüli memória foglalásoknak,
• WorkerThreadMemory: itt a max worker thread-ek számából kiindulva és a fenti kalkuláció alapján számolt memória mennyiség van,
• CalculatedMaxServerMemory: Ennyi lenne a fentiek alapján a max server memory "kezdeti optimális" értéke. Azért kezdeti optimális, mert ezt a - workload alapján lehet/kell változtatni +/-. jelen esetben ez ~122GB
• ConfiguredMaxServerMemoryMB: ez az érték van beállítva, most itt ~102GB
• ActiveMaxServerMemoryMB: ez az aktuálisan érvényes érték, itt ~102GB

A Configured és az ActiveMaxServerMemoryMB értékei között ha különbség van akkor az azt jelenti, hogy az sp_configure parancs után még nem volt RECONFIGURE, tehát még nem a beállított érték az aktiv.

Van még 3 olyan mutató, amit határozottan ajánlott figyelni az SQL Server esetében és köthető a max server memory beállításhoz is:

• SQL Server:Memory Manager\Total Server Memory (KB): ez a counter megmondja, hogy éppen mekkora a buffer pool mérete.
• SQL Server:Memory Manager\Target Server Memory (KB): ez megmutatja, hogy mekkore kellene legyen a buffer pool ideális esetben.
• SQL Server:Buffer manager\Page life expectancy: megmutatja, hogy egy page átlagosan mennyi ideig van a memóriában, másodpercben megadva.

Az első két - Total Server Memory, Target Server Memory - érékeinek nagyjából egyenlőnek kellene lenniük. Amennyiben a Target Server Memory nagyobb, mint a Total Server Memory, akkor valamilyen memória jellegű probléma állhat fent, például: külső memória "nyomás". Ilyenkor meg kell nézni, hogy mi a fő memória felhasználó, az SQL Server-en kívül. Általában a Target Server Memory legalacsonyabb értékét szoktam beállítani, amikor más alkalmazás is van a szerveren. A page life expectancy esetében 300 másodperc amit ajánlanak, de ez azért az újabb, nagy teljesítményű szervereken nem igazán elérhetetlen :) Ezeket az információkat a Perfmon és az alábbi lekérdezésekkel is megnézhetjük:

1SELECT
2    [counter_name],
3    [cntr_value]
4FROM
5    sys.dm_os_performance_counters
6WHERE
7    [counter_name] IN ('Target Server Memory (KB)','Total Server Memory (KB)');
8     
9SELECT * FROM sys.dm_os_performance_counters WHERE [counter_name] = 'Page life expectancy';

Érdemes a lock pages in memory opciót is megfontolni bizonyos esetekben.