RAID0 vs multiple files in file group

Question

I have a server that gets updated and backed up once a day and processes queries the rest of the day (selects). I'm trying to improve performance of the update process and the selects. I'm not worried at all about redundancy since the database is backed up after it's updated.

I'm currently using RAID0 with 2 SSDs but I think that the controller is limiting the read and write performance. I read that when using multiple files in a single file group then data is written in a stripped way to all files. Should I move from RAID0 to 2 single disks where the data is stored in one file in each disk?

Answer 1

RAID0 vs multiple files in file group RAID0, definitely, with SSD's. There MAY be an edge case exception if you know you're getting hung up on two particular indexes (clustered or not) in a JOIN, where you could gain performance by having two FILEGROUPs, one on each disk, and splitting those indexes between the filegroups, but I really, really doubt it, and you'd only find that out by benchmarking it.

As far as not caring about redundancy, I think you're crazy. Note that a modern Dell PERC (LSI Logic) controller can get >1GB/s sequential data transfer off of a set of 6 SATA SSD's in RAID5, and the random rates are also in the hundreds of MB/s if you keep your IO depth deep enough. Note also there's a huge different between high-end SSD's and low-end SSD's, particularly for random writes (i.e. probably your update process).

I'm trying to improve performance of the update process and the selects.

This is a general performance tuning question, so all the normal performance tuning advice applies. Run Profiler on your selects during the day, look at reads, writes, duration, CPU. Use the SSMS-based Standard Report for Top Queries by Total CPU and Total IO during the day. Tune your queries and data structure, starting with the highest impact and working your way down.

Do the same thing with the update, but separately.

If you really think IO is really the issue, look at IOStall in sys.dm_io_virtual_file_stats or use Perfmon (Avg sec/Read and Avg sec/Write).

As a general shotgun approach, if during the day it's 100% read only (selects), you can ALTER DATABASE x SET READ_ONLY to reduce possible locking. Set it to READ_WRITE before the update, and back to READ_ONLY afterwards.

As part of your update, are you rebuilding indexes (which also updates their statistics with FULLSCAN as part of the rebuild)? Or making sure stats are up to date?

If you're really having IO issues on pure reads (selects), and you're unwilling or unable to tune your queries and schema, then there's always buying more RAM or increasing the maximum amount of RAM allocated to this instance of SQL Server.