SQL Server CPU usage is overloaded sometimes

Question

We do have a couple of servers, one is dedicated to the website without SQL and the other server is dedicated to SQL.

Now the server running SQL is quite powerful, yet sometimes the server CPU is just maxed out at 100%.

Here is a couple of screenshots showing whats going on.

CPU maxed out:

CPU running ok:

As you can see the server is quite powerful.

Additional notes.

1. We are running nopcommerce version 3.70
2. The website has been heavily customized, by other developers.
3. The website has about 4000 - 5000 products.
4. When the cpu is maxed out load times are shocking. > 30 seconds and sometimes more than 1 min.

Is anyone able to shed some light on what might be going on, or guide me through some things to check.

Cheers

UPDATE: The following two screenshots are the results from the two queries @S4V1N suggested I run.

Answer 1

Is anyone able to shed some light on what might be going on, or guide me through some things to check.

Let's start with the obvious stuff. Your server, for a production SQL box, isn't that highly powered. For example, this is what I do development work on, and I'm just a consultant goofing around most of the time.

That being said, more hardware may not solve all your problems. I'm not familiar with the platform you mentioned, but typically when you start with a base product, and you start referring to it as "highly customized", that means the code and indexes that used to work with the base product probably don't work so well anymore.

What can you start with?

The company I work for writes free scripts that can help you get to the root of these things. Whether it's something you can fix with hardware, or with vendor support is another matter.

A lot of vendors don't like you making changes to their products. But hey, at least you can start a conversation about making changes.

1. Run sp_Blitz

EXEC sp_Blitz @CheckUserDatabaseObjects = 1, @CheckServerInfo = 1;

This will give you a general idea about what's going on health-wise with your server. Pay attention to a couple settings here: MAXDOP and Cost Threshold for Parallelism, and see my answer here for why they can make a difference in your situation.

2. Run sp_BlitzFirst

EXEC sp_BlitzFirst @SinceStartup = 1;

This will tell you what your server has been up to since it started up. Look at the wait stats pane to see where your bottlenecks are.

3. Run sp_BlitzCache

EXEC sp_BlitzCache @SortOrder = 'cpu';

Since you're concerned about your plan cache, look at it by CPU first. You may find other sort orders helpful down the line, but start here.

We'll warn you about all sorts of stuff in your query plans, and give you as much historical information as possible.

4. Run sp_BlitzIndex

EXEC sp_BlitzIndex @DatabaseName = N'YourDatabaseName', @Mode = 4

The main thing you'll want to look at here is high value missing indexes, which should be right up at the top.

4. When you catch cpu spiking, run sp_BlitzWho

EXEC sp_BlitzWho

This'll let you know what queries are running when CPU is high. They may be different from what's in the plan cache.

Hope this helps!

Answer 2

Monitoring CPU usage using task manager is not really a reliable source. There are many other(such as core OS activity,device drivers) non-sql processes running in the background that could be adding extra overhead without you even knowing.

PerfMon is the tool you should reach for in these cases.

Processor/%Privileged Time, Processor/ %User Time, Process (sqlservr.exe)/ %Processor Time

Will give you an idea of what is actually happening with your SQL server, without explaining each of these counters, turn on description checkbox and read from there, but it will essentially show you the ratio of SQL Server vs Other processes usage.

Even though its easy to spot, it is not so easy to diagnose. There could be other "hidden" issues that are indicating that the processor is the problem. Such as having lot of compilations/recompilations, which are issues related to non-parametrized queries or forced recompilations. You can find these metrics in Perfmon: SQLServer:SQL Statistics/SQL Compilations/sec, SQLServer:SQL Statistics/SQL Re-Compilations/sec.

SQLServer:Plan Cache/Cache hit Ratio Indicates memory problem, but excessive page flushing in/out of memory also add extra CPU usage.

DMVs can also help you diagnosing the problem.

SELECT TOP ( 10 )
wait_type ,
waiting_tasks_count ,
( wait_time_ms - signal_wait_time_ms ) AS resource_wait_time ,
max_wait_time_ms ,
CASE waiting_tasks_count
WHEN 0 THEN 0
ELSE wait_time_ms / waiting_tasks_count
END AS avg_wait_time
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%' 
AND wait_type NOT LIKE 'XE%'
AND wait_type NOT IN
( 'KSOURCE_WAKEUP', 'BROKER_TASK_STOP', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',
'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE',
'DBMIRROR_EVENTS_QUEUE', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE',
'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS',
'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH' )
ORDER BY wait_time_ms DESC

See if you can find SOS_SCHEDULER_YIELD & CXPACKET waits. If SOS_SCHEDULER_YIELD waits are high you might have some very CPU extensive queries, which you should pay attention to. This:

SELECT TOP ( 10 )
SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset ) / 2 ) + 1)
AS statement_text ,
total_worker_time / 1000 AS total_worker_time_ms ,
( total_worker_time / 1000 ) / execution_count
AS avg_worker_time_ms ,
total_logical_reads ,
total_logical_reads / execution_count AS avg_logical_reads ,
total_elapsed_time / 1000 AS total_elapsed_time_ms ,
( total_elapsed_time / 1000 ) / execution_count
AS avg_elapsed_time_ms ,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC

will show you the most CPU extensive queries which you might want to optimize further.

Optimizing these queries you might find missing indexes, outdated statistics, Non sarg-able queries which are real issues that are behind high CPU usage.

Its not the only blueprint how to fix CPU problems but i hope it gives you a good start!

Answer 3

There are two things possibly going on.

Let's start with the following: Under most use cases for a SQL Server, the CPU is the least taxing element. It just does not overload. Period. There are some exceptions, but in I would say 99% of the cases CPU overload can be traced back to bad SQL or table design that makes the SQL Server waste high amounts of CPU cycles - doing data conversion that is actually unnecessary.

Example: Table 1 has an ID field that is integer, Table 2 references it but the foreign key is character (storing a numeric value). Welcome to data conversion on every join - totally avoidable and generally a little "stupid" mistake (overlooked data type or something) that is easy to fix. NEARLY as bad is the use of NVarchar (in SQL Server) character fields for lookup - Unicode string comparison rules are complex, the N (for example for a product code table) easily is 20 times as CPU intensive as a pure VARCHAR (which does not handle Unicode). Sometimes you need it, but sometimes it just is stupid regulations (All fields are Unicode, even those containing things like phone numbers).

To fix this you really need to go into the slow queries and identify them - there are plenty of tools around, even in SQL Server Management Studio. The Activity Monitor (documentation can help you find it) will gladly give you the most expensive recent queries by CPU - then you can look at them and start figuring out where the error is. I am pretty sure a decent investigation will point towards a number of easy to fix issues that are quite trivial but result in the mentioned extreme CPU usages.

But as element to - you state so nicely: "Now the server running SQL is quite powerful" - I hate to go against your illusion here, but this is the year 2017. A 4 core machine with 14gb memory is not "quite powerful". It is essentially "low end". I would not work on a desktop like that. Now, I am not saying "get a bigger machine", just do not boast ultra low end specs as high end, please. There are TABLETS on the market that are more powerful than your "quite powerful" server. Note though that a problem on the server availability (not enough memory, not enough IO bandwidth) generally results in LOW CPU (CPU waiting), so this is not related to this particular problem. You may just consider server scaling once you get into trouble. To give you an idea HOW low end your machiene is - you run SQL Server, which requires a licensed Windows. The smallest Windows Server license you can buy covers 16 cores, 4 times your number.