Constant SQL Server 80% CPU Utilization

asp.net performance sql-server-2012 cpu-usage sql-server-performance
Alireza Noori picture Alireza Noori · Apr 8, 2014 · Viewed 22.5k times · Source

We have a small (for now) Asp.Net MVC 5 website on a dedicated VPS. When I go to the server and fire-up task manager, I see that "SQL Server Windows NT - 64 bit" is using around 80% of CPU and 170MB of RAM and IIS is using 6% CPU and 400MB of RAM. Server Specs are:

  • CPU 1.90Ghz dual core
  • Memory 2GB
  • Windows Server 2012
  • SQL Server Express 2012
  • Disk Space: 25GB, 2.35 Free.

The database is not very big. Its backup is less than 10MB.

I have tried to optimize the website as much as I could. I added caching to a lot of controllers and implemented donut caching for quite a lot of controllers. But today, even though there were only 5 users online, our search wouldn't work. I restarted the Windows on the server and it started working but I got the high CPU usage the minute server started. Interestingly when I open the SQL Server Management Studio and try to get the report for top CPU-consuming queries it says that there are no queries currently consuming any CPU!!! But at the same time I can see that SQL server is consuming a lot of CPU. How can I examine what is taking all the CPU? Below is a picture from the server:

High CPU Usage

I was/am very careful with designing and implementing the website. All the database access is through latest version of Entity Framework. I just wonder if the server's specs are low. Any help would be very much appreciated.

Update:

Here's the result of the sp_who2 stored procedure.

sp_who2

Answer

Chand picture Chand · Nov 18, 2016

This could happen if the memory set to use is more than the available memory on the box. The default memory setting of 2147483647MB. In our case the AWS box had only 30.5 GB so we changed the setting to 26GB and the CPU usage fell to 40%. You generally want to leave 20% of memory for OS and its operations.

Related questions

Running sites on "localhost" is extremely slow

Having real trouble using my localhost to test sites. It runs extremely slowly! Sometimes it takes up to a minute to load a page. I'm using Firefox and the sites I'm testing run fine on other developers in my office's …

Read More
I just discovered why all ASP.Net websites are slow, and I am trying to work out what to do about it

I just discovered that every request in an ASP.Net web application gets a Session lock at the beginning of a request, and then releases it at the end of the request! In case the implications of this are lost …

Read More
Is String.Contains() faster than String.IndexOf()?

I have a string buffer of about 2000 characters and need to check the buffer if it contains a specific string. Will do the check in a ASP.NET 2.0 webapp for every webrequest. Does anyone know if the String.Contains method …

Read More