High CPU on a SQL Server could have various causes and we need to provide the resolution accordingly.
One of our customer reported CPU being 100% for few hours and it was not going down even for few seconds. Majority of CPU workload was due to SQL processes only. This customer had never complained about performance issues on SQL database servers .
Business critical case was opened as it was first day of Thanks Giving and it needed to be resolved immediately so that business is not suffered. All the key stakeholders including customer’s CEO were on the call.
We were even finding it difficult to open SSMS on local database server so we connected instance from another server in customer’s domain.
We did basic troubleshooting and everything looked good:
- There were no long running queries.
- Execution plans looked optimal.
- No missing indexes were observed. Existing indexes were properly maintained.
- Statistics were properly updated.
- Performance specific settings on servers like MAXDOP , cost threshold for parallelism, Instant file initialization , optimize for ad-hoc workload , count of VLFs etc. all looked fine.
Below article was of great use :
https://learn.microsoft.com/en-us/troubleshoot/sql/performance/troubleshoot-high-cpu-usage-issues
We then looked at point 10 in above article which says to scale up server to have more CPUs if below scenario is observed:
Individual queries were using very less CPU however count of these individual queries being run was huge at any point of time. This caused total CPU workload of all the queries to contribute to high CPU scenario. Customer’s CEO was also on call and he also confirmed that huge count is due to more users on Thanks Giving. He told that workload was of 5x of normal days. Database server was a physical server and CEO immediately agreed to scale up CPUs. Licensing, costing , downtime all were discussed and CPUs were upgraded.
We monitored servers for next few days. Customer’s business went smooth during Thanks Giving and system sustained even 10x workload.