2

I have a VM 2012 R2 server with SQL Server 2014 Express SP2 installed. There are some applications and databases, but nothing too big. Databases are small, about 100MB size combined.

At some moment (not from the beginning) the SQL Server process started to show constant high CPU usage. Usually the CPU load is about 15-20%. It shows a periodic picture with a minute of 15-20% load, and a couple of seconds of 0% usage between the periods. Sometimes SQL Server stops to do that (whatever he does...) for like 2 minutes, then starts again with 10-20 seconds of >95% load and "usual" 15-20% after...

Of course it affects performance for users.

I have investigated all I could (session reports, query reports, profiler, memory allocation SP...), but I haven't found anything that could cause this. Or, exactly, I've found there are no sessions, no queries and nothing in the actual applications installed that could load the CPU so much. I tried to shut down all applications using SQL Server and detach their DBs and then restart the server. No use.

I even uninstalled and reinstalled SQL Server, but it started to consume CPU short after startup, while being "clean".

Also, there was no "unusual" configuration, it was just a clean VM with a straightforward SQL Server installation. I have installed it, created the DBs, logins and that's it.

From the Troubleshooting SQL Server high CPU usage by Karthick P.K, I've checked the possible issues with timer, that can be caused by power plan or other settings. Using -T8038 at startup did not affect the CPU load. Power plan is set to high performance.

Why is SQL Server using so much CPU, even if there are no queries and attached DBs? How can I at least learn what's happening?

Improve this question
6
  • 2
    Do the CPU issues coincide with backups, or with VM snapshots being taken? Have you considered using sp_whoisactive to perform captures to a table every few seconds to help catch the culprit?
    – Nic
    Sep 29, 2016 at 14:29
  • @Nic Nope, I already tried to exclude all "external" reasons such as scheduled tasks, apps, even had databases detached.
    – yaapelsinko
    Sep 29, 2016 at 20:24
  • Is SQL Server definitely using this CPU, or is it another guest on the VM that is stealing resources, forcing a skewed reading on the SQL side?
    – Randolph West
    Sep 29, 2016 at 20:30
  • @RandolphWest it is the SQL Server process, by any monitoring tool.
    – yaapelsinko
    Sep 30, 2016 at 10:18
  • Have a read of this: logicalread.solarwinds.com/…
    – Sir Swears-a-lot
    Oct 3, 2016 at 8:23

1 Answer 1

Reset to default
2

Issue is the following.

Let a Hyper-V VM is configured with high startup memory (like 10240MB), and dynamic memory limits active and are like 512-10240 range.

VM starts up with full amount of RAM available. Short after load the host reclaims excess memory from the VM. At the same moment SQL Server process starts to create that unexplainable CPU load.

It doesn't happens though, if startup memory set to 512MB.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.