SQL Server and Windows Server 2012's ReFS File System

So a while back I talked about the ReFS file system which Microsoft was introducing to Windows Server 2012 (back then it was Windows Server 8 beta). If you aren't familiar with ReFS it is a new file system which Microsoft has introduced to replace NTFS for large, high load servers. At first glance it looks like a great replacement for NTFS disks for SQL Servers. The file system includes features like auto-correcting data, torn page detection, very fast page recovery when there is corruption.

So far this all sounds pretty good right?

Sadly not so much. As a part of the ReFS spec there were a few things which were not included that NTFS includes.  As posted on the "Building Windows 8" blog on MSDN in a blog post titled "Building the next generation file system for Windows: ReFS":

[quote]The NTFS features we have chosen to not support in ReFS are: named streams, object IDs, short names, compression, file level encryption (EFS), user data transactions, sparse, hard-links, extended attributes, and quotas.[/quote]

The problem from this list is that SQL Server uses two of those features. Specifically named streams and sparse. Sparse files are used by database snapshots. Now while this is a features which isn't explicitly used by everyone, it is an important feature to have access to. The second feature is the named streams feature of NTFS. SQL Server uses named streams as well as sparse files when running any of the DBCC CHECK statements such as everyone's favorite DBCC CHECKDB when these commands are run online. This is a pretty big problem as people should be running DBCC CHECKDB pretty regularly to ensure that data corruption hasn't happened and that if it has so that the corruption can be resolved as quickly as possible.

What brings this up again is that I'm starting to see people looking into ReFS for SQL Server deployments, even with an EMC Engineer doing a blog post on the performance impact of ReFS on a SQL Server workload. (His post brings up all sorts of questions in my mind about block sizes as his reads and writes are much smaller than they should be).

So if you planned on using ReFS with your SQL Server 2012 deployments, I'd stop looking at it and plan on using NTFS. If you've already deployed using ReFS it's time to work on moving back to NTFS (which is going to take an outage).

Denny

Discuss this Blog Entry 1

on Sep 14, 2014

Hi Denny - if you may please update this blog for SQL 2014. This release does has changes for running dbcc on REFS.

Please or Register to post comments.

What's Troubleshooting SQL Server Storage Problems?

Practical advice, insight, and help for core SQL Server considerations.

Contributors

Denny Cherry

Denny Cherry is the owner and principal consultant for Denny Cherry & Associates Consulting and has over a decade of experience working with platforms such as Microsoft SQL Server, Hyper-V,...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×