SQL Server database restore is very slow (FILESTREAM)

Question

I created a backup of the MS SQL Server 2014 database which contains a lot of binary data (FILESTREAM), about 2 million records in the table. The table itself has the following structure:

The database size is around 100GB (70GB of binary FILESTREAM data). When trying to restore the database, everything goes smooth up to 60% point (takes a couple of hours), but then it slows down to a crawl. I left if for a week and it didn't move from that 65% percent mark.

A couple of things that I noticed:

• The usage of hard disk decreases. At the beginning in the resource monitor I see simultaneous access to several files (more than 20) in the DATA folder and the disk is busy 100%. After that 65% mark I see only 1-2 files being accessed by the SQL server process. The machine overall seems not busy at all.
• It looks like SQL Server is doing something but it doest it VEEERYY slowly. I suspect that the problem may be in the number of the STREAM_DATA rows.

How can I speed up the restore process?

Answer 1

We have same issue with large filestream. How we solve it:

1) Disable "Windows Search" (and indexing on drive where is your database located)

2) Disable "Last access time" updates for all files:

Key Name: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem Name: NtfsDisableLastAccessUpdate Type: REG_DWORD Value: 1

3) Disable 8.3 file names generation:

Key Name: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem Name: NtfsDisable8dot3NameCreation Type: REG_DWORD Value: 1

For details, see this article