I am Berke

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.

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

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.