TonyJK
Enthusiast
Enthusiast

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

Jump to solution

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

0 Kudos
1 Solution

Accepted Solutions
MKguy
Virtuoso
Virtuoso

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.

-- http://alpacapowered.wordpress.com

View solution in original post

0 Kudos
8 Replies
DanielOprea
Hot Shot
Hot Shot

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

PLEASE CONSIDER AWARDING any HELPFUL or CORRECT answer. Thanks!! Por favor CONSIDERA PREMIAR cualquier respuesta ÚTIL o CORRECTA. ¡¡Muchas gracias!! Blogs: http://danieloprea.blogspot.com.es/ https://communities.vmware.com/blogs/doprea
MKguy
Virtuoso
Virtuoso

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.

-- http://alpacapowered.wordpress.com
0 Kudos
rfdz12
Contributor
Contributor

Is this still the case in 2022?

0 Kudos
continuum
Immortal
Immortal

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


________________________________________________
Do you need support with a VMFS recovery problem ? - send a message via skype "sanbarrow"
I do not support Workstation 16 at this time ...

rfdz12
Contributor
Contributor

See post below

0 Kudos
rfdz12
Contributor
Contributor

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:"

0 Kudos
continuum
Immortal
Immortal

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


________________________________________________
Do you need support with a VMFS recovery problem ? - send a message via skype "sanbarrow"
I do not support Workstation 16 at this time ...

0 Kudos
rfdz12
Contributor
Contributor

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

 

0 Kudos