多租戶數據庫的性能調整
數據庫 MS SQL 2014 標準版正在成為我們多租戶 Web 應用程序的瓶頸。意識到大多數性能問題都可以通過提高程式碼效率來解決，但現在要超越這一點。
當負載很高時，數據庫似乎停止執行。在看似隨機的時間，它會大幅減速（當一個租戶插入大量數據時考慮表鎖定）。其他時候，我們會看到死鎖的事務。在最近的減速中，一大塊正在使用類似的查詢從該表中刪除單行
DELETE FROM tableX WHERE ID = x，導致很多Transaction (Process ID 183) was deadlocked on lock resources with another process and has been chosen as the deadlock victim錯誤。如果有的話，您會推薦以下哪種策略？
- 將聚集索引從自動遞增的 int PK 更改為租戶 ID + id。（注意所有表都有tenantID 列，並且在tenantID 上已經有一個非聚集索引）：
這將嘗試停止由於一個租戶的活動影響所有其他租戶而導致的頁面/表鎖定。這似乎是多租戶數據庫的推薦做法。否則，如果租戶插入 4,000 行或類似行，這似乎會鎖定表。 2. 從預設隔離級別更改為讀取已送出快照，因此寫入不會阻塞讀取，反之亦然 3. 轉向更快的物理磁碟 - 目前在 RAID 10 固態硬碟陣列上，查看英特爾 NVMe PCI-e 固態硬碟
我們使用具有高安全模式的 SQL 鏡像，因為我們不會因為硬體錯誤而失去單個預訂/行。印像中所有的寫入都寫入磁碟，並且直到寫入鏡像上的磁碟後事務才會完成。兩台伺服器之間的網路連接不到 1 毫秒，並且沒有爭用。機器中有足夠的 RAM（64GB 並在幾天內翻倍至 128GB），但確實為不同的產品託管了第二個多租戶數據庫（~40GB）。
你的評論：
可能，但是對於一張預訂表有很多查詢，許多人試圖同時獲得預訂。它比這更複雜，查詢需要連接其他表，其他租戶同時將數據載入到這些表中，等等
所以，它是一個數據庫！太好了，讓我們回答一些問題。
將聚集索引從自動遞增的 int PK 更改為租戶 ID + id。（注意所有表都有tenantID 列，並且在tenantID 上已經有一個非聚集索引）
你知道《辛普森一家》的那一集，他們帶蜥蜴來抓鴿子，帶蛇來抓蜥蜴，用吃蛇的大猩猩來抓蛇嗎？這就是你在這裡所做的。標識列聚集索引不是問題。
從預設隔離級別更改為讀取已送出快照，因此寫入不會阻塞讀取，反之亦然
這是一個您應該充分探索的絕妙選擇，但您必須確保不會遇到任何依賴於鎖定和阻塞來分配工作的隊列類型查詢的競爭條件。由於您的數據庫現在非常小（20 GB），因此您可以開始探索樂觀隔離級別。如果您的部分程式碼無法處理 RCSI，快照隔離可能更容易處理，因為您可以將其應用於特定查詢。
轉向更快的物理磁碟 - 目前在 RAID 10 固態硬碟陣列上，查看英特爾 NVMe PCI-e 固態硬碟
到處都是愚蠢的。您有一個 20 GB 的數據庫。購買 32 GB 的 RAM。如果全部都在記憶體中，那麼磁碟並不重要，而且 32 GB 的 RAM 比所有 SSD 便宜得多。儘管我完全同意您為 tempdb 獲取一些 NVM，但記憶體是更好的方法。
當然，如果沒有一些基本的故障排除，任何更改都意味著任何事情。嘗試使用sp_BlitzFirst 之類的免費腳本（完全公開，我為此 OSS 做出了貢獻）來弄清楚您的查詢實際上在等待什麼。
執行
EXEC sp_BlitzFirst @SinceStartup = 1並查看您的等待統計資訊。注意每個等待列的平均毫秒數。如果您對這些有任何疑問，請在此處與他們一起發帖。
更新
- 包含tenantID 的集群索引——這是為了阻止由於一個租戶的活動影響所有其他租戶而導致的頁面/表鎖定。這似乎是多租戶數據庫的推薦做法。否則，如果租戶插入 4,000 行或類似行，這似乎會鎖定表。
通過正確的非聚集索引，您可能不會獲得*太多不同的鎖定行為。*如果您真的希望一個租戶不打擾另一個租戶，您可以嘗試分區視圖（或者如果您升級到 2016 SP1，實際分區），或者將租戶分開到他們自己的數據庫中。
無論鎖定是針對聚集索引還是非聚集索引，都會發生鎖定升級。我不確定您為索引調整做了什麼，但是如果鎖定升級過於頻繁，缺少索引肯定是一個因素。關於貢獻的完整披露與上述相同，sp_BlitzIndex可以幫助您辨識積極鎖定的索引以及失去的索引。
- 磁碟 IO 與 RAM。我們使用具有高安全模式的 SQL 鏡像，因為硬體錯誤不會失去單個預訂/行。印像中所有的寫入都寫入磁碟，並且直到寫入鏡像上的磁碟後事務才會完成。兩台伺服器之間的網路連接不到 1 毫秒，並且沒有爭用。機器中有足夠的 RAM（64GB 並在幾天內翻倍至 128GB），但確實為不同的產品託管了第二個多租戶數據庫（~40GB）。
這是您原始問題中應該包含的重要資訊。如果網路正常，那麼鏡像延遲的兩個罪魁禍首可能是事務大小或事務頻率。通過將日誌文件放在更快的磁碟上可以緩解其中的一些問題，但最終控制事務的大小或頻率是一種更聰明的方法。
補充一下，在最近的減速中，一大塊正在從該表中刪除單行，查詢如 DELETE FROM tableX WHERE ID = x，並且很多事務（程序 ID 183）與另一個程序在鎖定資源上死鎖並且已經被選為死鎖受害者。
理論上，兩個寫查詢不會死鎖，它們只會互相阻塞，直到一個完成。您是否使用外鍵或其他約束來接觸其他具有級聯操作的表？您是否確定了他們與哪些查詢陷入僵局？
RCSI 對寫入查詢阻塞沒有幫助，但如果您願意花大量時間處理錯誤處理和查詢重試，Snapshot 可以。
但同樣，這一切都是手動的，沒有等待統計數據或其他診斷資訊。
祝一切順利。
不是為了反駁@sp_BlitzErik 的回答中所述的任何內容，而是為了更全面地解決 OP 提出的幾個問題中的一個具體問題：
將聚集索引從自動遞增的 int PK 更改為租戶 ID + id。（注意所有表都有tenantID 列，並且在tenantID 上已經有一個非聚集索引）
簡單地說：是的，是的，是的，而且可能再次是的:-)。絕對聚集在 TenantID（第一個）上，然後是 ID / Identity 列。現在，您只有
IDENTITY列上的聚集索引，並且該值被複製到 上的非聚集索引中TenantID，在這種情況下，您有兩個索引，並且 Identity 值都在兩者中。考慮有關目前設置的以下內容：
- 重複標識列會佔用更多磁碟空間
- 當您
INSERT或DELETE行時，需要修改兩個索引以考慮新/刪除的行。這增加了陷入僵局的可能性（您在@sp_BlitzErik 的回答中提到這確實發生了）。- 如果不是很明顯，維護兩個索引比只維護一個索引需要更多的時間和更多的事務日誌空間。
- 鑑於大多數查詢應該（希望！）具有
TentantIDinWHERE子句，這會強制大多數查詢使用非聚集索引，而不是直接使用聚集索引。這會對您的大多數查詢造成不必要的性能影響。只有後端/維護查詢（即可以跨租戶操作的查詢）只會使用標識列來查找行，並且這些查詢是否代表正在處理的大多數查詢是非常值得懷疑的。有關為什麼您應該繼續進行此更改的更多詳細資訊，以及您可能遇到的至少一個問題以及如何通過它，請參閱我對以下問題的回答，也在 DBA.StackExchange 上：