sql server data warehouse: I have a fact table which holds historical records. This table is increasing
exponentially in size. To date there're already over 10 million records. I'm
having trouble maintain this table on updates and queries.

Can anyone recommend any way to deal with this?

Thanks.

Start by profiling what date range of records are typically selected from
the table. If 90% of your queries are against transactions for the current
reporting period and only 10% of queries include records from past periods,
then consider maintaining your historical records in a seperate table with
the same structure while retaining only records for the current period in
the primary table. Perhaps a process at month end can insert into the
historical table and delete from the current table. If needed, both current
and historical records can be queried by implementing a view that unionizes
the two tables.

[quoted text, click to view]