How should I distribute my tables for better performance from fragmentation point of view

Question

I have a database on SQL Server 2008 R2 with about 20 non-indexed tables that are continuously undergoing INSERTs AND DELETEs. There are about 400,000 records @ 4KB/record being inserted or deleted each time.

This much action on the tables will undoubtedly cause fragmentation.

Along with the previous tables, I have about other 20 tables that hardly change inside the same filegroup. They are historic tables and grow month after month by approximately 400,000 records @ 4K/record per month.

Even if I make the filegroup files big enough to hold the data and reduce physical fragmentation by disabling auto growth, the internal fragmentation will eventually get out of control, causing performance to drop.

I am not talking about physical fragmentation of the database files either, nor vertical/horizontal fragmentation of tables.

Should I make one filegroup for the small change tables and one for the high change tables or should I distribute the tables evenly? For example; 10 high change tables with 10 small change tables in one group and the other half in a second group?

I want to reduce performance losses due to fragmentation of the tables (heaps).

Answer 1

Moving 400k records per month into and out of tables shouldn't cause all that many problems from a performance perspective. As SQL removes all the data from pages those pages will be reused for something else.

You shouldn't be getting inserts or updates in the middle of pages all that often as SQL should just be putting the new rows at the end of the table and deleting the old rows from where ever they are. (I'm assuming you are just deleting the oldest records when you delete records.)

If it is that much of a worry, I would put the historical tables into one filegroup and the other tables into another file group.