Entering content frameFunction documentation 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. To set the parameter Set working set size, refer to SAP note 327494.

The set working set size is used to reserve physical memory space for SQL Server that is equal to the server memory setting. Setting set working set size to 1 means that Windows 2000 will not swap out SQL Server pages even if they can be used more readily by another process when the server is idle.

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 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 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 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 = 1, if the maximum and minimum server memory are set to the same value (i.e. the server memory is FIXED). If the maximum and minimum server memory are different (i.e. the server is making dynamic memory adjustments), set set working set size = 0.

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 pages

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 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 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 %.

SQL Requests

The SQL Requests section is divided into the following areas:

SQL batches

The number of Transact SQL command batches received by SQL Server. For the SAP work processes, a batch is created every time a database commit is required. This number is usually much smaller than the number of database requests issued by ABAP.

Read ahead pages

The number of requests that are read ahead, that is, asynchronously prefetched from disk.

Request buffer pages

The total number of requests for buffer pages, or data cache pages, issued by SQL Server. This is a logical request, so if the page exists in the cache, no physical read/write takes place. If the page is not in cache, then a physical page read will be recorded.

reads

The number of physical page reads into the data cache. The total number of pages, across all databases, is recorded.

writes

The number of physical page writes from the data cache. This is the total number of pages written across all databases.

Full table/index scans

The number of full table or index scans. These scans can be either base-table or full-index scans.

Index range scans

The number of qualified range scans through indexes. This number represents logical, as opposed to physical, access.

Index searches

The number of index searches. Index searches are used to start range scans and single index record fetches.

Probe scans

The number of probe scans that occur in a database. Probe scans are used to find rows in an index or base table directly.

Lazy write

The number of buffers written by the buffer manager's Lazy Writer. The Lazy Writer is a system process whose task is to write dirty buffers to disk when I/O activity is low. The goal of the Lazy Writer is to minimize the time needed by checkpoints to write dirty data pages to disk.

 

 

Leaving content frame