Peformance Gain for different drive for SQL Server data and Log file (VM) ?

[TonyJK]

Hi,

It is suggested to separate the SQL Server 2012 data and Log files in different disk drive for VM.  Is there any performance gain if both of them resided in the same LUN (VMDK Partition) ?  OR Should they (different dives) in different LUN (VMDK Partition) ?

Thanks

[DanielOprea]

Hello,

To get better performance, you must have separated the DB across multiple disks. One DB for each disk. It is recommended that the LUNs to be RDMs

[MKguy · Accepted solution]

The general mantra in the physical world goes as follows:

For best performance, you should separate transaction log and database files on different physical spindles (i.e. disks).

This general concept naturally applies agnostically to virtual workloads as well. It basically translates to:

For best performance, you should put transaction log and database files on different VMDKs residing on different LUNs or use separate RDMs. You should also use a separate vSCSI controller for each VMDK/RDM because each controller has it's own IO queue.

There is one more important point which is often overlooked in the physical and especially virtual world:

Ideally you should make sure that your physical LUNs for logs and DB are not sourced from a common disk group of the same physical spindles. This violates the above general concept.

[rfdz12]

Is this still the case in 2022?

[continuum]

Admins in 2022 appear to be less skilled than admins in 2014 - so you should balance performance and complexity of your setup.

[rfdz12]

See post below

[rfdz12]

The reason I ask is because one person wants to build VMs as one big LUN for all data 40-60TB or so. One LUN for OS and another for LDFs. Vertical build

And another wants to cap each VM at 5TB for Data with a max of 4 Data LUNS if needed.  One OS LUN and LDF LUN. A more horizontal build. 

https://support.purestorage.com/Solutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/001_Microsof...

"From a pure performance perspective, storing all database files and transaction logs on the same volume will not impact performance. However, from a management perspective, it is recommended that:"

[continuum]

60 TB Luns are only ok if your automatic backups are 100% reliable and if you tested a worst case scenario and are satisfied with the time that you are out of business while restoring the backups.
From a recovery point of view 60TB Luns are a complete nightmare.

Ulli

[rfdz12]

To clarify it would be multiple data files of various sizes in those 60TB. Something like 150 MDF's . Can you point me to the papers discussing how to successfully design SQL Server VMs? 

https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-bes...

Page 56 says "When deploying a Tier 1 mission-critical SQL Server, placing SQL Server binary, data,
transaction log, and TempDB files on separate storage devices allows for maximum
flexibility, and can improve performance." 

However, I don't see maximums. I can't find the newest that talks about VVOLS and how to design so I can use vvol volume copies for other lower environments.

I did see this on page 57 "For the most critical databases where performance requirements supersede all
other requirements, maintain 1:1 mapping between VMDKs and LUNs. This will
provide better workload isolation and will prevent any chance for storage
contention on the datastore level. Of course, the underlying physical disk
configuration must accommodate the I/O and latency requirements as well. When
manageability is a concern, group VMDKs and SQL Server files with similar I/O
characteristics on common LUNs while making sure that the underling physical
device can accommodate the aggregated I/O requirements of all the VMDKs."