5

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.

|improve this question
  • 4
    Is your proposal to partition several tables using a common partition function defined over ranges of SupplierID values? Perhaps adding a complete concrete example to your question would help clarify. – Paul White 9♦ Feb 22 '17 at 10:55
  • Yes, that is what I was planning. Pick one range of SupplierID values to test it on, leaving the rest of the data in the main partition, which I am calling general pop. So I can post concrete results on whether the limited experiment worked. The question is getting at performance improvement vs work. Every phase after phase I will require more than just my resources to work. I'd rather not fail, but as they say in Agile, if you have to fail, fail early. – Matthew Sontum Feb 23 '17 at 0:15
  • 2
    I'm primarily inviting you to add a complete code example to your question. This would be clearer than trying to describe your proposal in words alone. – Paul White 9♦ Feb 23 '17 at 6:28
  • Well, I'll end up writing this for work today anyway. And what I will be writing should be generic enough to post here, the script to move all tables with SupplierID to use the new partition for a particular set of SupplierIDs – Matthew Sontum Feb 23 '17 at 12:00
8

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

|improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.