How To Fix SMMSS_ALERTS018 - This alert shows if the memory setting for SQL Server follows SAP rec.

SAP Error Message - Details

• Message type: E = Error

• Message class: SMMSS_ALERTS - Message class for MSSQL rz20 messages

• Message number: 018

• Message text: This alert shows if the memory setting for SQL Server follows SAP rec.

What causes this issue?

This alert shows is the memory settings for SQL Server, defined as
either FIXED, RANGE or AUTO, follow SAP recommendations. SAP
recommends that memory be FIXED, max server memory = min server
memory, for systems where SQL Server runs with an SAP instance. AUTO,
where max server memory is large and min server memory = 0, is
recommended for stand alone database servers. RANGE is recommended in
certain cases where update work processes are present on the database
server. These settings are just guidelines, hence only a yellow
condition if they aren't followed.

System Response

The system issues an error message and will not allow you to continue with this transaction until the error is resolved.

How to fix this error?

Procedure for System Administrators

Error message extract from SAP system. Copyright SAP SE.

Smart SAP Assistant

• What is the cause and solution for SAP error message SMMSS_ALERTS018 - This alert shows if the memory setting for SQL Server follows SAP rec. ?

  The SAP error message SMMSS_ALERTS018 indicates that the memory settings for SQL Server do not align with the recommendations provided by SAP. This alert is part of the monitoring and alerting framework in SAP systems that utilize Microsoft SQL Server as the database.
  Cause:
  The primary cause of this alert is that the SQL Server memory configuration is not optimized according to SAP's best practices. This can lead to performance issues, as SQL Server may not have enough memory allocated to handle the workload efficiently, or it may be over-allocated, causing resource contention with other applications.
  Solution:
  To resolve this issue, you should adjust the memory settings for SQL Server to align with SAP's recommendations. Here are the steps you can take:
  
  
  Check Current Memory Settings:
  
  Open SQL Server Management Studio (SSMS).
  Connect to your SQL Server instance.
  Right-click on the server name and select "Properties."
  Go to the "Memory" section to view the current settings for "Minimum server memory" and "Maximum server memory."
  
  
  
  Adjust Memory Settings:
  
  Minimum Server Memory: Set this to a value that ensures SQL Server has enough memory to operate efficiently. This is usually set to a low value (e.g., 0 or a small number).
  Maximum Server Memory: This should be set to a value that allows SQL Server to use a sufficient amount of memory without starving the operating system or other applications. A common recommendation is to allocate around 70-80% of the total available memory on the server to SQL Server, depending on the overall workload and other applications running on the server.
  
  
  
  SAP Recommendations:
  
  Refer to the SAP documentation for specific memory allocation recommendations based on your SAP system's version and workload. SAP notes and guides often provide detailed instructions on optimal configurations.
  
  
  
  Monitor Performance:
  
  After making changes, monitor the performance of both SQL Server and the SAP application to ensure that the adjustments have had a positive impact.
  
  
  
  Restart SQL Server (if necessary):
  
  In some cases, you may need to restart the SQL Server instance for the changes to take effect.
  
  
  
  Related Information:
  
  SAP Notes: Check for relevant SAP Notes that provide guidance on SQL Server configurations for your specific SAP version.
  SQL Server Documentation: Review Microsoft's documentation on memory management for SQL Server to understand how memory allocation works and best practices.
  Performance Monitoring Tools: Utilize performance monitoring tools to keep an eye on memory usage and performance metrics after making changes.
  
  By following these steps, you should be able to resolve the SMMSS_ALERTS018 alert and optimize your SQL Server memory settings for better performance with your SAP system.