I'm working with an application that deals mainly with large sets of related cost and planning data. This data gets stored across maybe 20 different tables in various models. Each set of data is related to a table that serves as the aggregate root - say a "Plans" table that ties together information about resource utilization for individual projects as well as other project details. There are other tables to store more information about the plan constraints, completion, etc. The Plans table has an auto-increment int PK. I'm hoping to partition my Plans and related tables by using the Plan's primary key as a partition boundary. I'm hoping partitioning will allow me to reduce lock contention among users and allow for quicker reads from the database because of the reduced chances for cache misses.
There are many tables where the Plans table serves as a foreign key, but there are also tables that hold detailed data that are only indirectly related to the Plans table and therefore do not have columns with an identifier from the Plans table.
I'm hoping to partition my database using the Plans table's primary key as a partition boundary. In order to do this, I'll need to add a column to hold my Plan ID to all tables for which it serves as an aggregate root. I'll also need to move existing data over to the new structure.
I've started on a SQL script to perform this work on the database, and I'm wondering if there's an advantage in doing things in a particular order. I've started on a script where I first create the partitioning function and scheme. From there, I can drop indexes and constraints as needed for a particular group of related tables, then partition by building the clustered index, and the add the indexes and constraints back afterwards. An alternative approach would be to drop all indexes and constraints, partition all the tables by rebuilding their clustered indices, and then add the indexes and constraints back afterwards.
I'm using SQL Server. The logical relationships among the tables necessitate that I drop constraints in a certain order when partitioning existing data in the first place, but is there an advantage to dropping all of the constraints for all of the tables (or as much as possible) up front instead of dropping only what is necessary to partition "sets" of related tables? There may be quite a lot of data in place to process already, so I'm looking to do things as efficiently as possible.