Sql-server – SQL Server – Benefits of splitting databases across different logical drives

Architecturesql serverstoragewindows-server

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?

Best Answer

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).

Related Question