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.