principlesqlMinor
ReFS Vs NTFS for SQL Server 2019, server 2019
Viewed 0 times
refssql2019forserverntfs
Problem
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.
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.
Solution
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).
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.
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.
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
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.
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).
- 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.
- 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.
- 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.
Context
StackExchange Database Administrators Q#334819, answer score: 8
Revisions (0)
No revisions yet.