Search code examples
sql-serverwindowsperformancefilestream

SQL Server 2016 + FILESTREAM + Windows Defender = constant CPU and disk usage

I have an issue that started a few weeks ago after a Windows update, And I cannot find any info about the problem on the interwebs. I have a SQL Server 2016 Express instance installed on an up to date Windows 10 machine, with a database that has a FILESTREAM file group, and a full text search catalog. The database is attached and functions properly as far as I can tell, there is nothing off in the Windows event log. However, since that update, SQL Server constantly churns on the database, using CPU and disk constantly.

I had the database stored on a mechanical hard drive, and the CPU usage was constantly around 30% until I shut down the SQL instance. Restarting it only helps temporarily as the churning soon starts again. Keep in mind this is on an off-network machine (apart from an internet connection). At first I thought I got a virus or something, so I shut down the server, and nuked it from orbit. I got a new SSD, installed Windows 10, installed SQL Server 2016, updated everything, took the MDF and LDF (and filestream folder), moved them over to the new machine, attached the database. No issue at first. Then it starts again, albeit now the CPU usage is much lower, probably because the storage is so much faster.

This is what it looks like in the Resource Monitor: enter image description here

This seems to be related to Windows Defender somehow, as I can start a scan and see the amount of sqlservr.exe handles to the same database blow up live.

The SQL Server logs look like endless pages of this: enter image description here

And all the while the SSMS activity monitor shows no processes or anything database wise that could explain the activity. Keep in mind this is an isolated database on a freshly installed machine with no client connected apart from me.

I have looked at the updates that could cause this, but I see nothing apparent and now I am at a loss as to what to do. The only solution I see is a downgrade to SQL Server 2008 SP3 which I know for a fact worked fine before. I would greatly appreciate any help on this.

Solution

  • The frequent "Starting up database 'Abacus'" message in the SQL Server error log indicate the database is set to AUTO_CLOSE and the database is frequently accessed. This constant opening and closing of the database results in significant overhead and is the likely cause of the high resource utilization you see.

    The simple cure is to turn off auto close:

    ALTER DATABASE Abacus
SET AUTO_CLOSE OFF;

    It is generally best to keep the AUTO_CLOSE database setting off to avoid unnecessary overhead. The exception is a SQL instance hosting hundreds or thousands of databases where most are not actively used.