I have a ~10 day old installation of WinServer2012R2 and SQL Server Express 2016 for testing. I am the only user on this machine. A database with a .bak from SQL Server 2005 of ~250MB is restored without any issues. After a reboot of the machine the process "SQL Server NT - 64 Bit" uses 0% CPU.
After a couple of minutes or hours and some simple queries (no updates/inserts!) from SSMS CPU usage of "SQL Server NT - 64 Bit" suddenly jumps to ~15% and stays there, even when idle. From that point on queries which usually take less than a second suddenly take 2 minutes. During an actual query the CPU usage does NOT increase. The server becomes virtually unusable in this state.
Only connecting SQL Server Profiler then takes >30sec. Beside my own queries I see only very few queries from SQLServerCEIP / SQLTELEMETRY, ~3 per minute.
Restarting SQL-Server does not solve it. CPU usage jumps right back to ~15%. Even after hours SQL-Server does not recover. Only rebooting the entire machine solves the issue.
As this is an "out of the box" installation, there is only a small database, virtually no queries, only me as a user and probably no locks, the many articles on regular SQL-Server performance issues talk about lot of things that don't really apply here. It seems as SQL-Server exclusively wants to concentrate on some internal task.
This is a virtual machine with 2GB RAM and dual Xeon at 2GHz. I also have VS2016 on it and it's really fast. No antivirus, not even Windows Defender. Already late here. I will try the sp_whoisactive tomorrow. I really wonder WHAT SQL-Server IS doing there... On the previous machine with 1 GB the same DB ran under SQLServer2005 for 10 years without issues...
I am not an SQL-Profiler expert. Where should I start looking?
auto_update_statistics_async
was turned on.