Memory Usage 

Use

In the SAP/SQL Server Database Monitor, the Memory Usage section is divided into the following areas:

Current memory

The current size of the virtual memory used by SQL Server is shown here in KB. This memory includes areas for the data cache, the procedure cache, open objects, locks, and connections. If the parameter Set working set size is set to 1, the value displayed is the physical memory used by SQL Server.

Maximum memory

The maximum amount of virtual memory used for the SQL Server since its start.

Memory setting

Memory usage is determined by the SQL Server parameters min server memory and max server memory.

This area shows the memory allocation strategy that SQL Server uses. There are three possible values

FIXED:

The memory setting is FIXED if min server memory (MB) is equal to max server memory (MB). SQL Server will use a constant amount of memory, specified by these two parameters.

This is the recommended setting for R/3. If FIXED is not set, and an R/3 instance is running on the same server, R/3 and SQL Server will compete for memory.

AUTO:

SQL Server will dynamically allocate memory it requires between 4 MB and 2 PB. min server memory (MB) = 0 and max server memory (MB) = 2147483647.

AUTO is the recommended setting for a standalone database server, that is, a server without an R/3 instance.

RANGE:

SQL Server can dynamically allocate memory between min server memory (MB) and max server memory (MB).

SQL Server will use memory in a range between these 2 parameter settings.

We recommend that you set the SQL Server parameter set working set size to 1, so that the allocated memory will always reside in the physical memory.

Total SQL connections

All the connections on the SQL Server database. This includes the R/3 connections, the SQL Agent, and potential Query Analyzer connections.

R/3 connections

The number of connections from the SAP work processes to the SQL Server database.

Each work process has multiple connections to the SQL Server database.

Free buffers

The number of free data buffers available. These buffers can be used either by the data cache or by the procedure cache.

Procedure cache

The size in KB of the procedure cache, which contains stored procedures, execution plans, etc. In SQL Server 70, this value is adjusted automatically.

Data cache size

The size in KB of the data cache memory. Data and index pages are stored in the data cache so that they do not need to be stored on disk.

Data cache hit ratio

The data hit ratio is the main performance indicator for the data cache.

The hit ratio is the percentage of pages found in the cache without having to read from the disk. The ratio shows the average percentage of data pages found in the cache since SQL Server was started.

The hit ratio is a mean value that gives a snapshot of a short period of time before the analysis. This value should always be above 95, even during periods of heavy workload. If the hit ratio is significantly below 95, the data cache could be too small.

Always check the history of these values. Choose Detail analysis menu ® Performance database. A snapshot is collected every 2 hours. Check the column Data cache hit %.