Memory
In the SAP/SQL Server Database Monitor, the Memory section is divided into the following areas:
Physical available memory
Total physical memory that is available on the database server (not necessarily used by SQL Server).
Current memory
The current size of the virtual memory used by SQL Server is shown here in MB. This memory includes areas for the data cache, the procedure cache, open objects, locks, and connections.
SQL 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:
| Value | Meaning |
|---|---|
|
FIXED: |
The memory setting is FIXED if Min server memory (MB) is equal to Max server memory (MB). SQL Server uses a constant amount of memory, specified by these two parameters. This is the recommended setting for a central system. If FIXED is set, and an SAP instance is running on the same server, SAP and SQL Server do not compete for memory. |
|
AUTO: |
SQL Server dynamically allocates memory. It requires between 4 MB and the available memory. 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 SAP instance. |
|
RANGE: |
SQL Server can dynamically allocate memory between Min server memory (MB) and Max server memory (MB). SQL Server uses memory in a range between these two parameter settings. |
We recommend that you set the SQL Server parameter set working set size = 0, in all situations. There has been some anecdotal evidence to raise some stability concerns with it being set = 1, and it is likely to be removed from future releases of SQL Server.
Free pages
The number of free data buffers available in MB. These buffers can be used either by the data cache or by the procedure cache. Since the number of free pages can become very low in a busy system we display this value not as an integer value but as a decimal value with 1 decimal to allow values below 1 MB.
Procedure cache
The size in MB of the procedure cache, which contains stored procedures, execution plans, etc. In SQL Server this size is adjusted automatically. Since the size of the cache can become very low in an idle system we display this value not as an integer value but as a decimal value with 1 decimal to allow values below 1 MB.
Data cache size
The size in MB of the data cache memory. Data and index pages are stored in the data cache so that they do not need to be read from disk.
Lock memory size
The size in MB of the memory used for lock management.
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 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.
Procedure cache hit ratio
The proc hit ratio is the main performance indicator for the procedure cache. In this cache, SQL statements, access plans, stored procedures, etc. are cached. Its calculation is done in the same way as for the data cache hit ratio.