Why use separate databases for high traffic/volume tables?

Question

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.

Answer 1

Ask the developers/architects who designed the solution. Only they can really tell. I can think of several reasons:

1. 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.

2. 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).

3. Plans for different back up times etc. Maybe the archive is never down for backup but available for statistics 24/7, production is perhaps backed up daily or even more often and some other plan for the event logs?

It could actually be all, some or none of the above...

Answer 2

You've got two different questions in here that I'll answer separately.

Q: Can the performance of one table suffer from other large tables in the same database?

Yes, during insert/updates/deletes. SQL Server only has one log file per database. When you insert/update/delete, your transaction doesn't complete until your data is written into the log file.

Let's say we have two tables:

• Documents - stores large XML representations of stuff. Has just two fields, an ID and an XML field, but that doesn't mean the records are small. When we do an insert into here, we need to write the entire XML to the log file, and our XML documents tend to be 5-10MB each.
• Orders - stores incoming orders from our web site. It's a small table - no XML or VARCHAR(MAX) fields, just a few IDs for customer and product. When we do an insert here, it's just 1K.

If both of those tables are in the same database, and we try to place orders while we're writing documents too, the performance of order transactions can be slower.

Now, this is an extreme example, but generally this is why I recommend storing mission-critical, high-value, small-width-record tables in a separate database from less-critical, lower-value, large-width-record tables. However, if you need to restore both sets of tables to the exact same point in time, or fail them over together between servers, then they need to be in the same database. SQL Server doesn't guarantee transactional consistency between databases on failover with things like AlwaysOn Availability Groups.

Q: If I have fast-changing data, slow-changing data, and never-changing data, should I separate them into different databases?

Yes, for a few different reasons:

• Different backup schedules - I need to make sure the fast-changing data is backed up as fast as possible. Generally people use a single backup job to do their full backups for all of their databases, and often they don't do transaction log backups at the same time they're doing full backups. If I've got a 10GB fast-changing database and a 1TB never-changing database, and if I use a single full backup job, that means I may not be getting any transaction log backups while my 1TB full backup is going on. That's a bigger window for potential data loss. You could mitigate this with separate backup jobs or tighter schedules. What you really want to do is avoid backing up never-changing data - less IO loads, less backup files to shuffle off to tape.
• Different index rebuild & stats update jobs - I don't want to be doing index rebuilds/defrags on large databases that aren't changing. Ideally I rebuild all their indexes with 100% fill factor, update the statistics with fullscan, and set the database to read-only. Presto, dense pages, faster performance without locking hassles. (Plus, as a paranoid DBA, I can just make sure nobody's changing it that way.)
• Different HA/DR strategies - if I've got a large, stable database that rarely changes, I might put it in read-only mode, and then restore that read-only copy to my disaster recovery environment. When I'm copying data from my primary data center to my secondary, then I only have to worry about the frequently-changing data.