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?


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.

  • The question is about multiple files in one filegroup, this link has the info about the stripping: technet.microsoft.com/en-us/library/…. I understand when there will be an advantage when splitting indexes/tables across multiple filegroups, that's not what I'm considering. The sad truth is that I had only a couple thousand $ to set up a server and I'm getting by with a not-that-good RAID controller. – ytoledano Feb 22 '14 at 22:52
  • What RAID controller is it? If it's any kind of hardware RAID, its RAID0 should be equal to or better than SQL Server trying the same thing, unless, maybe, you put each SSD on a different controller. You did use AHCI mode, right? If you have a HW budget and time, work on optimizing your SQL. – Anti-weakpasswords Feb 23 '14 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.