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?
Best Answer
Since your CPU utilization is low (and you have a great CPU set), I would check for memory pressure first. With SQL Express 2016 you are limited to 1410 MB of memory, but your VM only has 2GB. Give your VM 1GB more of memory and see if that helps. You can also check your SQL Server log if it is paging memory to file.
If that does not work, try setting the database setting LEGACY_CARDINALITY_ESTIMATION to ON. Reference: Using DB Compatibility Level 130 with Old CE in SQL Server 2016. We ran into this with some of our upgrades; results will vary with each database.