Optimal procedure for partitioning with auto-increment PK as boundary value

Question

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.

Answer 1

Partitioning in SQL Server isn’t as straightforward as it is in other systems, so you should have a bit of a think if it’s really the right option.

“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.”

This sounds more like a case of missing indexes and possibly using the default read committed isolation. Missing indexes will means that you’re reading more data than you really need to and therefore need bigger cache for the same workload. Read committed means that your writers block readers which is usually not ideal, you should investigate using RCSI which allows your readers to not be blocked. Obviously, this change needs thorough testing as it changes the point at which you are reading from.

In terms of indexes, go over your top IO queries and identify where they’re reading the majority of their data. There are queries all over the net for listing top SQL, identifying the source of IO will usually require executing the statements with actual execution plans and interpreting that.