SQL Server on SAN, same LUN: one logical drive vs multiple

Question

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.

Answer 1

In your question you say "one LUN assigned to a SQL Server VM" - what you want is one LUN assigned to the hypervisor machine that can have multiple virtual machine disks 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, specifically, 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).