We have a database that is being used to collect data from around 3000+ user computers. There is 2 data collections, 1 for collecting service data each hour and 2 performance collection that occurs every 15 mins. The Service data collection only retains just 1 collection of data so when the next collection is due it removes the Data for that computer first. The performance collection collects data every 15 mins and retains it for the day so at midnight it removes all the data.
The current design of the DB is using a single mdf file and I was thinking of splitting this out.
mdf file for the system stuff, 1 ndf for the service collection and the 2nd ndf for the performance data collection. The DB is approx 10GB by the end of the day and my thinking is that the collections hit different tables so if I group the tables together for each collection and place it on different files it could improve performance. We are currently using SQL Server 2005 on a VM with SAN storage and 2.5GB memory allocated.
Just wondering if this would improve the performance?