SQL Azure 大型多租戶表/鎖升級問題/分區/全文索引
我們的應用程序將其數據組織在保存在 SQL Azure 數據庫中的“項目”中。一個項目可以包含 100 或 100'000 行（分佈在 35 個表中）。由於我們有 1000 多個項目，因此每個項目都使用數據庫或模式不是一種選擇（我們最終會得到 10,000 個表）。我們現在將每個實體保存在一個具有 ProjectKey 並使用行級安全性的表中。
有時，一些使用者會在一個批次/事務中導入大量數據（100'000 行）。這是我們應用程序中的合法場景。對於如此大的插入，SQL Server 會執行鎖升級並鎖定整個表。這意味著所有其他使用者都被阻止保存他們的數據。
我們可以禁用鎖升級，但這對於 100'000 行似乎不是一個好主意。另一種選擇是對錶進行分區，以便（幾乎）每個項目都有自己的分區並使用（lock_escalation = auto）。這樣，SQL Server 只鎖定相應的分區。我已經對 13'500 個分區進行了一些實驗，一切似乎都執行良好。
我們還使用 SQL Server 的全文搜尋來索引我們的一些表/列。FTS 需要單列唯一索引。預設情況下，分區表需要分區索引，從而導致多於一列。我發現我可以使用“非對齊”索引在所有分區上創建單列唯一索引。
但是，這破壞了我的每個分區鎖定策略。由於非對齊索引位於分區 1 上並且每次插入都會更新，因此大型插入將完全鎖定它並再次導致所有其他使用者的鎖定……
有沒有辦法將 FTS 與分區表一起使用並為全文索引提供單列索引？
我找到了解決方案。
- 對於需要全文索引的表，我創建了一個附加列，由 ProjectKey 和一個 int 標識列的連接計算/保留。這個新的主鍵列使用 PartitionFunction/Scheme A進行分區 ，並用作全文索引的單列唯一索引。
- 所有非全文索引表都使用 ProjectKey 分區，使用 PartitionFunction/Scheme B。
學習點是：可以在 SQL Server 中創建多個不同的分區函式/方案。如果函式產生相同的結果，則相應的表將被“拆分”到同一個分區。使用 (lock_escalation = auto)，現在每個項目都將與其他項目分開鎖定。