6

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

Community
  • 1
  • 1
Alireza Noori
  • 13,898
  • 24
  • 89
  • 165
  • 1
    Do you have any triggers or stored procedures which could be getting caught in an infinite loop? – CathalMF Apr 08 '14 at 18:24
  • Try directly running `EXEC sp_who2`. Are there any active processes? You probably recognize them by the amount of CPU time spent. You can also try configuring symbols in process explorer and looking at the stacks in sqlservr.exe. Or capture a profile with PerfView. Stacks are often illuminating. – usr Apr 08 '14 at 19:16
  • @CathalMF No, just basic EntityFramework stuff for Asp.Net MVC. – Alireza Noori Apr 08 '14 at 19:45
  • @usr I don't have much experience in admin stuff. Could you help me out a little bit more? Like posting links to articles I could read and tutorials. That would be very much appreciated. – Alireza Noori Apr 08 '14 at 19:46
  • yeah do an `sp_who2` as @usr suggested. You might want to boot up a SQL Profiler instance, since EF is notorious for serious issues with N+1 and at least 4 nested table select statement for a simple object without expansion. – GoldBishop Apr 09 '14 at 00:05
  • And backups are not a gauge of a Database Size, check your Log file, in certain environments the Log File was the hog, mostly due to geo-spatial data streams. make sure that SQL Server instance is throttled, by default it will consume 100% of what is available, if the need arose. That includes CPU, Memory, and HDD, no matter how many processors or how much RAM/HDD you have, it will consume ALL :0 – GoldBishop Apr 09 '14 at 00:07
  • @GoldBishop Thanks for your comments. I ran the `sp_who2` SP and put the result in the update. Another point is that I stopped the website in IIS and the process went down. So I know it's related to my website. – Alireza Noori Apr 09 '14 at 08:42
  • Its your EF implementation....did you do Code or Database first? I have found that i end up building my own EF design and dont use the Wizards. – GoldBishop Apr 09 '14 at 11:00
  • SQL Profiler will be your biggest friend here. Note that it will only profile the start and end of events. If you have a very long running query then only recording start/end of batches will not show you anything until the query finishes. Also, check http://www.brentozar.com for some great scripts, e.g. SP_AskBrent which will tell you exactly why your server is running slow! – JLo May 16 '14 at 13:24
  • I think your hardware is being squeezed too much. 2gb to run an IIS and SQL Express is going to push memory to the max and probably starve SQL of memory. Further it looks like your disk space is really tight as well. Plus this looks like it's a Virtual Machine that's potentially over-provisioned? RUnning a local copy of SQL Server you probably need closer to 4gb. – Rick Strahl Jun 10 '14 at 22:48

2 Answers2

2

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.

Chand
  • 391
  • 2
  • 7
  • I develop using a VM (host macOS, vm Windows 10). I had only assigned 2 GB to the VM, and "SQL Server Windows NT - 64 bit" was at constant 60% CPU utilization, even idling. Allocating 4 GB seems to have solved the issue completely, pages reload much faster. Much joy. Using Veertu to emulate has weirdly low macOS RAM usage compared to Parallels or VMWare. – Peheje Jan 09 '17 at 21:48
0

I would agree running SQL Profiler to spot large query durations and large write operations. Try running perfmon and spotting any potential connection leaks (reclaimed connections).

Spencer
  • 251
  • 2
  • 7
  • We ended up changing the server and even in that, we found a problem. Here's the question related to that: https://stackoverflow.com/questions/23700383/high-cpu-usage-by-iis-even-after-i-stop-all-the-websites – Alireza Noori Jun 12 '14 at 08:13