Best Clustered Index Configuration for Partitioned Multi-Tenant Table with RLS

  • I’m working with a SQL Server database that supports a multi-tenant application. We have row-level security based on a TENANTID column, which restricts data access by tenant.

    Here’s the setup:

    All tables include an identity column named ID that is currently the first key in the clustered index (defined as (ID, TENANTID)).

    The tables are partitioned by TENANTID using a partition scheme (TenantIDPartitionScheme) that maps partitions across multiple filegroups.

    The ID column is not used as a foreign key in any other table and isn't referenced by other tables.

    I’m considering whether the clustered index should instead be ordered as (TENANTID, ID) to better align with row-level security and the partitioning strategy, as queries frequently/always filter by TENANTID. My goals are to optimise performance for tenant-based data retrieval, reduce fragmentation, and improve maintenance efficiency.

    Specific Questions:

    1. Would changing the clustered index order to (TENANTID, ID) improve performance for this setup?
    2. How much does the partition scheme on TENANTID alone help in query performance with the current clustered index (ID, TENANTID)?
    3. Are there any potential downsides to switching the clustered index to (TENANTID, ID) with the existing partition scheme?

    Any insights into the best approach for indexing and partitioning in this multi-tenant, row-level security scenario would be greatly appreciated!

  • If the queries tend to be by TENANTID -- and I would expect that they are -- then I would definitely uniquely cluster by ( TENANTID, ID ).  If lookups are done by ID only, you could a nonclus index to cover that.  Thus, from my standpoint:

    (1) Yes.

    (3) No.

    (2) is somewhat more involved to analyze.  But generally you partition only to be able to easily work with individual partitions.  With the table properly clustered (i.e. by ( TENANTID, ID )), you could easily find that you don't need to partition at all.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thank you, Scott, for the helpful advice! I’d like to dive a bit deeper into point (2) to better understand how partitioning impacts performance in a multi-tenant setup. I’m new to working with both row-level security and partitioning in the same database.

    Based on your experience:

    Does partitioning by TENANTID improve query performance when most queries are filtered by tenant? For instance, will partitioning reduce the amount of data SQL Server scans, or is it mainly useful for manageability, like simplifying maintenance and backups?

    If I use (TENANTID, ID) as the clustered index, would partitioning still provide any additional performance benefits? Or would the clustered index be sufficient for efficiently handling tenant-filtered queries on its own?

    I believe the original implementers may have chosen ID as the leading column in the clustered index because, in general, the most selective columns are usually placed at the start of an index. However, in our multi-tenant setup with row-level security, it seems that making TENANTID the leading column might better serve our needs. Since TENANTID is frequently used for filtering (due to row-level security) and aligns with the partitioning scheme, having it as the first column in the clustered index could improve performance by allowing SQL Server to target relevant partitions directly for tenant-based queries. Additionally, since the clustered index on (TENANTID, ID) remains unique, we wouldn’t lose selectivity with this change.

  • I'll address your qs/ concerns as (A) ["Does partitioning ..."], (B) and (C):

    (A) Yes, absolutely.  You only need to partition then for other aspects, such as manageability.

    (B) In the vast majority of cases, the clus index alone is enough.

    (C) You've got it exactly right.  ( TENANTID, ID ) will be vastly better if most of your queries specify a WHERE on TENANTID.  And, as you noted, it is important to include the ID so that all key values are already unique.  I have one table clustered on 5 keys because data is always selected based on at least the first 3 and up to 5 of them (depending on the security level of the id doing the accessing).

     

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thank you for the detailed reply - it’s very helpful!

    Just to clarify, the queries themselves don’t specify TENANTID in the WHERE clause. Instead, row-level security handles tenant filtering through

    EXEC sp_set_session_context @key=N'TENANTSID', @value=...

    which restricts access based on the tenant ID set in the session context.

    Given this setup, would you still expect (TENANTID, ID) to perform better than (ID, TENANTID), since the TENANTID filter isn’t explicitly in the query but is applied via the session context?

  • Interesting.  Look at the query plan and see if it is doing a SEEK as part of the clus index scan.  I'll read up on the mechanics of row-level security too.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • It's just a predicate / search, it's not an index SEEK.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • if it is partitioned what matters is if it is has the partition operator on the plan or not - if it has then being partitioned is helping the query to reduce data even if it isn't the first column on the clustered index.

  • Since it's not an index seek, partitioning should still help the query by reducing the number of pages it has to search.  Once again, verify that by looking at the query plan.  I would still definitely cluster the table by TENANTID first.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thank you, Scott and Frederico, for the helpful advice!

    I’ll take a closer look at the query plan tomorrow to verify whether it’s performing a seek or just a scan. I’ll also check if there’s a partition operator in the plan to confirm whether partitioning is effectively reducing the data scanned.

    My plan is to change all indexes on our test database to use (TENANTID, ID) as the clustered index order and then run before-and-after load tests to compare performance. I’ll monitor the execution plans closely to see how the changes impact filtering and page scans.

    Thanks again for your guidance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply