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.

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.

  • 1
    Well, do they all have the same SLAa, recovery model, recovery plan, access rights, etc.? Not all separations are performance-driven. Apr 19 '13 at 19:35
  • 2
    Also, the concurrency gains from separating reporting workloads vs. OLTP workloads can be significant. Not to mention the handful of points that @AaronBertrand brought up. In order to know they "why" of that database design, you need to talk to the developers that designed it. Apr 19 '13 at 19:41
  • That are really good points. I was asking this question, because I am currently developing an application with similar demands and therefore I was wondering if I could learn something from this and use it in my own project.
    – Karsten
    Apr 19 '13 at 19:50
  • Don't be surprised if the actual answer to your specific scenario is just that the designer like to compartmentalise his solution. Lots of people untrained in DB design do end up designing DBs.
    – Paul
    Aug 7 '13 at 13:34

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

  • I think they actually do have different backup plans for these databases (as you said the archive is not backed up).
    – Karsten
    Apr 19 '13 at 19:52
  • There you go - it's harder to back up individual tables from a database so the easy solution might be to split up in different databases!
    – Jensd
    Apr 19 '13 at 19:56
  • Could you please also comment on my updated more generalized question?
    – Karsten
    Apr 19 '13 at 20:00
  • I dont really know. The db itself does probably handle that by itself. Performance is tricky - how fast do you want it to be. Are there problems in the application itself? Have you got fast discs, lots of memory and enough CPU and a fast network etc...
    – Jensd
    Apr 19 '13 at 20:05
  • I think I will use this structure regardless of performance benefits because of the other benefits you have mentioned (mainly maintanence reasons and the possibility to split the database if performance becomes an issue). Thanks for your help.
    – Karsten
    Apr 19 '13 at 20:18

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.
  • To tack onto a part of that, different DBs on the server may keep their files in different locations. e.g. maybe the archive is on a local RAID array and the production data is on a SAN or SSD based RAID array for faster disk access.
    – Paul
    Aug 7 '13 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.