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ó:
 
CPU 32-bit 64-bit
<= 4 256 512
8 288 576
16 352 704
32 480 960
 
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):
 
--Itanium is not supported in this calculation!!!!
IF OBJECT_ID('tempdb..#memory') IS NOT NULL
	DROP TABLE #memory;
GO

CREATE TABLE #memory
(
	[PhysicalMemory] int,
	[RoomForOS] int,
	[MemToApps] int,
	[WorkerThreadMemory] int,
	[CalculatedMaxServerMemoryMB] int,
	[ConfiguredMaxServerMemoryMB] int,
	[ActiveMaxServerMemoryMB] int
);
GO

--Memory allocated to other apps than SQL Server. Eg.: antivirus, backups software + 1024 MB for multi-page alocation, sqlxml, etc.
DECLARE @MemToApps int;
SET @MemToApps= 2048;
--Memory allocated to the OS in MB.
DECLARE @RoomForOS int; 
SET @RoomForOS = 2048;
--max worker threads
DECLARE @WT int 
SET @WT = (SELECT [max_workers_count] FROM sys.dm_os_sys_info);
DECLARE @PhysicalMemory int 
SET @PhysicalMemory = (SELECT [physical_memory_in_bytes] / 1048576 FROM sys.dm_os_sys_info);

IF EXISTS (SELECT 1 FROM sys.configurations WHERE NAME LIKE '%64%')
	BEGIN
		--64 bit platform
		INSERT INTO #memory
		SELECT  @PhysicalMemory AS [PhysicalMemory], 
				@RoomForOS AS [RoomForOS], 
				@MemToApps AS [MemToApps], 
				CAST((@WT * 2) AS int) AS [WorkerThreadMemory],
				CAST((@PhysicalMemory - @RoomForOS - @MemToApps - (@WT * 2)) AS int) AS [CalculatedMaxServerMemoryMB],
				CAST([value] AS int) AS [ConfiguredMaxServerMemoryMB],
				CAST([value_in_use] AS int) AS [ActiveMaxServerMemoryMB]
		FROM
			sys.configurations WHERE [name] = 'max server memory (MB)';
			
	END
ELSE
	BEGIN
		--32 bit platform
		INSERT INTO #memory
		SELECT  @PhysicalMemory AS [PhysicalMemory], 
				@RoomForOS AS [RoomForOS], 
				@MemToApps AS [MemToApps], 
				CAST((@WT * 0.5) AS int) AS [WorkerThreadMemory],
				CAST((@PhysicalMemory - @RoomForOS - @MemToApps - (@WT * 0.5)) AS int) AS [CalculatedMaxServerMemoryMB],
				CAST([value] AS int) AS [ConfiguredMaxServerMemoryMB],
				CAST([value_in_use] AS int) AS [ActiveMaxServerMemoryMB]
		FROM
			sys.configurations WHERE [name] = 'max server memory (MB)';
	END

SELECT 
	* 
FROM 
	#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:
SELECT 
	[counter_name],
	[cntr_value]
FROM 
	sys.dm_os_performance_counters 
WHERE 
	[counter_name] IN ('Target Server Memory (KB)','Total Server Memory (KB)');
	
SELECT * FROM sys.dm_os_performance_counters WHERE [counter_name] = 'Page life expectancy';

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

 

Add comment