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?

  • 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 '16 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 '16 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 '16 at 20:30
  • @RandolphWest it is the SQL Server process, by any monitoring tool. – yaapelsinko Sep 30 '16 at 10:18
  • Have a read of this: logicalread.solarwinds.com/… – Sir Swears-a-lot Oct 3 '16 at 8:23
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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