0

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?

Improve this question
2
  • 1
    Have you considered using a staging table to receive the new rows, and importing those rows into the main table in batches where you control the batch size? This would limit the lock escalation problem.
    – Hannah Vernon ♦
    Nov 20, 2017 at 18:32
  • I have thought about this approach. But I would 1) loose transactional safety and 2) produce incomplete data if another user reads data between the first and the last chunk.
    – Thomas
    Nov 22, 2017 at 8:04

1 Answer 1

Reset to default
0

I have found a solution.

  1. 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.
  2. 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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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