19

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.

Improve this question

4 Answers 4

Reset to default
18

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:

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.

Improve this answer
1
  • 1
    Yep, I've been given the wrong information somewhere along the line but like I commented on Stuart's answer, I asked the question to make sure I wasn't basing my decisions on incorrect information. Thanks for the links, I'll check them out.
    – Nathan Ridley
    Feb 27, 2012 at 23:39
17

My first suggestion would be to not make any assumptions about performance without doing load testing against both configurations.

My guess from having seen such configurations (that make sense on paper) in the past would be that having each table on a separate file wouldn't have a measurable positive impact for performance... and that the additional complexity would offset any performance gains even if they were measurable.

Lastly, when it comes to squeezing every drop of performance out of a Sql Server, I refer you to the following chart (provided my Microsoft):

enter image description here

Any potential optimizations that could be made from an application perspective easily dwarf any possible optimizations at a hardware / database configuration level... so focus your attention appropriately.

Improve this answer
2
  • Of course. In my case though, I've been optimizing the entire system as much as I can and the primary bottleneck I have right now is very fast query speeds in the face of frequent updates, deletions and insertions. As I'm going to leverage SQL Server to solve this problem, I want to make sure I give it the absolute best chance possible to operate as fast as possible on my data.
    – Nathan Ridley
    Feb 27, 2012 at 21:37
  • @NathanRidley Ok, understood... I think the real answer unless someone has a resource saying "never do this", that the best course of action would be to compare two configurations against your typical workload, and see if there's a measurable difference.
    – Michael Fredrickson
    Feb 27, 2012 at 21:42
4

As others have noted, there is no direct benefit from one file per table; here's a great synopsis from Steve Jones on how this myth originated: http://www.sqlservercentral.com/blogs/steve_jones/2009/10/13/sql-server-legend-data-files-and-threads/

You may also want to investigate a partitioned view which I believe is supported by 2008 Web Edition. There are some tricks to coding against a partitioned view, but you can mimic a lot of the functionality of partitioned tables relatively easily.

Improve this answer
0
2

I think separate files for each table would bring no performance benefit. The correct indexes could have a potential performance (disk read) incrase on the database server.

Is the SQL Server 2008 R2 supports compression? If yes, turn that on.

Correct me if i'm wrong.

Improve this answer
4
  • Could you elaborate on why there would be no performance benefit? At the very least, explain why this is the case when separate files allow SQL Server to use multiple threads for reading.
    – Nathan Ridley
    Feb 27, 2012 at 21:32
  • If you put all table on it's own filegroup but on the same drive the performance will be equal before the partitioning. But if you are separeting some tables to their filegroups on a different faster disk it will have performance benefit. You can also partitionate for example by year if you have a lot of data which are depends on the year. With this techniqu you can keep your most used data on a faster disk than the old ones. You can separate indexes as well but only if you put them into a new physical disk will have any performance benefit.
    – Peter Kiss
    Feb 27, 2012 at 21:43
  • Your are right about the parallel threads (tables/files) but i think until you have only one physical disk the performance gain will be small.
    – Peter Kiss
    Feb 27, 2012 at 21:46
  • And i recommend you to get a stronge HDD RAID array for the database becouse the SSD will die soon.
    – Peter Kiss
    Feb 27, 2012 at 21:55

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.