Can you gain the performance benefits of partition elimination without changing SQL module or table definitions?

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.

Possibly, but in my experience you don't partition data for performance reasons. Yes, partition elimination can reduce the IO that some queries consume but there are complications that partitioning will introduce, see here for my favourite.

What partitioning does help with is maintenance. You can rebuild indexes, and stats (on SQL Server 2014+) at the partition level. This can be a huge win when you struggle to keep maintenance operations inside your maintenance window.

Partitioning is also great when it comes to loading and archiving data. The SWITCH statement is your friend here.

Lastly partitioning can also be used to optimise columnstore indexes by introducing a level of ordering to the data. This helps with dictionary pressure. Each partition acts as it's own columnstore index which means you get more global dictionaries as well as delta stores and delta bitmaps. Niko's blog has much much more info here

StackExchange Database Administrators Q#165115, answer score: 9