SQL Server 2017 Backup of Filestreams is slow

Question

SQL Server 2017 Backup of Filestreams is slow

Paolo Taverna 101

We use SQL Server 2017 Standard Edition with CU 29
Our Database has approximately 400GB Data (Standard SQL Server tables and index) and 1.2 Terabyte of SQL Server Filestream Files. We use 20 SQL Server FileStream Groups. The Files are distributed equally on the 20 Filestreams Filegroup.
Each SQL Server FileStream Filegroups has approximately 162'000 Files. Each of this FileStream Filegroup is approximately 55GB.

The SQL Server Data, Index and SQL Server Filestream Filegroup are on the same disk for example on G:
The SQL Server Backups are written on another disk lets say P:

We start the SQL Server TSQL Backup command "with stats=5". After 2 hours it shows 20% and the Table and Index Data are written, it starts to backup the SQL Server Filestreams. After 8 hours it shows 35%. So it took 6 hours for the next 15% of the database.
The backup rate for the SQL Server Filestream is much slower as the backup rate for tables and index.

We are now investigating the setting of the disk with the SQL Server Filestreams. We check:

if "windows search" is disabled
if "Last access time" is disabled, Registry Key NtfsDisableLastAccessUpdate
if "Disable 8.3 file names generation" is set, Registry NtfsDisable8dot3NameCreation

Is there something else that could cause this slow backup performance of the SQL Server Filestreams?

Thank you for advice and help
Kind regards
Paolo

PandaPan-MSFT 1,931 Reputation points

2022-09-01T06:57:38.05+00:00

Hey @Paolo Taverna , have you solved your problem already? If you still have some problems pls let me know and if you are satisfied with my answer pls mark it. Thanks a lot.
Paolo Taverna 101 Reputation points

2022-09-01T08:22:32.787+00:00

Problem still persists. We are testing if we can achieve better performance with the points that i listed in my initial post.
The link in your post is about SQLBackup.exe and cvd.exe (Commvault). We don't use either.
.
PandaPan-MSFT 1,931 Reputation points

2022-09-02T06:11:55.417+00:00

Hello,I'm so sorry that my solution doesn't help. I can only think of three ways to speed up the process of backup:
Use a faster disk.
Store multiple database files on different physical disks.
Reduce database file fragmentation level.
Maybe you can use the following order to see the using time of each database, and to see if the fragmentation slows your speed.
SELECT database_name, backup_start_date, CAST(CAST((backup_size / (DATEDIFF(ss, backup_start_date, backup_finish_date))) / (1024 * 1024) AS NUMERIC(8, 3)) AS VARCHAR(16)) + ' MB/sec' speed
FROM msdb..backupset
ORDER BY database_name, backup_start_date
Best regards
Jong
Paolo Taverna 101 Reputation points

2022-09-02T06:33:01.597+00:00
Thank you for your reply.
The actual state is that we cant run the SQL Server Backup since several month. Its a production environment and it would approximately take 32 hours to complete.
So we stop the SQL Server service at night and backup the whole disk. The disk backup runs approx 3 hours. I know, this situation is not optimal, but at least we have daily full backups.
The environment belongs to a customer. So i have to wait until their IT checks and configures the recommended settings like:

if "windows search" is disabled

if "Last access time" is disabled, Registry Key NtfsDisableLastAccessUpdate

if "Disable 8.3 file names generation" is set, Registry NtfsDisable8dot3NameCreation

Its a step to step approach to see what helps.
As soon as i have new findings i will post them in this thread.

Kind regards
Paolo

4 answers

Your answer

PandaPan-MSFT 1,931 Reputation points

2022-09-01T06:57:38.05+00:00

Hey @Paolo Taverna , have you solved your problem already? If you still have some problems pls let me know and if you are satisfied with my answer pls mark it. Thanks a lot.
Paolo Taverna 101 Reputation points

2022-09-01T08:22:32.787+00:00

Problem still persists. We are testing if we can achieve better performance with the points that i listed in my initial post.
The link in your post is about SQLBackup.exe and cvd.exe (Commvault). We don't use either.
.
PandaPan-MSFT 1,931 Reputation points

2022-09-02T06:11:55.417+00:00

Hello,I'm so sorry that my solution doesn't help. I can only think of three ways to speed up the process of backup:
Use a faster disk.
Store multiple database files on different physical disks.
Reduce database file fragmentation level.
Maybe you can use the following order to see the using time of each database, and to see if the fragmentation slows your speed.
SELECT database_name, backup_start_date, CAST(CAST((backup_size / (DATEDIFF(ss, backup_start_date, backup_finish_date))) / (1024 * 1024) AS NUMERIC(8, 3)) AS VARCHAR(16)) + ' MB/sec' speed
FROM msdb..backupset
ORDER BY database_name, backup_start_date
Best regards
Jong
Paolo Taverna 101 Reputation points

2022-09-02T06:33:01.597+00:00

Thank you for your reply.
The actual state is that we cant run the SQL Server Backup since several month. Its a production environment and it would approximately take 32 hours to complete.
So we stop the SQL Server service at night and backup the whole disk. The disk backup runs approx 3 hours. I know, this situation is not optimal, but at least we have daily full backups.
The environment belongs to a customer. So i have to wait until their IT checks and configures the recommended settings like:

if "windows search" is disabled

if "Last access time" is disabled, Registry Key NtfsDisableLastAccessUpdate

if "Disable 8.3 file names generation" is set, Registry NtfsDisable8dot3NameCreation

Its a step to step approach to see what helps.
As soon as i have new findings i will post them in this thread.

Kind regards
Paolo

Answer 1

Took a lot of time to analyze this issue. Used the process monitor of the sysinternals suite. The microsoft defender service watching the sql server filestream directories slowed down the backup.
Solution:
you have to configure file extension and folder exclusions for the Windows Defender

Open Group Policy Management Console
go to Computer configuration and select Administrative templates
expand the tree to Windows components > Windows Defender Antivirus > Exclusions.
configure the "Path Exclusions" add the directory where all SQL Server Filestream are stored and the In-Memory directory. Value must be 0
open the Extension Exclusions and add "mdf", "ldf", "ndf" with the value 0

No Windows OS restart needed.

Executed a new Full SQL Server Database Backup. Now the Backup I/O for the SQL Server Filestream is the same as for Table / Index Data.

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-10-26T21:08:09.873+00:00

Thanks again for the update!

Answer 2

Olaf Helper 47,441

slow backup performance of the SQL Server Filestreams?

I would say, it's an expected behavoiur.
The backups picks file by file. The database data file is one large one and is processed in one batch.
And the each filestream file is processed, one by one and that takes some time.

Paolo Taverna 101 Reputation points

2022-08-26T12:30:47.973+00:00

This just approximate calculation. The first 400GB (table/index) are backed up in 2 hours (stats showing 20%). I get a backup rate around 60MB/sec.
For the next 15% it takes another 6 hours. The approximate backup rate is around 12MB/sec.

On the same server i have build a 40 GB Database without filestreams. It took only 4 minutes for a full SQL Server backup. An approx of 170MB/sec backup rate. It seems that the backup of SQL Server database with filestreams is slower than a backup of a database without filestreams. With the actual Backup rate it would take 32 hours to make a full backup of a 1.2 Terabyte Database.

Do you have a source / link where i can read about this performance issues?

Answer 3

PandaPan-MSFT 1,931

Hi @Paolo Taverna ,
To see the situation of filestream, you can use the Windows Resource Monitor. And there is one article slow-backup-performance-of-sql-server-database-with-a-large-filestream-151 you may follow its steps. I hope it can give you some help.

Best regards

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 4

Paolo Taverna 101

Some news regarding this problem. Yesterday i experienced the same behavior on the server of another customer.
The backup of the relational table had an I/O of 200MB/sec, the I/O on the Backup of the SQL Server Filestreams was only 8MB/sec.
On the server of this customer i had the possibility to install the sysinternals Tools and analyze which processes accessed files.
Turns out that a Antivirus Software with their services "blocked / slowed down" the SQL Server TSQL Backup Statement.
Stopped the Antivirus Services on this Server - the backup of the Filestream then raised to 200MB/sec.

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-09-08T20:54:19.007+00:00

The evil antivirus strikes again!

Thanks for reporting back! Very useful for people who run into the same issue.

Share via

SQL Server 2017 Backup of Filestreams is slow

4 answers

Your answer