Sql-server – Configuring Dynamics GP’s (SQL Server) Disks

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:

1. the SAN hosts other arrays and therefore hot spares are available from a pool
2. 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.

Variants of this question come up semi-regularly:

• SQL Server build
• Help me choose a RAID level combination for a SQL Server 2008 instance
• How should I configure these disks on a SQL Server for a BI configuration?

There are also occasional bun fights about the data/log separation "best practice".

• Placing Transaction Log on Separate Volume [solid state]?
• SQL Server - Separating data, log, and TempDB files on a SAN

Without more detailed analysis of what this server is doing, the same advice applies as given previously.

• RAID 1 for OS
• RAID 10 (6 disk) for data/logs/tempdb

There is rarely any point in a split with so few spindles available. A single array with a larger IOPs capacity will typically soak up the lumps and bumps of your workload better than 2 smaller arrays.

One variant that can be worth testing is putting tempdb on the OS drive. Only do so if you have a representative workload that you can replay repeatedly, to ensure a fair comparison of the configuration. If you go for this arrangement in production make sure tempdb growth is restricted so you don't inadvertently consume all free space on the OS drive.

Given that your OS drives are 7200RPM coasters, I'd be surprised if the tempdb on OS drive config bore any benefit.