Sql-server – SQL Server on SAN, same LUN: one logical drive vs multiple

performancesansql serverstoragevirtualisation

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:

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
One virtual disk file with one virtual controller but multiple logical partitions/drive letters for tempdb/data/log
Multiple virtual disk files one separate virtual controller with separate partitions for tempdb/data/log

So far I have heard the following answers:

There is no or negligible performance difference, as in the end, its still just same spindle with same IO capacity
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.

Best Answer

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

Related Solutions

Sql-server – SQL Disk Setup Advice – TempDB, Log DB, Data file placement question

Unfortunately this is a big ol' "it depends". Complicated further by your having so many applications, probably with wildly different IO profiles, sharing the same instance/server and arrays.

Usually you would want to isolate data and log files as the IO profiles are polar opposite. Typically weighted toward random read for data files and sequential write for logs. The log element is somewhat different in your case as you have multiple log files.

I'd start by getting an understanding of the IO the databases are consuming relative to each other.

    SELECT
        DB_NAME(fs.database_id) AS [Database Name]
      , mf.physical_name
      , io_stall_read_ms
      , num_of_reads
      , CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
      , io_stall_write_ms
      , num_of_writes
      , CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
      , io_stall_read_ms + io_stall_write_ms AS [io_stalls]
      , num_of_reads + num_of_writes AS [total_io]
      , CAST((io_stall_read_ms + io_stall_write_ms) 
        / (1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]
    FROM
        sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
    INNER JOIN sys.master_files AS mf
    ON  fs.database_id = mf.database_id
        AND fs.[file_id] = mf.[file_id]
    ORDER BY
            DatabaseName ASC, mf.physical_name
--  num_of_reads DESC
--  num_of_writes DESC  
    OPTION (RECOMPILE) ;

If you can format the output of this into a readable form and add to your question, someone can make a more educated guess at the best use of your 18 disks.

If I was doing this blind, I'd probably start off allocating:

6 RAID10 for the busiest database's data files
6 RAID10 for the other database's data files
4 RAID10 shared Logs
2 RAID1 Tempdb (your single disk configuration is a risky single point of failure)

Assuming that:

the SAN hosts other arrays and therefore hot spares are available from a pool
snapshot isolation isn't in use or isn't placing heavy demands on tempdb

Edit: Following update to question regarding queue lengths:

Queue length counters are oft misunderstood when evaluating SQL Server performance. One of the best analogies I've come across is from a Simon Sabin blog post, Disk Queue Length - a bit like buying Guiness.

In your case, they are so low that you might get by with half the spindles. Would still be interesting to see the other stats from the above query though.

Sql-server – SQL Server – Separating data, log, and TempDB files on a SAN

One thing to consider is that the log files are sequential writes where as the data files are non sequential. That is one of the reasons for separate LUNs. Log files write faster if they are on their own LUN because the spindles don't have to skip around, just write sequential. If you add in a data file then the spindles have to skip around and you lose some performance. I'm hoping I got the right terminology there as I'm not all that familiar with SANs themselves. The idea behind it should be however.

Frequently vendor recommendations are wrong when it comes to SQL Server. Just because SQL Server has different needs than most applications that use a SAN.

Best Answer

Related Solutions

Sql-server – SQL Disk Setup Advice – TempDB, Log DB, Data file placement question

Sql-server – SQL Server – Separating data, log, and TempDB files on a SAN

Related Question