I have a database on SQL Server 2008 R2 with about 20 non-indexed tables that are continuously undergoing
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).