Sql-server – the best practice location for Memorize Optimization (OLTP) files

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.

For a 4 bay configuration with a high insert/update workload I would go with one of these:

1) Spindle only

• One RAID1 for the log. Back this up with a a few hundred MB of write cache
• One RAID1 for the data files and tempdb

Rationale: you will mostly be writing to the log. Latency of log is king for OLTP and it doesn't get much better than a RAID1 with a write cache. You can use SATA - which should give you at least 80MB/sec sequential writes at latency less than 1ms.

2) SSD plus spindle

• One RAID1 for log with same configuration as the above.
• One RAID1 for data files and tempdb

Rationale: This config will favour higher tempdb load and would be good if you are skimping on DRAM (as this will cause checkpoint activity to go up and drive read I/O)

3) Pure SSD

• Put them in a RAID5 if you are not worried about wearing them out.
• If you are using consumer grade SSD - put them in a RAID10.

Rationale: RAID5 with 4 x SSD is fast enough for nearly all purposes.

Everything goes on same LUN

4) 8 bay, spindle only config

• Put 2 disks in RAID1 for the log file.
• Rest goes into a RAID10 or RAID5 (RAID10 if you are worried about tempdb)

Overall: always prefer a 2 spindle RAID1 with a write cache for logs. It's plenty for 99% of all cases (80MB/sec for SATA and up to 120MB/sec for SAS).