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.
It's a simple setting for sp_configure (that should work for all newer versions of SQL Server - 2005, 2008, 2008 R2):
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
This will enable the use of OPENROWSET and OPENDATASOURCE.
Best Answer
There is an advantage to having separate SQL Server instances however do those advantages outweigh the cons? You'll have to look at how you would use those instances and decide for yourself.
Advantages
Allocate/limit resources that each instance has access to, specifically you can limit memory and CPU on the Dev instance so that the Live instance will not be 'overly' affected while you make mistakes in Dev...
Data separation: by having separate instances if you want to share data between Live and Dev you would need to explicitly set up a Linked Server, if they aren't separated you can just refer to the live db (assuming you've given the same user permission)
Ease of development: you can have db's named the same thing on difference instances, ie: dev can be a structural replica of Live (which seriously reduces the development complexities)
Disadvantages
Note: The benefits of a separate instance would probably be better if you instead created a separate VM for your Dev DB environment. This would have the added benefit that when/if you can get another physical server you can move your VM without having to change any connection settings...