What can I do to improve MWR performance?
Nowadays MWG cluster push 200 MB every 5 minutes during peak hours, and the MWR and DB are on the same machines "fighting" for disk attention.
MWG - 6.8.7
SQL Server 2005
DB size - 100 GB (almost 2 years of log data)
Xeon processor (4 x 2.66) and 8 GB of RAM.
What's your performance issue? Running reports, parsing logs, or both?
Disk IO would likely be the biggest problem in that configuration. If you are not able to make infrastructure changes such as moving one to different hardware or redoing a RAID, etc., then you don't have a lot of options for log parsing performance..
Make sure DB indexes are rebuilt on a regular basis.
Disk IO is my big problem and my big concern is: Will I have big improvements if I put the DB in another server?
What's the amount of data that will go through to the DB after a 200 MB log parsed?
Moving the DB to faster disks or separate disks (such as another server) should help, assuming your diagnosis is correct.
In regards to the report performance, make sure indexes and statistics are maintained. Upgrading to 5.1 or newer will also help with Quick View report performance since they were rewritten during that release.
In regards to log parsing performance, it's hard to say that the DB is the problem without seeing the server.log and checking your configuration.