What I would do is to benchmark the performance before and after the changes. There should be a performance gain after tempdb is moved to another drive. Use DMVs like sys.dm_io_virtual_file_stats to see the read and write wait times for the DB files.
Use the perfmon physical disk counters: Avg. Disk sec/Read, Avg. Disk sec/Write, Disk Reads/sec, Disk Writes/sec. The change in these metrics will let you know if the disks are physically separate or are just the same disks which are logically separated.
Want a third party disk benchmarking tool for your disks, so that the SAN admins cannot accuse SQL server of its results? Use SQLIO. Although there is SQL in the name, this tool does not require SQL server to run and is not related to SQL server.
This is a good question, and I look forward to see the other answers.
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.
Best Answer
To expand on Aaron's comment of:
Each SAN implements this differently. For example, some can define exact RAID groups, type, etc. Some you just put disks in and they create their own internal sets and configuration - you just tell it how big of a LUN you want and where to zone it. Some SANs will automatically tier your data and you may be on many different sets of drives. Others will yet again operate completely in cache and only flush to disk like a checkpoint operation would in SQL Server.
This also doesn't take into account anything in the fabric that may be acting as yet another intermediate tier such as SVCs.
Really, the question you should be asking is, "Is one LUN allowing me the throughput I require in order to properly run this instance of SQL Server and all of the associated databases."
If there is only one LUN, most drivers for the HBAs won't multipath. This may or may not matter depending on your fabric - but most of the time it helps. Additionally we don't know the queue depth of the HBAs (though not all implementations have such a configurable item) and don't know the typical profile of IO for that server.
Just some extra items to think about :)