Can Windows 2012 Server ReFS benefit the SQL OLTP workload?

Author: Tony Wu @EMC

July 2012 Shanghai

In Windows Server 2012 CTP, a new file system for Windows, which we call ReFS, has been designed from the ground up to meet a broad set of customer requirements.

The key goals of ReFS are:

  • Maintain a high degree of compatibility with a subset of NTFS features that are widely adopted while deprecating others that provide limited value at the cost of system complexity and footprint.
  • Verify and auto-correct data. Data can get corrupted due to a number of reasons and therefore must be verified and, when possible, corrected automatically. Metadata must not be written in place to avoid the possibility of “torn writes,” which we will talk about in more detail below.
  • Optimize for extreme scale. Use scalable structures for everything. Don’t assume that disk-checking algorithms, in particular, can scale to the size of the entire file system.
  • Never take the file system offline. Assume that in the event of corruptions, it is advantageous to isolate the fault while allowing access to the rest of the volume. This is done while salvaging the maximum amount of data possible, all done live.
  • Provide a full end-to-end resiliency architecture when used in conjunction with the Storage Spaces feature, which was co-designed and built in conjunction with ReFS.

  The key features of ReFS are as follows (note that some of these features are provided in conjunction with Storage Spaces). 

  • Metadata integrity with checksums
  • Integrity streams providing optional user data integrity
  • Allocate on write transactional model for robust disk updates (also known as copy on write)
  • Large volume, file and directory sizes
  • Storage pooling and virtualization makes file system creation and management easy
  • Data striping for performance (bandwidth can be managed) and redundancy for fault tolerance
  • Disk scrubbing for protection against latent disk errors
  • Resiliency to corruptions with "salvage" for maximum volume availability in all cases
  • Shared storage pools across machines for additional failure tolerance and load balancing


The following test is to evaluate the performance of REFS for 100 GB TPC-E like application.  The storage pool is using EMC VNX 5300 with NL SAS. Each test scenario restarted SQL Server 2012 service to make a fresh running test.

The environment profile is as follows.

VMware and Virtual Machine Profile

VMware vSphere 5.0 update 1 Version: 5.0.0 623860

VM number: 1

Configuration:  4 vCPU on VMware vSphere 5.0 with update1 with 16GB RAM configure

Virtual disk: eager thick zeroed

Application Profile

Application configuration: 100GB TPC-E database with SQL Server 2012 RTM

Configuration:   VNX 5300: NL SAS pool (RAID 5 8+1)

Using NTFS 64k format unit

This configuration uses 1 x 300GB virtual disk

KPI: (sampling after 2 hours and running for around 1 hour before stopping test)

tpsE: 66.95,         TPC-E transaction/sec:  760

data lun average IOPS: 3376        data lun Peak IOPS: 8639

  1. Avg. Read latency: 5ms Avg. Write latency: 4ms                 Avg.disk latency 7ms

SQL transaction in total (tpce+tempdb): 2500

Average CPU utilization: 55.4%

  1. Avg. Read size: 8k            Avg. Write size: 13k        Avg IO size: 13k

Read:Write: 5.5:1

Using ReFS default format unit

This configuration uses 1 x 300GB virtual disk and then format with ReFS format with 64K element.

KPI: (sampling after 2 hours and running for around 1 hour before stopping test)

tpsE: 67.9,           TPC-E transaction/sec:  680

data lun average IOPS:3368         data lun Peak IOPS: 5882

  1. Avg. Read latency: 5ms Avg. Write latency: 3ms                 Avg.disk latency: 6ms

SQL transaction in total (tpce+tempdb): 2504

Average CPU utilization: 59.1%

  1. Avg. Read size: 8k            Avg. Write size: 13k        Avg IO size: 12k

Read:Write: 5.5:1




The interesting different is the peak IOPS of the data LUN.  When I used NTFS with 64k format unit, the peak IOPS can be very high but on ReFS it was only 5882.  The maximal IOPS occurred on the DB checkpoint that means the ReFS could have problem when handle large burst IO.


Using ReFS on Mirrored & Storage pooled disk

This configuration used 2 x 150GB virtual disk to form 1 mirrored disk with 150GB size. And then format with ReFS format.

KPI: (sampling after 2 hours and running for around 1 hour before stopping test)

tpsE: 3                   TPC-E transaction/sec:  31

data lun average IOPS:307           data lun Peak IOPS: 424

  1. Avg. Read latency: 8ms Avg. Write latency: 2ms                 Avg.disk latency: 7ms

SQL transaction in total (tpce+tempdb): 118

Average CPU utilization: 4.8%

  1. Avg. Read size: 29k         Avg. Write size: 45k        Avg IO size: 32k

Read:Write: 3.5:1



Comparing with NTFS and ReFS on single non-pooled disk, this configuration has more tradeoff.  The IOPS and transaction per second of SQL Server are decreased greatly.  If you check the KPI you may find the IO size increased from average 12kb to 32kb, with 166% increment.   This is tradeoff impacted the IO and application performance greatly: the IOPS dropped from 3368 to 307, with around 90% dropping off.


You can imagine this increment of IO size will change the storage behavior also.



Using ReFS on Parity & Storage pooled disk (4+1 disks)

This configuration used 4 x 40GB virtual disk to form 1 parity disk with 160GB size. And then format with ReFS default format.

KPI: (sampling after 2 hours and running for around 1 hour before stopping test)

tpsE: 19.53                          TPC-E transaction/sec:  192

data lun average IOPS:1226         data lun Peak IOPS: 3451

  1. Avg. Read latency: 8ms Avg. Write latency: 2665ms          Avg.disk latency: 1729ms

SQL transaction in total (tpce+tempdb): 841

Average CPU utilization: 21.5%

  1. Avg. Read size: 9k            Avg. Write size: 36k        Avg IO size: 36k

Read:Write: 8.5:1.5




This configuration has more performance downgrade; especially the latency of the data lun is completely unacceptable for DBA and application.  The major impact is the latency. Although the write ratio is not higher than the read ratio, the write latency is unacceptable- it has more than 2 seconds.

But the IOPS and transaction per second was not so bad.



If you want to use ReFS, you need take care of this because you need know the performance of the data on the ReFS.   The SQL Server OLTP performance on the raw ReFS may only have peak IO tradeoff, based on the test.  However when creating mirrored disk on the raw disks and the read IO size will be major performance impact factor,  because the typical IO size index seek, which is 8kb, changed to 29kb, this will impact the transaction per second.


While using parity of the ReFS disk format, the Read IO size could be kept at around 9kb, but the parity calculation obviously increase the disk latency.   And this impact is unacceptable.  


Just as the article ( said the ReFS has been a product already so we would not expect any performance improvement after the Windows 2012 RTM released.