I'm going to build a Microsoft SQL server database which could end up being more than 40 TB in size. There are some quite large tables in the DB with more about 1010 .. 11 records. Every table has typical primary key index. Also most of the tables have composite non-clustered indexes too (B-tree). In a few cases we have defined columnstore indexes.

Which of the below physical designs of the server will result in better performance1 and db management2 overtime?

Scenario 1. create one (or more) filegroup per physical drive (we only have SSD NVMe drives), and spread the heavily used tables and indexes across different drives.

Scenario 2. Bundle all drives using RAID 10 and create a single large volume. Data will be striped on all disks by the OS. In this scenario, data file (.mdf), log file (.ldf) and tempdb are all stored on a large RAID 10 volume.

Scenario 3. Partition tables/indexes and store each partition on different physical drive. I should mention that it wold be close to impossible to distribute data uniformly across all partitions because of the type of data we are dealing with.

If you think none of the above are ideal solutions; I'd love to know what would you recommend.

Many Thanks.

[1] by performance I mean lower retrieval time and higher IOPs, for a given indexing architecture

[2] by db management I mean add/remove/update data, keeping the performance, redundancy, availability ..

1 Answer 1


I believe all 3 options are very much correlated and can be implemented together: Depending upon the table structure and such a size of database and tables you can have them on individual file groups especially if they are partioned.

Also it's always good to have them on good storage. We have in our scenario most of the data and log files of user dbs and temp dbs on SSD's

There are lot of factors if you are considering performance. I believe you should be on sql server 2016 + to make use of all new improved features like CE's.

Also consider other various factors like fibre cables that SAN is being used followed by good network bandwidth. All I can say per my experience is test the system or hardware with all sorts of load testing and see how well it suites your requirements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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