Memory 
Use
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 will use a constant amount of memory, specified by these two parameters. This is the recommended setting for a central system. If FIXED is not set, and an SAP instance is running on the same server, SAP and SQL Server will compete for memory. |
|
AUTO: |
SQL Server will dynamically allocate memory. It requires between 4 MB and 2 GB. 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 will use memory in a range between these 2 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 will most likely 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. Because 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. Because 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.