I have a thousand web servers writing their logs to a SQL Server 2008 database. The log information can be up to 100 GB per day, and we keep 1 month’s worth of logs.
Each day, we run queries on the log data and store the results of the queries in the database. The total size of the query results for a day is about 10 MB.
Right now, the table which holds the log data and the tables which hold the query results are on different filegroups on different physical drives.
Would it make a different from a performance point of view to have the log data and the query results stored in two separate databases (which would be on separate physical drives)?