While looking at the database structure of an application I am using I recognized that it uses 3 different databases on the same SQL Server instance for different things.
The first one contains the main data that changes rarely.
The second one contains event logs with high traffic and volume and the last one is an archive database for old event logs.
I was wondering what the benefit of this structure might be, because the databases are running on the same instance and the database files are located on the same disk. Therefore I would not expect any performance improvements from this.
I thought, maybe I am overlooking something and somebody can point me to benefits that I did not think of.
Update:
Some good points were made regarding maintenance and security. But I am still wondering if it is possible to get a performance improvement.
A more general question would be: Can the performance of one table suffer from other large tables in the same database (due to fragmentation or for some other reason) or are those effects probably negligible.
Best Answer
Ask the developers/architects who designed the solution. Only they can really tell. I can think of several reasons:
It could be that the databased was added at different times. First the main database, then they decided they needed a database for the events and last a place to store old data. Depending on environment it might be easier to create a new database rather than adding a lot of new tables to an existing one. I've worked on systems where they at times took a backup of the production data, put it online as a read-only archive and then removed all old data from the current db - thus having several old databases available at the same time.
Future use: maybe they thought that the system eventually would have to be split up on different servers (or perhaps just faster discs for that high traffic database).
It could actually be all, some or none of the above...