Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gwestwater
Starting Member
1 Post |
 Posted - 2009-01-12 : 17:44:18
|
| Under normal conditions I do not think I would ever consider this but I am facing some issues with my existing database and I would like some feedback from the group on if I should split my database into multiple databases.I have a SQL Server 2005 database with what I would consider to be a small amount of central records in my star schema (about 10 million customer records) I am getting a lot of large locking issues and indexing issues as this database of 10 million customers is broken up into a schema similar to that of an oracle VPD. Where I have many end users (accounts) using the system each with a subset of the 10 million records. They all query differently and update records differently thus the number of indexes I need to achieve performance goals is vast. In addition nightly rollup sql processes and data cleanup processes place locks on the entire system stalling all efforts.This may be an over simplifaction of the problem but I am starting to get terrible performance on seeks and updates due to the amount of diffrent data access points, number of indexes and database locks. What I was thinking about doing is the following:Create a central database for all common settings for our SaaS application, create a database for account specific data (customer table and all relevant data) this would allow me to keep my indexes and FK's (with few exceptions) but allow me to customize the indexes for each account, and even move the files to their own groups.Now I am open to other suggestions as mining this data is going to be a pain if it is in multiple databases, and I will for sure have to create a datamart to roll all this data back up again.I would really value anyones opinion on this matter |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-01-12 : 18:20:03
|
| That is really going to be hard for someone to help you with on a forum, since identifying the source of the problems involves having detailed knowledge of the database schema, the data, the queries being run, usage patterns, hardware specs, and many other things that impact performance.CODO ERGO SUM |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-01-14 : 00:37:57
|
| If you are experiencing reads being blocked by writes and writes being blocked by other writes as the lock escalates then consider the multi row versioning option. It won't magically increase performance but it can significantly increase throughput as it blocks other transactions much more infrequently, depending on what you are doing. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Mutliple databases vs One large database