SQL Server database on an SSD - any advantage to a separate file for every table?

I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key column in order to maximise query efficiency in the face of heavy updates and insertions and make heavy use of clustered indexes. Two of the tables will contain variable-length textual data, with one of them containing hundreds of millions of rows but the rest will contain only numeric data.

As I really want to squeeze every last drop of performance out of the hardware I have available (about 64GB of RAM, a very fast SSD and 16 cores), I was thinking of allowing each table to have its own file so that no matter if I'm joining on 2, 3, 4, 5 or more tables, each table will always be read using a separate thread and the structure of each file will be closely aligned with the table contents, which would hopefully minimise fragmentation and make it faster for SQL Server to add to the contents of any given table.

One caveat, I'm stuck on SQL Server 2008 R2 Web Edition. Which means I can't use automatic horizontal partitioning, which rules that out as a performance enhancement.

Will using one file per table actually maximise performance, or am I overlooking built-in SQL Server engine characteristics that would make doing so redundant?

Second, if using one file per table is advantageous, why does create table only give me the option to allocate the table to a file group and not to a specific logical file? This would require me to create a separate file group for every file in my scenario, which suggests to me that perhaps SQL Server isn't envisioning the advantages I am assuming would come from doing what I'm proposing.

I was thinking of allowing each table to have its own file so that no
matter if I'm joining on 2, 3, 4, 5 or more tables, each table will
always be read using a separate thread and the structure of each file
will be closely aligned with the table contents, which would hopefully
minimise fragmentation and make it faster for SQL Server to add to the
contents of any given table

What the heck are you talking about? Not sure where you got your information from, but you should certainly discard that source. Nothing from what you assume here is actually correct.

If you want to read a good discussion of SSD performance for SQL Server there are several blog series out there. As usually, Paul Randal's one is the top read:

• Benchmarking: Introducing SSDs (Part 1b: not overloaded log file array)

• Benchmarking: Introducing SSDs (Part 1: not overloaded log file array)

• Benchmarking: Introducing SSDs (Part 2: sequential inserts)

• Benchmarking: Introducing SSDs (Part 3: random inserts with wait stats details)

• Benchmarking: Multiple data files on SSDs (plus the latest Fusion-io driver)

Brent also has a nice presentation on the topic: SQL on SSDs: Hot and Crazy Love and there are more out there.

Going through all these presentations you will quickly notice that they all focus on writes since this is where SSDs performance comes into picture. Your post wording is almost entirely about reads, which is a different topic. If reads are your pain point then you should be talking about RAM, not about SSDs, and about proper indexing and querying strategies.

StackExchange Database Administrators Q#14047, answer score: 18