2

I'm not an expert on database performance. A colleague has suggested splitting a BI data warehouse database into several databases based on functional business unit - I believe still on one machine though I'm not sure - for performance reasons - there are many processes, procedures, logging, and monitoring happening. This has nothing to do with security or permissions - in fact those will be made more complicated potentially.

Again I can't say I'm the expert but for some reason, it doesn't seem logical to me. DB in total is about 500 GB and we have about 200 tables. Naturally it seems like most solutions from my cursory research recommend improving the disc drive speed (like SSD) or RAM (currently an anemic 4 GB) for performance reasons. Or possibly keeping one logical database, but fragmenting the schemas into 2-3 different machines.

But I don't know. Is there a significant use case or performance reason to split one database into 2 logical databases - other than user permissions/ security which aren't a factor here. There will be many views that span across these 3-4 databases.

Do read operations, normalization, tuning options, or database/table locks come into play here? My instincts tell me no - those are entirely separate issues, but I can certainly be wrong. I'm just concerned that we are going to trade minimal, if not zero, performance improvements for a whole lot of added complexity and maintenance.

I obviously am coming in here already leaning/ biased at one side, but wondering your thoughts.

  • RAM and disks improvements are indeed to be considered. Partitioning also can be an option. It will allow you to split your database in several files (so you can then play with disks). But in any case, it will depend on the database usage (reads ? write ? etc...), maybe you can can describe it a little bit more. – irimias Nov 8 '16 at 16:40
  • What problem are you trying to solve? It seems that your colleague has jumped to a solution without first understanding the problem. I get that you have performance issues, but we need more details on that. Maybe what you have is an architecture issue. With a properly designed database and good hardware, a database larger than even 20TB can perform well. – Tara Kizer Nov 8 '16 at 16:50
  • I'm in agreement with the comments, I am spinning my wheels trying to think of a reason to properly split your data warehouse across different catalogs for performance reasons. Even if there is some performance to be squeezed out of doing such a thing, perhaps it would be better to simply look at spinning up data marts for each department - leave the poor warehouse alone. – Avarkx Nov 8 '16 at 16:52
  • For comparison, the iPhone 6s has 2GB of memory. – Jorriss Nov 8 '16 at 16:59
  • Wait, I just saw it says 4GB of RAM after reading Jorriss' comment. 4GB of RAM! I haven't seen a server that small in several years. Beef up your server! 4GB is crazy low, especially with 500GB of data. Depending on your active working set, you might need 512GB or more of RAM. I probably wouldn't want any less than 128GB, but that's just with the very little info you've told us about the environment. – Tara Kizer Nov 8 '16 at 17:03
2

Size is not really a reason to do a database split. If there is slow query performance or other factors like that, then maybe. But keep in mind, splitting a database using the same hardware and storage will provide very few gains. There is also the possibility of partitioning the data which will accomplish something similar.

The problem is that this was devised as a solution that it may not fit. I have multi-terabyte databases that respond faster than 100 GB databases because it is tuned and indexed within an inch of it's life. Replacing hardware will often cover up poor database and query design. An immediate fix would be to add more RAM. 8 GB would be the minimum I would want to see in a SQL server, but given the size of the database I would suggest a minimum of 128 GB.

Ultimately we need more information on what performance issues you are experiencing in order to guide you to a more complete solution.

0

Echoing Tara's comment I would clarify what problem you are trying to solve. If there is a performance problem I can't think of a reason why you would get increased performance if the databases are split and still exist on the same server.

Instead of carrying around a single 30 pound weight you're suggesting carrying around three 10 pound weights. It's still the same load.

0

You should split the database for performance reasons, once you have evidence that is the performance bottleneck you have (or will have in order to scale further)

Without a performance issue, neither load testing, you would be fixing something that may be or not the bottleneck. Even if your bottleneck would be the need for splitting the database, business unit may not be the proper way to split (for example, what if 50% of your queries are against your last 2 days of data, then 40% for the last 2 weeks and 10% for older data, for all business units equally?)

While it is not impossible that the split helps, there are various avenues you should explore first (for simplicity), particularly:

  • Tuning indexes
  • Partitioning the database (still the same database, but distributed on different files and preferably disk volumes)

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.