SQL Azure large multi-tenant table / lock escalation issue / partitioning / full text index Our app organizes its data in "projects" that are saved in an SQL Azure database. A project can contain 100s or 100'000s of rows (spread over 35 tables). Since we have 1000s of projects, using a database or schema per project is not an option (we would end up with 10'000s of tables). We now save every entity in one single table that has a ProjectKey and use row level security.

Occasionally, some users import a large amount of data in one batch / transaction (100'000s of rows). This is a legitimate scenario in our app. For such large inserts, SQL Server performs a lock escalation and locks the complete table. This means that all other users are blocked from saving their data.

We could disable lock escalation, but this does not seem to be a good idea for 100'000s of rows. Another option is to partition the table so that (nearly) every project has its own partition and use (lock_escalation = auto). This way, SQL Server locks the respective partition only. I have made some experiments with 13'500 partitions and things seem to work well.

We also use SQL Server's full text search to index some of our tables / columns. FTS requires a single column unique index. By default, partitioned tables require partitioned indexes, resulting in more than one column. I found out that I can create a single column unique index over all partitions with a "non-aligned" index.

However, this destroys my locking-per-partition strategy. Since the non-aligned index sits on partition 1 and is updated by every insert, a large insert will lock it completely and again lead to the locking of all other users...

Is there a way to use FTS with a partitioned table and have a single-column index for a full text index?
I have found a solution.


For the tables that need full text indexing, I create an additional column, computed/persisted from the concatenation of the ProjectKey and an int identity column. This new primary key column is partitioned with PartitionFunction/Scheme A and is used as the single column unique index for the full text index.
All non-full text indexed tables are partitioned with the ProjectKey, using PartitionFunction/Scheme B.


The learning point was: Multiple different partition functions / schemes can be created in SQL Server. If the functions produce equal results, the respective tables will be "splitted" to the same partition. Using (lock_escalation = auto), every project will now be locked separately from the others.