Microsoft SQL Server is consuming excessive memory, causing instability in the host operating system and potentially leading to failures during Information Centric Analytics' (ICA) RiskFabric Processing and RiskFabric Intraday Processing jobs, or during processing jobs for IT Analytics' (ITA) cubes.
Release : 6.5.4
Component : Microsoft SQL Server
Enable the maximum server memory setting for the SQL Server by following this procedure:
* Broadcom cannot prescribe this value. Please refer to Microsoft's documentation for guidance in selecting the value for this setting in your environment.
By default, SQL Server does not have a limit on the maximum memory it is allowed to use. This is controlled through the setting Maximum server memory, and its default value is 2,147,483,647MBs (2 petabytes).
Per Microsoft's SQL Memory Management Architecture Guide:
SQL Server dynamically acquires and frees memory as required. Typically, an administrator does not have to specify how much memory should be allocated to SQL Server, although the option still exists and is required in some environments.
...
The default memory management behavior of the SQL Server Database Engine is to acquire as much memory as it needs without creating a memory shortage on the system. The SQL Server Database Engine does this by using the Memory Notification APIs in Microsoft Windows.
When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free memory. Maintaining this free memory prevents the operating system (OS) from paging. If less memory is free, SQL Server releases memory to the OS. If more memory is free, SQL Server may allocate more memory. SQL Server adds memory only when its workload requires more memory; a server at rest does not increase the size of its virtual address space.
The following query will return the current Maximum Server Memory value and the current amount of memory in use:
SELECT c.[value] AS 'Maximum Server Memory',
c.value_in_use AS 'Memory In Use'
FROM sys.configurations AS c
WHERE c.[name] = 'max server memory (MB)';
See also:
Microsoft SQL docs online: Server memory configuration options