7

Recently we've been running into errors when snapshotting DBs (operation error 665)

One of the solutions of this error is to change the underlying disk type to ReFS. However I can't find much out there in terms of people running a SQL Server production workload on ReFS over NTFS.

I understand that for SQL Server 2012 and earlier versions - it uses file streams instead of sparse files to create CHECKDB snapshots. Which ReFS doesn't support, so that on SQL Server 2012 files in ReFS might result in errors in check DB.

I'd genuinely love to know if anyone out there is running their production workloads on ReFS and if there's any gotchas that we need to consider.

3 Answers 3

8

Recently we've been running into errors when snapshotting DBs (operation error 665) One of the solutions of this error is to change the underlying disk type to ReFS.

Yes, NTFS has a limited amount of space for attribute metadata. Hitting that limit will cause a 665 to occur (The requested operation could not be completed due to a file system limitation). One thing if you didn't want to move off of NTFS would be to format the volume with a large file record segment (FRS) which can prolong the lifespan but ultimately will end up with the same fate. It's really not an if but a when question.

I'd genuinely love to know if anyone out there is running their production workloads on ReFS and if there's any gotchas that we need to consider.

There are really two main gotchas (outside of 3rd party support, for example if you have some type of disk backup software it might not know ReFS).

  1. ReFS doesn't do in-place metadata changes

This doesn't seem like a big deal, however if the metadata changes frequently, each of those will be a write amplification so to speak, as in it'll need to pick up the metadata, make the change, and write it in a new location. This can be expensive if it happens often. Think of a log file expanding by 64 MB at a time, hundreds of times.

  1. ReFS stores data differently

ReFS uses a different internal mechanism (B+ Tree) to store data and as such I've noticed that it isn't quite as performant as NTFS (which uses virtual cluster to physical cluster mapping). This isn't to say it's bad just that it's different. In my testing, NTFS is always able to push more IOPs thus reducing the overall amount of time, given the same workload. When looking at driver performance, NTFS used less overall cpu than ReFS for the same workload.

Additionally, while not turned on by default, make sure administrators don't turn on integrity streams as this will hugely tank performance for SQL and SQL already includes it own integrity checks for pages.

  1. Bonus: Administration

Since ReFS is different, you can't use the typical tools with it, such as chkdsk (although the need for chkdsk doesn't really exist for ReFS). This means there are different utilities and items that need to be known or explored. If this is not common in the environment, there may be some investment needed for administrators into education of the administrative efforts, in this case the main tool is going to be refsutil.

Overall I have no issues using ReFS if you go into it knowing that it's going to have a slightly higher burden on the cpu for the driver and you're not going to gain any performance but actually lose a little in many cases (but not all!).

Last item, there are only two options for allocation unit sizes in ReFS, 4k and 64k, which should be checked against your disk devices to see which would be best for use.

0
2

When you first create the Disk and are at the format stage you can increase the metadata size for the File Record for the drive by using the following format command on NTFS systems.

format <your_drive>: /A:64K /L:enable /Q

The metadata cannot be changed after the initial format.


References:

1

Just so you know, I've run into the same problem. (See SQL Server should I format with ReFS to get rid of operating system error 665?)

But in the end:

  • I kept the NTFS file system
  • I split the large tables on different file groups - thus creating smaller files
  • Changed the database maintenance (see my question for the details)

With that in place, I was able to keep from having the error 665 for a while now. (Haven't got the error since the question was posted more than 2 years ago.)

Your Answer

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

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