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)?
well I think so. One of the bottleneck in transaction for sql is the writing in the ldf (sql log file) file. Of course you may split the sql log on several physical files, but I'm quite sure you can't manage the dispatch of transactions between those physical files.
When having two databases you will have two sql logging and then can set them on two different physical drives and then gain better performance.
but this is really pertinent in case of concurent transactions.