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?
Best Answer
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.