SQL Server Performance – CPU Usage Overloaded Sometimes

performanceperformance-testingsql serverwindows-server

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:

enter image description here

CPU running ok:

enter image description here

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.

enter image description here

enter image description here

Best Answer

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.

NUTS

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.

  1. 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.

NUTS

  1. Run sp_BlitzCache

EXEC sp_BlitzCache @SortOrder = 'cpu';

NUTS

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.

  1. 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.

  1. 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!

Related Question