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.
1: yes.
2: depends;) The query execution is still driven by the optimizer. It wont do paralellization for small result sets.
3: Ah - what would that be good given that you are still reading one backup file ;)?
4: Not running the files on one underlying SAN and thus improoving your IO budget?
The most brutal thing I have ever seen like that had nearly 30 files all on separate SAN volumes (count of hard discs going close to 200) and it was done as every LUN had a queue limit in the driver of 255 outstanding requests, which the SAN (with a 32 gigabyte cache) was just not caring about ;) THat thing was pulling in nearly 1.5 gigabyte per second over multiple fiber connections.
Best Answer
I am not a SAN admin, but I have a very nice one in my team that takes the time to do his job proprely, and share his knowledge with us when we ask.
The way our SAN is configured makes it so there is a very large amount of cache between the SQL servers and the physical disks. In this situation, having multiple filegroups, or even splitting the DATA and LOG files on different drive letters is a moot point, as it all ends up in the same cache cluster and points to the same large array of 15000RPM disks. The cache is intelligent enough to keep the relevant stuff cached, and write to disk in a "Lazy Writer" method.
That said, althou we handle a very large amount of data, we are not overly heavy in throuput. This setup is lightning fast for our needs, and very safe between the usual backups and SAN automatic redundancy to another server room.
Sit down with your admin, tell him what your needs are, and balance the final setup with his knowledge and architecture. SAN architecture can vary greatly, so some of the "best practices" of SQL server installation may not even apply, like it did for us.