CPU utilization measures the load of all the tasks that are being performed by the server, including SQL Server itself as well as ancillary operations (shared applications). Process CPU is a specific CPU utilization and time consumed by a specific process running in the server. This process might be an application, system program, or resource.
Find out how you can save hours of work when investigating the root cause of this issue.
Symptoms :
Individual processes are consuming excess levels of average CPU power, which can affect the overall performance.
Impact: Medium
High levels of load on the total CPU processing capacity can degrade user responses, resulting in poor efficiency for all operations. In addition, the program itself might operate slower than usual, causing all program’s operations to execute slower.
Expected behavior :
The figures will vary based on levels of user activity, time of day, etc. Loads from non-SQL activities can also significantly increase overall utilization levels.
Measuring how the performance of the server is affected by the process CPU load depends on the specific program. ERP applications usually have at least several processes activated at a time, while others have only one process. The total CPU value an application consumes should not exceed 50% utilization for production servers. However, some applications might affect the user’s activity even with a 20% load. The number of cores should also be taken into account.
Possible causes of high process CPU load
1- Long system tasks Priority: Medium
System tasks might run longer when multiple applications or processes run on the server and require CPU. This can cause the CPU of a specific process to be higher due to a heavy workload.
CPU loads might occur once many users are using the same application on the same machine, which happens regularly in Terminal servers. Otherwise, unusual levels of CPU load activities may be coming from external operations (anti-virus scans, backups, restores, etc.) from networked data stores. Probably part of the application will require using only an individual core.
Problem identification:
Identify the source of the load. If it is recurrent, try to reschedule some applications to a time of low user demand or regulate the overall performance of the applications.
- When trying to identify the source of the load, check the Task Manager or Activity Monitor: use built-in OS tools such as Performance Monitor to identify which processes are using CPU resources. You can’t guarantee precise results when checking the online status with no historical events.
- Identify the process causing high Core usage and then determine the cause of it. You can use a process explorer tool like Process Explorer for Windows or Htop for Linux.
- Look for unnecessary startup programs: Some programs may start automatically and run in the background, consuming CPU resources. You should disable it. It might take time to identify it.
- Make sure your software and drivers are up-to-date.
Recommended action :
Cancel unnecessary programs that cause high Core utilization or change their execution to a time when work demand is lower. Upgrade software and hardware if needed.
2- Long SQL tasks Priority: Medium
Several of the programs might depend on the performance of queries.
It loads the CPU due to long tasks performed, such as :
- accessing data where indexes are missing or corrupt (causes long loops)
- pulling large volumes of data (results in many threads or long loops)
- coding problems (inefficient organization, missing indexes causing many threads or long loops)
Problem identification:
Identify high CPU-consuming queries which are running from applications that currently consume high CPU for their process.
- Check the database server activity: You can monitor the database server activity to identify which SQL statements are consuming the most CPU resources. You can use the built-in activity monitors or SQL Profiler for SQL Server. You can use tools like AWR to identify the top SQL statements for Oracle. This mission might be complicated and take hours or days of work, requiring a highly skilled DBA.
- Check the database server configuration. Make sure they are optimized for your workload.
- Check the query execution plans for the top CPU-consuming SQL statements to identify inefficiencies or potential improvements. This mission might be complicated, requiring a highly skilled DBA that can view a complete SQL query plan that might be long and complicated.
- Look for a way to optimize the SQL statements for better performance. This may involve rewriting the SQL statements, adding or removing indexes, or making other schema or database settings changes. It might be complicated and take days of work.
- Check for hardware issues. Consider adding cores if needed.
Recommended action :
Fix database server settings that are not optimal and might cause low performance. Look for functions over fields that can be tuned or avoided in order to improve performance. Optimize queries’ execution plans, add or remove indexes (while detecting it by using query execution plans), and make changes to database schema or database settings. Add cores if possible.
Break complex queries into more simple steps. Make sure that each transaction has COMMIT (in case of no errors) and ROLLBACK (in any case of an error) statements where applicable.
3- Application-related issues Priority: Low
In some applications, there are tasks that are running by default on working hours, which might overload workflow.
In some cases, the application settings are not optimal for needed workflow or do not fit the host server.
In other cases, the server does not fit the application requirements for better resources than the existent in the hosting machine.
Problem identification:
After trying to check long system tasks or long SQL tasks, you should try checking this issue. Search for processes causing high CPU and seen on the command line as part of the suspicious application that is checked currently. Look for errors.
- Enter the Task manager and follow up on specific processes that are consuming high CPU. Search for the command line in order to check whether these processes are assigned to the same application repeatedly. Take into account that you can only see live data without historical events.
- Look for errors related to that application, these errors might be related to non-optimal application settings or to non-fit hosting machines. You should do this step with the application team.
- Consider changing application settings or upgrading hardware if needed. You should track this issue over time, and it might be hard to track without proper monitoring.
Recommended action :
Try making changes to current application settings while consulting with the application team. Track any change that is done. Consider upgrading hardware if needed.
