53 lines
2.2 KiB
Transact-SQL
53 lines
2.2 KiB
Transact-SQL
/*
|
|
derived from http://sqlmax.chuvash.eu/
|
|
|
|
SQL Max Memory = TotalPhyMem - (NumOfSQLThreads * ThreadStackSize) - (1GB * CEILING(NumOfCores/4)) - OS Reserved
|
|
NumOfSQLThreads = 256 + (NumOfProcessors*- 4) * 8 (* If NumOfProcessors > 4, else 0)
|
|
ThreadStackSize = 2MB on x64 or 4 MB on 64-bit (IA64)
|
|
OS Reserved = 20% of total ram for under if system has 15GB. 12.5% for over 20GB
|
|
*/
|
|
DECLARE @totSysMemKb INT;
|
|
DECLARE @NumOfCores INT;
|
|
DECLARE @NumOfProcessors INT;
|
|
DECLARE @NumOfSQLThreads INT;
|
|
DECLARE @isX64 BIT = 1;
|
|
DECLARE @ThreadStackSize INT;
|
|
DECLARE @osReserved INT;
|
|
DECLARE @newMaxMem INT;
|
|
DECLARE @appReserved INT = 4*1024*1024; --memory reserved for other apps on the server in Kb
|
|
|
|
SELECT @totSysMemKb = [total_physical_memory_kb]
|
|
FROM [sys].[dm_os_sys_memory];
|
|
|
|
SELECT @NumOfCores = [cpu_count], @NumOfProcessors = [cpu_count] / [hyperthread_ratio]
|
|
FROM [sys].[dm_os_sys_info];
|
|
|
|
SELECT @isX64 = CHARINDEX('64-bit',CAST(SERVERPROPERTY('Edition') AS VARCHAR(MAX)));
|
|
|
|
SELECT @ThreadStackSize = CASE WHEN @isX64=1 THEN 4096 ELSE 2048 END;
|
|
|
|
--force 16 Go, 2 vcpu, 2 cores
|
|
SELECT @totSysMemKb = 16 * 1024 * 1024, @NumOfProcessors = 2, @NumOfCores = 2
|
|
|
|
SELECT @NumOfSQLThreads = 256 + (@NumOfProcessors - 4) * 8 * (CASE WHEN @NumOfProcessors >4 THEN @NumOfProcessors ELSE 0 END);
|
|
|
|
|
|
SELECT @osReserved = CASE WHEN @totSysMemKb < (20*1024*1024) THEN @totSysMemKb * 0.2 ELSE @totSysMemKb * 0.125 END;
|
|
|
|
PRINT'
|
|
@totSysMemKb: '+COALESCE(CAST(@totSysMemKb AS VARCHAR(MAX)),'null')+'
|
|
@NumOfCores: '+COALESCE(CAST(@NumOfCores AS VARCHAR(MAX)),'null')+'
|
|
@NumOfProcessors: '+COALESCE(CAST(@NumOfProcessors AS VARCHAR(MAX)),'null')+'
|
|
@NumOfSQLThreads: '+COALESCE(CAST(@NumOfSQLThreads AS VARCHAR(MAX)),'null')+'
|
|
@isX64: '+COALESCE(CAST(@isX64 AS VARCHAR(MAX)),'null')+'
|
|
@ThreadStackSize: '+COALESCE(CAST(@ThreadStackSize AS VARCHAR(MAX)),'null')+'
|
|
@osReserved: '+COALESCE(CAST(@osReserved AS VARCHAR(MAX)),'null')+'
|
|
@appReserved: '+COALESCE(CAST(@appReserved AS VARCHAR(MAX)),'null')+'
|
|
';
|
|
SELECT @newMaxMem = @totSysMemKb - (@NumOfSQLThreads * @ThreadStackSize) - ((1* 1024 * 1024) * CEILING(@NumOfCores/4)) - @osReserved - @appReserved;
|
|
|
|
SELECT
|
|
@newMaxMem AS maxMemKb
|
|
, @newMaxMem / 1024 AS maxMemMb
|
|
, @newMaxMem / 1024 /1024 AS maxMemGb
|
|
; |