10

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?

Improve this question
6
  • Could be a bad install. Consider a fresh install of the OS and SQL. All service packs. You can chase stuff like this forever. If the problem is still there then you can reasonably dismiss install.
    – paparazzo
    Aug 12, 2016 at 17:40
  • 7
    Do you have an antivirus installed? I'd add an exclusion for the SQL Server executable, as well as MDF, NDF, and LDF files.
    – Randolph West
    Aug 12, 2016 at 18:29
  • 1
    Did you look into power saving settings as well?
    – Randolph West
    Aug 12, 2016 at 22:21
  • I recall similar behaviour of 2005 if database option auto_update_statistics_async was turned on.
    – Roger Wolf
    Aug 15, 2016 at 2:18
  • Try to take database offline and check the cpu usage. If the cpu usage dont go down then its sql server database engine problem.
    – Muhammad Yousaf Sulahria
    Dec 5, 2016 at 15:30

2 Answers 2

Reset to default
3

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.

Improve this answer
1
  • I meanwhile found that starting / stopping another software with excessive memory usage (like Visual Studio) makes the issue worse in a reproducible manner. So I would confirm it's a memory issue - SQL-Server starts paging when being squeezed to much.
    – AndreasS
    Feb 9, 2018 at 3:41
-1

The following patch describes a similar problem. It may be worth trying:

https://support.microsoft.com/en-us/help/3195888/fix-high-cpu-usage-causes-performance-issues-in-sql-server-2016

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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