5

First, let me start by saying that I did notice there are multiple similar questions to this, but neither of them is exactly what I want to ask, and not one of them has a definite answer.

Second, let me confirm that I do understand, that it's recommended to use different LUNs/spindles for log/data even on SAN/virtualized environment.

Now the question:

IF there is just one single LUN assigned to a SQL Server virtual machine, is there a performance (NOT management, security, or any other) difference between following configurations:

  1. One virtual disk file (vhd, vmdk, whatever) with one virtual controller with one logical partition in virtual OS, and tempdb/data/log in that partition
  2. One virtual disk file with one virtual controller but multiple logical partitions/drive letters for tempdb/data/log
  3. Multiple virtual disk files one separate virtual controller with separate partitions for tempdb/data/log

So far I have heard the following answers:

  1. There is no or negligible performance difference, as in the end, its still just same spindle with same IO capacity
  2. There is a performance difference in some scenarios because virtual controllers have separate (virtual) IO queues and scheduling in the guest OS

If important, let's assume the workload is very large number of threads with very small requests (so deep small queue).

I would like to get this settled once and for all, so I would like to ask to stay on topic of performance on one LUN, and refrain from suggestions for optimizing the layout.

5

In your question you say "one LUN assigned to a SQL Server VM" - what you want is one LUN assigned to the host VMware machine that can have multiple VMDKs for the SQL Server stored on it. This allows the VM to use a much larger number of threads to access the disk.

In order to get the best queue depth out of VMware you'll need to use as many separate virtual controllers, and drives as possible. Make sure you use the para-virtualized controller, if at all possible. Refer to this blog from VMware.

I'd put tempdb on 2 controllers (with 4 files, spread over the two controllers/LUNs).

I'd put data on a controller, and tempdb on a controller.

Virtual controllers have their own queues (just like hardware); typically at most 32. Pretty clearly, 32 is not a lot, so having more controllers actually increases the effective queue depth.

Clearly, if you're going after performance, there are also a lot of other settings, such as CPU and Memory reservations.

The standard warning applies here; do what you have tested as being the best solution for your system. Since we have no idea about your actual load, I/O requirements, etc, etc, it's pretty hard for us to give you a hard-and-fast rule to follow for every installation.


VMware whitepaper on pushing I/O performance is here (warning, PDF).

  • Just to clarify: so far this is a hypothetical scenario. I'm interested in a generic answer, not limited to just one virtualization vendor. I will read the links. – mrQQ Mar 21 '16 at 21:19
  • sorry about my assumption! - I see virtualization, and I automatically assume VMware since their tools are the defacto standard on PC hardware. – Max Vernon Mar 21 '16 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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