How To Fix SMMSS_ALERTS011 - This alert tracks the percentage of CPU used by SQL Server

SAP Error Message - Details

• Message type: E = Error

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

• Message number: 011

• Message text: This alert tracks the percentage of CPU used by SQL Server

What causes this issue?

This alert shows the percentage of cpu capacity on the database server
used by SQL Server. The value is taken from the global variable
@@cpu_busy. The threshhold values are set depending on whether a stand
alone database server is in use, or if an SAP instance exists on the
database server also.

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?

If the threshhold values are incorrect, right click on the object and
select properties. Then, customize the values to your liking. If the
threshhold values are being reached, try to off load computing tasks
from the database server or add cpus.

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_ALERTS011 - This alert tracks the percentage of CPU used by SQL Server ?

  The SAP error message SMMSS_ALERTS011 indicates that there is an alert related to the percentage of CPU usage by SQL Server. This alert is part of the monitoring and alerting framework in SAP systems that utilize SQL Server as their database.
  Cause:
  The alert is triggered when the CPU usage of the SQL Server exceeds a predefined threshold. High CPU usage can be caused by several factors, including:
  
  Inefficient Queries: Poorly optimized SQL queries that consume excessive CPU resources.
  High Transaction Volume: A sudden increase in the number of transactions or concurrent users can lead to higher CPU usage.
  Lack of Indexing: Missing or poorly designed indexes can cause SQL Server to perform full table scans, which are CPU-intensive.
  Resource Contention: Other processes or applications running on the same server may be consuming CPU resources.
  Configuration Issues: SQL Server may not be configured optimally for the workload it is handling.
  
  Solution:
  To address the SMMSS_ALERTS011 alert, consider the following steps:
  
  
  Analyze SQL Queries:
  
  Use SQL Server's Query Performance Insights or Execution Plans to identify slow-running queries.
  Optimize these queries by rewriting them, adding indexes, or updating statistics.
  
  
  
  Monitor Resource Usage:
  
  Use SQL Server Management Studio (SSMS) or Performance Monitor to track CPU usage and identify which processes are consuming the most resources.
  Check for any blocking or deadlocking issues that may be affecting performance.
  
  
  
  Review Indexing Strategy:
  
  Ensure that appropriate indexes are in place for frequently accessed tables.
  Consider using SQL Server's Database Engine Tuning Advisor to get recommendations for indexing.
  
  
  
  Adjust SQL Server Configuration:
  
  Review SQL Server settings such as max degree of parallelism, memory allocation, and CPU affinity.
  Ensure that SQL Server has enough resources allocated to handle the workload.
  
  
  
  Scale Resources:
  
  If the workload has increased significantly, consider scaling up the hardware (more CPU, RAM) or scaling out (adding more instances).
  
  
  
  Regular Maintenance:
  
  Implement regular database maintenance tasks such as index rebuilding, updating statistics, and cleaning up old data.
  
  
  
  Related Information:
  
  Monitoring Tools: Utilize tools like SQL Server Profiler, Extended Events, or third-party monitoring solutions to gain insights into SQL Server performance.
  SAP Notes: Check SAP Notes related to performance tuning and SQL Server integration for specific recommendations and best practices.
  Documentation: Refer to the official Microsoft SQL Server documentation for guidance on performance tuning and optimization techniques.
  
  By following these steps, you can effectively address the high CPU usage issue indicated by the SMMSS_ALERTS011 alert and improve the overall performance of your SQL Server database in the SAP environment.