Incoming records will be 30 millions every day in every seconds. We need hold at least 1 year records in active table which means billions records for single website page. We have 15 pages in total. 3 billion records already exists.

I've already done those before:

1. Separate single page records into different databases

2. Create index on [DimTime], [DetailKey], [DimDate]

3. Partition table with [DimTime]

Question is:

1. Insert 30 millions is painful slow which have indexes, disable index then rebuild also slow when table have so much records.

2. I need hourly, daily, weekly, monthly reports on unique visit(distinct by cookie), unique IP visit(distinct BY IP), and total count in condition, this query is too heavy.

I am considering using slide window, but still, the history data can't be referred since we need unique counts.

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.