Symptoms: Individual processes are consuming excess levels of 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.
Default settings are:
| CRITICAL : | 70% | |
| MEDIUM : | 60% | |
| LOW : | 50% |
Timespan: more than 5 minutes
Expected behavior :
There are no suggested metrics for CPU utilization. The figures will vary based on levels of user activity, time of day, etc. Load from non-SQL activities can also significantly increase overall utilization levels.
Possible causes
Long system tasks Priority: Medium
Long tasks performed outside SQL, such as anti-virus scans, backups, etc. started to load CPU.
Recommended action :
Identify the source of the load (using built-in OS tools such as PerfMon) and if it is recurrent, try to reschedule to a time of low user demand.
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)
Recommended action :
Identify the source of the load and if it is recurrent, try to reschedule to a time of low user demand.
Inefficient query design Priority: Medium
Incorrect query code may result in inefficient data access.
Recommended action :
Look for functions over fields that can be tuned or avoided in order to improve performance. Minimize the number of repeats (reading records more than once) as much as possible. Minimize the number of fields and records in the result sets as much as possible so that they would only return what is needed and nothing more. Break complex queries to more simple steps. Look for often used “Cluster Lookup” and “Hush Merger” and minimize their frequency by optimizing the code. Make sure that each transaction has COMMIT (in case of no errors) and ROLLBACK (in any case of an error) statements where applicable.
Missing indexes Priority: Medium
Recommended action :
Detect missing indexes by looking at the execution plans.
Background
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). CPU usage above this number means degradation in user response times.
