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.

  • Are you sure splitting up your transactional tables won't affect your business processes? Such as agents not being able to see all of a customer's order history in one system. Have you checked the execution plans of your queries to make sure that the indexes you think are being used are actually being used? – user2669338 Aug 5 '20 at 23:22
  • My idea was a stored proc like sp_Get_Orders would query on both transactional and historical tables if needed to return data. I have checked indexing and they work for some queries and not others. How the schema is structured we'd need to create too many indexes for all scenarios. This is more of a systemic problem IMO. The other 40 devs in our org have also done this so I don't believe it's just one person missing something. I believe it's a design flaw. – user3953989 Aug 6 '20 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.