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.