There are many ways to make sure your database is (almost) conflict-free. It doesn’t matter how many applications use the database, it’s more how many transactions are running against it and what are these transactions doing. So to reduce contention in the database you should follow few rules:
* Transactions as short as possible
* Use lower transaction isolation level when possible
* Partition tables (Enterprise Edition)
* Tune you T-SQL to use indexes properly and lock as less resources as possible
* Spread tables/indexes across physical disks to avoid IO contention
* Monitor memory usage and increase memory if necessary.
But why would you like to have all applications in the same database? Do they all use same tables/objects?
Note that even if objects are in a different database, you can still use them.
There are many advantages in separating applications to different databases, because each database will be smaller and therefore:
* Less contention on Transaction Log
* Less blocks/locks on system tables
* Faster backups
* Less management – you don’t have to spread objects on separate file groups to avoid disk contention (or you do it less than in a larger database)
* Tables are smaller (depends on the design) and therefore IO operations are faster
Michelle.
www.pythian.com.
Discuss This Question: 1  Reply