I'm doing some research on how to scale our current SQL database and need some advice with a possible solution. The goal being able to handle more data and doing it in a way that performs well.
We have one MSSQL database that's a couple TB in size, several hundred tables, some with billions of records. Everything is mostly normalized(3NF) with a few exceptions. Indexes are descent but we frequently run into SQL timeouts and really slow performance. Most of this is due IMO to have tables that are too large, incorrect indexes, and having to join on a ton of tables in queries.
We have really beefy hardware so I believe for our amount of data a lot can be leveraged with a new design.
My current plan is to first split the data into two types. Transactional and historical. Using orders as an example, the transactional order tables would have minimal indexes and be normalized but only contain orders until they can no longer be changed. I.e. refunds can't happen anymore.
Once orders can no longer change, they would get denormalized and moved to historical tables with more indexing. The historical tables would eventually grow too large as well, I was thinking partitioning by year would help solve this.
To help prevent denormalization issues in code like data getting out of sync I would have an api layer of stored procs used to do CRUD operations from code instead of directly accessing tables.
I'm not sure if this is a practical approach or would even work at all.