Symptoms: Server is consuming high levels of CPU capacity – SQL is running slow
Impact: Medium
Slow SQL responses will degrade the user experience, resulting in poor efficiency.
Expected behavior :
There are no suggested metrics for CPU Core utilization. The figures will vary based on levels of user activity, time of day, etc. Business Intelligence (BI) applications will typically load the CPU by 20-30 percent above other applications. In addition, load from non-SQL activities can significantly increase the overall utilization level.
Possible causes
Long system tasks Priority: Medium
Unusual levels of CPU load activities may be coming from external operations (anti-virus scans, backups, restores etc from networked data stores)
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 :
See our recommendations regarding deadlocks here and free memory here.
Background
CPU hardware and configuration can cause potential performance problems. It’s necessary to not only look at the speed of or number of CPUs. CPUs can become a point where the whole system is bottlenecked. If there is such a bottleneck, you may not even see the CPU performance at 100%. CPUs have command queues in much the same way that disks have I/O queues. Commands are loaded into a CPU queue and the operation waits for the CPU to become available before performing the operation. When memory problems begin, you will start to see an increase in disk I/O, an increase in disk queuing and a decrease in the buffer cache hit ratio and page life expectancy. As memory requirements increase, you may begin to see error messages like these in the log file:
part of SQL Server process memory has been paged out.
SQL Server has encountered occurrence(s) of IO requests taking longer than 15 seconds to complete
These are not the only time these errors are reported, and this is where AimBetter provides greatly improved control, by correlating all of the relevant performance metrics.
When dealing with SQL Server memory issues, there are a few options to resolve the problems. One solution is to increase server memory, which increases the amount of buffer cache available. This adds to the amount of data in memory and reduces disk I/O.
A further option is to add CPU cores or re-allocate cores between SQL Server and the operating system. It is, however, important to take into account the physical limitations of the server hardware, as well as the logical limitations imposed by the SQL Server version and specific license attached by Microsoft (see the relevant details here)
Other potential solutions include removing clustered indexes for extremely large tables and using only non-clustered indexes for the table, including the Primary Key.
This will only make a difference when the clustered index is being used for lookups, and clustered index seeks are used. If another index is in use, it will not relieve any memory pressure, as the clustered index won’t be in memory. If you’re using clustered index scans, then this turns into table scans that load the table into memory instead of the index. If clustered index scans are being performed, then a new non-clustered index may help the situation without removing the index.
