I work for a company that has a multi-tenant database model that doesn't currently use database partitioning. The ideal end-state would be adding a new TenantID column to every existing table, including this new column as the first column in every primary key, and rewriting all existing SQL definitions to filter and equi-join on TenantID.
However, with thousands of existing SQL modules, this is a resource intensive solution, outside of a complete rewrite of the entire database. So my new plan is to make this update in phases.
Phase I will be keeping the existing database schema exactly as is, and creating aligned database partitions that use the existing SupplierID column (which is in nearly every table) as a proxy for TenantID. SupplierID already lines up well to what I want to do with TenantID. Existing suppliers are clustered into supplier groups, so a group of suppliers can be thought of as a single tenant.
So my question is. If I do this, create aligned database partitions on the existing tables without changing any table schema or the definitions of any existing SQL modules, will I see any performance benefits?
You can ask for additional information in the comments, and I will update the question to reflect them. But for starters there are many existing queries that filter on SupplierID, but few that equi-join on it.