Solved

Slow backup performance of SQL Server Database with a Large FILESTREAM

  • 8 January 2021
  • 1 reply
  • 2126 views

Userlevel 2
Badge +4

I’m seeing really poor performance for a SQL Server database with a large FILESTREAM. The backup starts out quite fast and then quickly falls back to a much lower throughput. Checking the status tab of the backup operation, I can see that other databases on the same server are backing up quite fast in comparison to the FILESTREAM database. 

icon

Best answer by Ron Potts 8 January 2021, 19:16

View original

1 reply

Userlevel 3
Badge +7

To diagnose whether this issue is specifically for the FILESTREAM database you can run through the below tests.

Check the Windows Resource Monitor networking tab and filter on SQLBackup.exe to see what the current "Send (B/sec)" rate is. This will tell you how fast data is being sent from the SQL Server.

 

 

Check SQLiDA.log on the client for SQL Data Write Timer and Allocate Buffer Timer stats to see whether this value is higher than the recorded send rate.

13304 3820 03/13 10:40:59 952012 stat- ID [SQL Data Write Timer], Bytes [84192264192], Time [899.072058] Sec(s), Average Speed [89.305411] MB/Sec
13304 3820 03/13 10:40:59 952012 stat- ID [Allocate Buffer Timer], Bytes [84196458496], Time [0.034798] Sec(s), Average Speed [2307490.969043] MB/Sec
 

Check CVD.log on the MediaAgent for write speed stats and ensure these are higher than the send rate seen on the client.

2368  57cc  03/13 12:17:34 952012 2068678-470363 [MEDIAFS    ] stat- ID [SI-FS Write Speed] Curr Avg [662.869602] MB/Sec, Bytes [503394701]; Total Avg [390.961698] MB/Sec, Bytes [64901144488], Time [158.31] Secs
 

Test the network between the client and MA using CvNetworkTestTool. This tool is installed with the Commvault packages by default. There is also a workflow which can be downloaded to allow the test to be run from the Commserve. Check whether the recorded throughput is the same as the data send rate in Resource Monitor.

Test the volume which is holding the FILESTREAM data using CvDiskPerf.

Check Windows Event Viewer for previously completed backup events to confirm whether the recorded throughput is lower for the FILESTREAM database.

FILESTREAM database backup completed and recorded throughput of 7 MB/s

[TYPE] Information [TIME] 2020/03/11 08:19:46 [SOURCE] MSSQLSERVER [COMPUTER] SQLCLIENT.domain.com [DESCRIPTION] Database backed up. Database: FILESTREAMDATABASE, creation date(time): 2016/05/11(15:33:33), pages dumped: 508656572, first LSN: 658240:192339:223, last LSN: 658242:329203:1, number of dump devices: 8, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'f58f0339-f9f9-4e15-bca9-6b435186c555'}). This is an informational message only. No user action is required.
 
[TYPE] Information [TIME] 2020/03/11 08:19:46 [SOURCE] MSSQLSERVER [COMPUTER] SQLCLIENT.domain.com [DESCRIPTION] BACKUP DATABASE successfully processed 507775912 pages in 505062.863 seconds (7.854 MB/sec).
 

Other databases on the same client are completing with a recorded throughput over 200 MB/s

[TYPE] Information [TIME] 2020/02/29 02:15:57 [SOURCE] MSSQLSERVER [COMPUTER] SQLCLIENT.domain.com [DESCRIPTION] Database backed up. Database: NONFILESTREAMDATABASEErrorLog_DB, creation date(time): 2016/04/15(21:27:03), pages dumped: 28193802, first LSN: 13685:28300:54, last LSN: 13685:28324:1, number of dump devices: 8, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'7ab32b73-25c5-42b0-a907-cfe0f77a463c'}). This is an informational message only. No user action is required.
 
[TYPE] Information [TIME] 2020/02/29 02:15:57 [SOURCE] MSSQLSERVER [COMPUTER] SQLCLIENT.domain.com [DESCRIPTION] BACKUP DATABASE successfully processed 28188306 pages in 890.849 seconds (247.203 MB/sec).
 
[TYPE] Information [TIME] 2020/03/11 08:20:58 [SOURCE] MSSQLSERVER [COMPUTER] SQLCLIENT.domain.com [DESCRIPTION] Database backed up. Database: FILESTREAMDATABASE_Cache, creation date(time): 2019/05/29(14:30:28), pages dumped: 2498730, first LSN: 628:3170324:24, last LSN: 628:3172306:1, number of dump devices: 8, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'979de8fb-db35-42c3-85ac-06d2450ed525'}). This is an informational message only. No user action is required.
 
[TYPE] Information [TIME] 2020/03/11 08:20:58 [SOURCE] MSSQLSERVER [COMPUTER] SQLCLIENT.domain.com [DESCRIPTION] BACKUP DATABASE successfully processed 2495308 pages in 69.105 seconds (282.101 MB/sec).
 

Check CVPerfMgr.log on the MediaAgent (note ths will only update after an attempt has been killed, suspended or completed). We are interested in finding which stat has the highest recorded time. If the issue is related to the FILESTREAM we would expect to see the "CVA Wait to received data from reader" stat will be the largest recorded (almost 100% of the run time of the attemt). For a SQL Server backup this records the time spent waiting for the reader to receive data from SQL Server.

|*2068678*|*Perf*|952012| Head duration (Local): [12,March,20 23:00:28 ~ 13,March,20 23:04:16] 24:03:48 (86628)
|*2068678*|*Perf*|952012| Tail duration (Local): [12,March,20 23:00:28 ~ 13,March,20 23:04:16] 24:03:48 (86628)
|*2068678*|*Perf*|952012| -----------------------------------------------------------------------------------------------------
|*2068678*|*Perf*|952012| Perf-Counter Time(seconds) Size
|*2068678*|*Perf*|952012| -----------------------------------------------------------------------------------------------------
|*2068678*|*Perf*|952012|
|*2068678*|*Perf*|952012| Reader Pipeline Modules
|*2068678*|*Perf*|952012| |_CVA Wait to receive data from reader........ 85274
 

As a final test, run backup outside of Commvault and see whether the same behaviour is observed.

Ensure that Microsoft best practices are followed for SQL Server FILESTREAM performance as per this MSFT article.

Also consider that Antivirus interference can play a major role in the backup performance bottleneck. Ensure that the SQL Server and Commvault exclusions are in place.

Reply