4

We're about to start a project to migrate a large DWH to new physical servers in a new data centre. The current server spec is SQL Server Enterprise 2016 SP2 running on Windows 2012 R2. The new servers will be MSSQL 2019 Enterprise running on Windows 2019.

SAN storage for the current and new servers is an all flash storage array. In the current environment as well as separating data and log files onto different logical drives, different databases (data files only) are also split across different logical drives.

  • Local SSD - TempDb
  • Logical Drive 1 - log files
  • Logical Drive 2 - data files for staging databases
  • Logical Drive 3 - data files for user facing databases
  • Logical Drive 4 - data files for support databases (ReportServer, MDS database)

As part of the server migration I am considering combining all data files onto a single logical drive.

  • Local SSD - TempDb
  • Logical Drive 1 - log files
  • Logical Drive 2 - data files

As well as database file management is there any performance benefits to keep the data files split across different logical drives? Does multiple logical drives give better IO, even though ultimately it's the same physical storage array?

Improve this question

3 Answers 3

Reset to default
11

is there any performance benefits to keep the data files split across different logical drives?

If all the volumes map to the same set of physical disks on the SAN there's normally no difference.

However, if each Volume maps to a different SAN LUN, it's possible for the SAN to allocate storage resources differently to the volumes. For instance, they can be hosted across separate SAN controllers, have different caching policies, be separately monitored, etc, even if the LUNs share the same underlying storage.

If each volume maps to a separate set of physical disks, then splitting them up is very costly, as you can't pool and share IO resources between the volumes. And as most database file IO is background IO, you should normally pool all the IO resources for all database files together to maximize efficiency, sharing, and per-database peak-IO throughput.

So, it depends, and you need to work with your SAN experts to choose, and to appropriately configure the SAN and the server (eg IO Queue Depth).

Improve this answer
0

I think that the writes to the data file are not done "live". Usually, you'll have performance impact when the log files are on a "slower" storage (as SQL need to write the log before it can commit the transaction (*if you haven't configure it to not do it)).

I guess there will not be real benefits from having the data files on different drives.

I guess if it was me, I would put all data files on the same drive and then check the SQL Waits stats or compare with a baseline to check performance. If ever you have issues, you will still have the option to add new drives and move some database files.

If you have issues, you can also check if there won't be other option to "help" like adding RAM (As the data pages are stored in RAM when SQL uses them), tune your biggest IO request so that you don't have to load too much page in RAM (forcing SQL to flush the oldest one more often), check you fill factor, etc..

Improve this answer
-2

My article from 2007 is still very relevant to this question. Yes you get a speed boost when using multiple physical volumes. It applies to SSD as it applies to Harddrives. In fact, splitting a SQL database across physical volumes is faster than using RAID based on my testing. https://www.zdnet.com/article/comprehensive-raid-performance-report/

Improve this answer
1
  • Though not that this only applies to SAN storage if the "drives" it presents are independently provisioned/limited (i.e. the storage controller tries to use different drives for each, or sets IOPS guarantees/limits per presented drive to stop one hogging the resource) rather than being a simpler "effectively just a networked RAID array" arrangement.
    – David Spillett
    Commented Nov 25, 2020 at 10:29

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