1

I have a server 2012R2 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 SLQ 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 the 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 is 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 did uninstall/reinstall 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.

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

Also, from the link https://mssqlwiki.com/2012/10/04/troubleshooting-sql-server-high-cpu-usage/  I've checked the possible issue with timer, that can be caused by power plan or other settings. Using -T8038 at startup did not affect on CPU load.

  • Have you checked the Windows power plan settings? That would be something easy to try if it is set to the default (Balanced). It needs to be set to High Performance. – Greg Askew Sep 25 '16 at 19:54
  • @GregAskew It was "balanced", I've set it to "high performance" as you advised. Still no changes. What's weird is that I've never seen something like that, doing many similar installations for quite a few years. – yaapelsinko Sep 26 '16 at 3:51
0

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, privacy policy and cookie policy

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