Excessive SQL Server memory utilization

Products

Information Centric Analytics IT Analytics

Issue/Introduction

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.

Environment

Release : 6.5.4

Component : Microsoft SQL Server

Resolution

Enable the maximum server memory setting for the SQL Server by following this procedure:

Using SQL Server Management Studio (SSMS), connect to the database engine hosting the RiskFabric database
In Object Explorer, right-click the name of the SQL Server host and select Properties
In the Server Properties window, select the Memory page
On the server memory page under Server memory options, change the Maximum server memory (in MB) setting to a value appropriate for your server*
Click the OK button to close the Server Properties window

* Broadcom cannot prescribe this value. Please refer to Microsoft's documentation for guidance in selecting the value for this setting in your environment.

Additional Information

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)';