It seems to me that, if the application could insert the actual ProductID being clicked on, that would be more useful to the business for reporting purposes than the full URL. From a database administration perspective, this would probably reduce the storage size of this table by 90%. That's a game changer.

Also, if the business is simply interested in how many distinct users click on each distinct product (but NOT interested in how many times a distinct user clicks on a distinct product), then you may want to set IGNORE_DUP_KEY = ON to prevent insertion of duplicate records. There is no point in logging 10 seperate records for the same Time / Product / User event, if that's just redundant data for which the BI team has to filter out. First let the BI team define what a "duplicate" is, and then run some queries and see how much "duplication" there is in this table of 400+ million records.

An average of 3 million inserts per day would put a heavy strain on an OLTP server. I definately reccomend you investigate the approach of having the web application log records to flat text files, and then have an ETL process that bulk loads the records into the table at regular intervals like hourly. That too is a game changer.

If you take the above advice, reduce the table to a narrow set of keys and perform batch inserting, then it's possible you could get by without partitioning. Sliding window partitioning is fairly complex to setup, and I would only do it as a last resort.

If you cluster the table on insert time then that will help reduce index fragmentation. Also insert time is probably what drives most of the reporting queries on this table. To further faciliate reporting, you may get by with only one additional non-clustered index. Really, if the reporting team is hitting this table hard with queries throughout the day, then you may want to look into leveraging Analysis Services or some other OLAP tool.

Perhaps you thought I was talking about schema compaction (DBCC CLEANTABLE) or index regorganization (DBCC REINDEX). But actually I was just talking about replacing things like user names and URLs with smaller data types (like ProductID for example istead of URL) so the column set and row size is narrow.

There would definately be a benefit to logging records into a flat text file and then bulk loading at internals. You don't want the web application inserting 3 million records daily (~35 records per second) to a table. That's bad for the web application (synchonous database calls), bad for the database (heavy disk i/o queuing), and bad for the reporting queries (blocking). Another advantage is that whenever you need to perform some maintenance on the table, like re-indexing, you can temporarily turn the bulk load process off. The web application would continue logging to the text file uninterrupted, and then the bulk load can catch up again when it's turned back on. You may also find benefit in suspending the bulk load process while the reporting team is running reports.

If nothing else, do this.

Just forget about logging daily records from the application directly to a table. As described above, have the application log to a text file, and then schedule a process to load the records to the table daily (or hourly).

Hadoop would be an investment in learning and development, both for you and the BI team. Currently you are accumulating about 300 GB a year. The threshold for Hadoop is about 10 TB, so that can be part of a five year plan. Just focus on the above reccomendations and read up on Microsoft's HDInsight service when you have time.

Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius, and a lot of courage, to move in the opposite direction. - Albert Einstein