SQL Server Database File Layout: Separating Data and Log Files When Using Flash Arrays

For years, I’ve recommended data and log files reside on separate drives for performance reasons. But given today’s flash arrays, virtualization, and much of the storage subsystem being abstracted, is separating data and log files still important?

Short answer: yes, it should be, but don’t stop there.

Long answer: it depends. You should review multiple aspects when building a new server to host SQL Server.

Let’s take a look.

Why did we separate data and log files in the first place?

If you’ve worked with SQL Server for a while, you know that the general guidance has been to separate the data files from the log files. Both data and log files are extensively used by SQL Server, but they have different uses and characteristics.

Placing the data files and log files on the same device can cause contention for that device. This can cause a degradation in performance as I/O activity is channeled through one device.

By placing the data and log files on separate drives, I/O activity is spread across different devices, thus reducing contention.

And that’s been the way we’ve always done it.

But then, along came SSDs and the improved performance they offer.

Advantages of SSDs Over HDDs

There are multiple advantages of SSDs over HDDs.

• No Physical Movement: SSDs have no moving parts and no seek time. Seek time is the time it takes for the disk head to move to the correct part of the disk. This reduces access times significantly compared to HDD. If you’re curious, check out Hard Drive Seek Time: What It Means (And Why It Matters) – Datarecovery.com for more information regarding seek time.
• High Throughput: SSDs can handle a much higher volume of I/O operations per second (IOPS).
• Better Handling of Random Access: Unlike HDDs, SSDs do not experience performance degradation with random I/O operations, which are common for SQL Server environments. 
• Durability: Without moving parts, SSDs are less prone to physical wear and tear, making them more reliable over time. SSDs are overprovisioned as well. Overprovisioning is a technique to allocate a portion of the storage space for internal use to improve performance and endurance.

Should you still use separate drives on SSDs?

It depends. When building a new server to host SQL Server, consider the following in your design:

• Storage Vendor: Most vendors provide reference architectures. Here are a few well-known vendors:
  • Pure Storage – Design Guide for SQL Server and VMware on Pure Storage
  • Nutanix – Microsoft SQL Server on Nutanix
  • Netapp – Microsoft SQL Server on ONTAP (netapp.com)
• Virtualization: Hypervisors introduce their own set of best practices/considerations for SQL Server.
  • VMware – Architecting Microsoft SQL Server on VMware vSphere | VMware
  • Hyper-V – Unfortunately I couldn’t find any specific documentation from Microsoft for SQL Server running on Hyper-V.
• Controller settings
  • Queue depth
  • Cache
  • Interface (Iscsi, fiber)

Work with your storage and virtualization (if applicable) teams to design a solution that works best for your specific hardware stack.

What About Azure Virtual Machines?

Yes, separating data and log files is still recommended here. See Checklist: Best practices & guidelines – SQL Server on Azure VMs | Microsoft Learn.

What About Amazon AWX EC2 Instances?

Amazon states:

Use one volume for data and log files, another volume for tempdb workloads, and Cold HDD (sc1) or Throughput Optimized HDD (st1) volumes for backups. If you run into an I/O-related issue and want to separate workloads for data and log files, consider using different volumes. If your workload requires you to separate specific databases, consider using a dedicated volume for each database. Typically, tempdb is the target of the highest I/O, so if that workload isn’t separated, it might become a bottleneck. This separation also helps isolate tempdb from the data and log files of the user database. You can use comparatively lower-cost storage for backups to optimize your costs.

Optimize your disk layout or file distribution – AWS Prescriptive Guidance (amazon.com)

Conclusion

Getting storage right for SQL Server can be complex, especially when you’re working with multiple layers. It’s important to consider each stack, review the “best practices” provided by each vendor, and then proceed with designing the correct solution for your specific workload.

Want to work with The SERO Group?

If you’re concerned about the performance of you SQL Server, and wonder if database file layout may be one of the culprits, we’re happy to help. Contact us to schedule a discovery call.