CPU utilization measures the load of all the tasks that are being performed by the server. This depends largely on the type of operation. For example, Business Intelligence applications typically take over 80% of CPU capacity, compared to SQL database applications, where CPU usage below 70% is considered normal. CPU usage above this number means degradation in user response times.
Find out how you can save hours of work when investigating the root cause of this issue.
Symptoms :
Server applications (including SQL Server) are running slow due to high CPU levels.
Impact: High
Slow application responses will harm user experience, which negatively impacts the business flow.
Slow SQL responses will degrade the user experience, resulting in poor efficiency of your organization’s operations.
Expected behavior :
There are no suggested metrics for CPU utilization. The figures will vary based on levels of user activity, time of day, and other factors. Business Intelligence (BI) applications typically load the CPU 20-30 percent above other applications. In addition, load from non-SQL activities can significantly increase the overall utilization.
Average utilization of less than 60% is usually considered good.
Possible causes of high CPU Utilization
1- Long system tasks Priority: Medium
CPU loads might occur once many users use 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, and restores from networked data stores).
Problem identification:
Identify the source of the load and if it is recurrent. Try to reschedule heavy tasks to a time of low user demand.
If you are experiencing high CPU usage on a system and you have ruled out database-related issues, there are some steps you can take to identify the cause as detailed next.
- Use built-in OS tools such as Performance Monitor to identify which processes use the most CPU resources. On Windows, check the Task Manager or Activity Monitor. Notice that you can’t guarantee precise results when checking the online status without historical events. On Linux, using “top” or “htop” commands provides real-time information about CPU usage. Once you identify problematic tasks, you can use the “pidstat” command to monitor individual tasks currently being managed by Linux when checking the CPU utilization of specific processes.
- Identify the process causing the high CPU usage and then determine the cause. You can use a process explorer tool, such as 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 them. It might take time to identify them.
- Make sure your software and drivers are up-to-date.
Recommended action :
Cancel unnecessary programs that cause high CPU utilization or look for the cause that suddenly the CPU utilization of a specific program has risen.
2- Long SQL tasks Priority: Medium
SQL Processes started to load 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:
Check the database server activity by looking for the most CPU-consuming queries. Look for non-optimal query plans and database server settings.
If you are experiencing high CPU usage due to a database server like SQL Server or Oracle, there are several steps you can take to identify the cause:
- Check the database server activity: You can monitor the database server activity to identify which SQL statements are consuming the most CPU resources. For SQL Server, you can use the built-in activity monitors or SQL Profiler. For Oracle, you can use tools like AWR to identify the top SQL statements. 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 changes to the schema or database settings. It might be complicated and take days of work.
- Check for hardware issues.
- You should consider adding cores; however, it might be expensive.
Recommended action :
Fix database server settings that are not optimal and might cause high performance. Optimize queries execution plans, add or remove indexes, and make changes to database schema or database settings. Add cores if possible financially.
